Tightening Security with SELECT ANY DICTIONARY in Oracle 12c

Developers and users sometimes request the SELECT ANY DICTIONARY system privilege to enable them to view various data dictionary tables.  This may be fine for querying DBA_TABLES, etc, but the Oracle data dictionary contains a LOT of information.  Some of the views/tables are compromising from a security standpoint.  One of Oracle’s legendary gaffs in version 9i displayed cleartext passwords for database links in SYS.LINK$.  Yikes.  In version 12c, Oracle has locked down this type of access even further.  Here’s an example from version 11g.

SQL> create user altdotoracle identified by altdotoracle;

User created.

SQL> grant create session, select any dictionary to altdotoracle;

Grant succeeded.

SQL> conn altdotoracle/altdotoracle

SQL> select name, password from sys.user$ where password is not null;

NAME                           PASSWORD

------------------------------ ------------------------------

SYS                            AAJ125C9483Q017

SYSTEM                         W45825DFFFD37SE

OUTLN                          WW24Z1N6A8ED2E1

ALTDOTORACLE                   73NH15SG3Q2364W

Armed with these password hashes, password cracking tools can be used to compare these values to common dictionary passwords.  When a match is found on a non-complex password, your password is cracked.  (Don’t wear yourself out trying to crack the hashes above, I obfuscated them)  SELECT ANY DICTIONARY no longer allows access to dictionary objects like USER$, ENC$ and DEFAULT_PWD$. Let’s try it.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create user altdotoracle identified by altdotoracle;

User created.

SQL> grant create session, select any dictionary to altdotoracle;

Grant succeeded.

SQL> conn altdotoracle/altdotoracle

Connected.

SQL> select name, password from sys.user$ where password is not null;

select name, password from sys.user$ where password is not null

                               *

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> select * from sys.enc$;

select * from sys.enc$

                  *

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> select * from sys.default_pwd$;

select * from sys.default_pwd$

                  *

ERROR at line 1:

ORA-01031: insufficient privileges