% ANY % privileges and other excessive privileges

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_CATALOG_ROLE
  • 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).
Advertisements

About Marcel-Jan Krijgsman

Ever since I started working with Oracle, I had an interest in Oracle database performance tuning. This led, eventually, to a four day training I made and gave for customers of Transfer Solutions. Since 2012 I work for Rabobank Nederland. A few years ago I also became interested in Oracle database security. All technology aside, it is my experience that security usually plays out on a political level. I'm a Oracle certified professional for the 8i, 9i, 10g and 11g databases and Oracle Database 11g Performance Tuning Certified Expert.
This entry was posted in Oracle security and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s