By Mike Dietrich-Oracle on Mar 02, 2015
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.
Documentation can be found here:
ANY DICTIONARY Privilege No Longer Accesses Some SYS Data
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.
- 18.104.22.168 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.
PS: Credits go to Andy Kielhorn for highlighting this to me and thanks to Gopal for providing me with the doc links