Why are TOAD schema objects granted to PUBLIC and is it a security concern?

Toad for Oracle
1

My security people audited my database and stated that all grants to PUBLIC or grants with the word ANY are a violation of policy.

Specifically, they identified the TOAD schema and the tables within it. They’re asking me if they can revoke all privs from PUBLIC,

create a role and grant the privs to the role. Can anyone tell me if doing the following will break features in Toad 12? I don’t

have the privs to execute these because I’m just a developer who uses Toad.

Also, if anyone could explain what these server side objects (TOAD schema) are used for, that would be great.

REVOKE SELECT ANY DICTIONARY FROM TOAD;
REVOKE DELETE, INSERT, SELECT, UPDATE ON TOAD.TOAD_DATA_FILES FROM PUBLIC;
REVOKE DELETE, INSERT, SELECT, UPDATE ON TOAD.TOAD_FILESTAT FROM PUBLIC;
REVOKE DELETE, INSERT, SELECT, UPDATE ON TOAD.TOAD_FREE_SPACE FROM PUBLIC;
REVOKE DELETE, INSERT, SELECT, UPDATE ON TOAD.TOAD_REF FROM PUBLIC;
REVOKE DELETE, INSERT, SELECT, UPDATE ON TOAD.TOAD_TABLESPACES FROM PUBLIC;

CREATE ROLE TOAD_ROLE;
GRANT SELECT ON SYS.DBA_TRIGGERS TO TOAD_ROLE;
GRANT SELECT ON SYS.DBA_SOURCE TO TOAD_ROLE;
GRANT DELETE, INSERT, SELECT, UPDATE ON TOAD.TOAD_DATA_FILES TO TOAD_ROLE;
GRANT DELETE, INSERT, SELECT, UPDATE ON TOAD.TOAD_FILESTAT TO TOAD_ROLE;
GRANT DELETE, INSERT, SELECT, UPDATE ON TOAD.TOAD_FREE_SPACE TO TOAD_ROLE;
GRANT DELETE, INSERT, SELECT, UPDATE ON TOAD.TOAD_REF FROM TO TOAD_ROLE;
GRANT DELETE, INSERT, SELECT, UPDATE ON TOAD.TOAD_TABLESPACES TO TOAD_ROLE;
GRANT TOAD_ROLE TO TOAD;

2

Those tables are used to store tablespace usage history (from Toad’s main menu: Database -> Administer -> Tablespaces). If you don’t use this feature in Toad, you can drop those tables. I don’t view these as a security concern, but I’m not you. It’s your call if this is sensitive information or not.

The space usage history feature in Toad (TOAD user) selects from DBA_TABLESPACES, DBA_DATA_FILES, DBA_FREE_SPACE, DBA_JOBS, and V_$FILESTAT. I’m not sure offhand how DBA_TRIGGERS and DBA_SOURCE got in there. These need to be granted directly to TOAD (not through a role) for this feature to work.

All of those TOAD tables (TOAD_DATA_FILES, etc)…the TOAD schema owns those so you don’t need to grant them to TOAD. Grant the role to whatever user you use to login with Toad to look at the tablespace history info.

3

Another thing I thought of that will probably make your security people feel better - as long as nobody is logging in as TOAD, you should be able to LOCK and EXPIRE the TOAD user.

And I guess I didn’t really answer why those tables are granted to public - it’s just so you don’t need to pick and choose who can select from them and who can’t. It’s just tablespace size data, which in my mind is not sensitive information.

4

John, thanks for your rapid response. I meant to type “GRANT TOAD_ROLE TO TOAD_USER1;” where TOAD_USER1 is Toad user who needs that table. Also the “GRANT SELECT ON SYS.DBA_” grants was a “replacement” to appease security for “SELECT ANY DICTIONARY” that I grabbed from the Toad server-side objects wizard screen.

Anyway, I’ll combine your first and second posts and give it to my security person.