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:
- 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.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
Please provide complete document to upgrade the oracle database from 11.2.0.4 to 12c
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
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 ?
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!
I can’t tell you – but as they have taken away the priv by default there may have been something …
Cheers
Mike