New Behaviour in Oracle Database 12c and 11.2.0.4: SELECT ANY DICTIONARY with reduced privilege set

You’ve just upgraded to Oracle Database 12c – but your favorite admin tool receives an ORA-1031: Insufficient Privileges after connection?

Then the reason may be the reduced set of privileges for the SELECT ANY DICTIONARY privilege. This privilege does not allow access to tables USER$, ENC$ and DEFAULT_PWD$, LINK$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, and XS$VERIFIERS. Actually such changes are not new. For instance in Oracle 10.1 we removed the access to  LINK$ in SELECT ANY DICTIONARY (well, this may have happened because the dblink’s password was stored in clear text in LINK$ – a misbehavior which is fixed since Oracle 10.2).

Please be very careful with granting this privilege. Furthermore, you need to be aware that it can’t be granted either through a role, nor is it included in the GRANT ALL PRIVILEGES.

Oracle 11.2:

Oracle 12.1:

Documentation can be found here:

  1. SELECT ANY DICTIONARY Privilege No Longer Accesses Some SYS Data Dictionary Tables
    For better security, the SELECT ANY DICTIONARY system privilege no longer permits you to query the SYS schema system tables DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, and XS$VERIFIERS. Only user SYShas access to these tables, but user SYS can grant object privileges (such as GRANT SELECT ON USER$ TO sec_admin) to other users.
    .
  2. 2.9.2.5 Increased Security When Using SELECT ANY DICTIONARY

Please be aware that you can’t query anywhere inside the database which privileges are included in the SELECT ANY DICTIONARY privilege as this is embedded in our code.

–Mike

PS: Credits go to Andy Kielhorn for highlighting this to me and thanks to Gopal for providing me with the doc links

5 thoughts on “New Behaviour in Oracle Database 12c and 11.2.0.4: SELECT ANY DICTIONARY with reduced privilege set

  1. Hi,

    either take our BIG slide deck to your right – or login to MOS and access Note 1503653.1 Complete Checklist for Manual Upgrades to Oracle Database 12.1

    Cheers
    Mike

  2. Hello

    Thank you for those information.

    So, with Oracle 12, is there any security issue if I grant "select any dictionnary" to my applicative user ?

  3. Great clear answer. Ora Support asked me to query user$ and I got "table does not exist" as a user with DBA rights. Slight panic till I found your article, thought the table was hosed. Logged in as Sys and good to go. Thanks Mike!

Leave a Reply

Your email address will not be published. Required fields are marked *