Suppose you are a security-minded Oracle DBA and one day a project knocks on your door. They have an application and to run it on an Oracle 11g R2 database, their application user needs a list of 76 database privileges amongst which:
- the DBA role (but only during installation)
- SELECT ANY DICTIONARY
- SELECT, INSERT, UPDATE, DELETE ANY TABLE
- CREATE, ALTER, DROP ANY INDEX
- CREATE, DROP ANY VIEW
- CREATE, ALTER, DROP ANY TRIGGER
- CREATE, SELECT ANY SEQUENCE
- CREATE, EXECUTE ANY PROCEDURE
- select on sys.user$, obj$, tab$, col$, ind$ and lots of other fixed tables
- select on dba_users, dba_sys_privs, dba_tab_privs, dba_roles, etc., etc..
- select on sys.v_$database, sys.v_$controlfile, sys.v_$session, sys.gv_$session, etc., etc..
After you’ve stopped laughing or protesting or sadly shaking your head (or all of the above), often the politics start. In this case this is an application sold by a IBM and according to their website this is the list they need. So it’s either to grant or not to grant. To keep the project running or not keep the project running. That’s the question. (Of course you can use SQL trace to find out what SQL’s are executed and create your own list of privileges. But with specs that specifically ask for grants on sys.user$, sys.obj$, sys.tab$ and so on, it’s a safe bet they will actually use it. You wouldn’t be able to work around that.)
A discussion with collegue DBAs about a situation like this turned into a question whether ANY privileges affect the SYS schema. I knew that SELECT ANY TABLE without the parameter o7_dictionary_accessibility=true would not give access to tables or views from SYS (not more than you had anyway).
But take EXECUTE ANY PROCEDURE. If I didn’t have grants to execute sys.dbms_metadata in any way (neither directly of via PUBLIC), would EXECUTE ANY PROCEDURE give me that grant? It turns out it doesn’t. I didn’t know that. The role EXECUTE_ANY_DICTIONARY does grant that privilege by the way.
DELETE_CATALOG_ROLE can be used to delete rows from sys.aud$ (the audit trail). Creating a view as SYS however won’t work with any %_CATALOG_ROLE role.
SELECT_CATALOG_ROLE doesn’t give you access to fixed tables like sys.user$. The system privilege SELECT ANY DICTIONARY does that. That’s why you have to be very careful with that privilege. See my blogpost about password hash cracking for that.
As for the politics: if you ever have to run this, consider following things:
- Make sure the passwords of admin accounts are totally different from those on your other databases.
- Make sure no other applications share the same database as this application (because of the many ANY privileges).
- Make sure the database doesn’t share the same database server with the database of this application (because with these privileges and access to password hashes, you’re (indirectly) able to access the database server, change config files that the oracle OS user can access, etc. (for example by hacking the SYS password and then misusing directory objects).