Wednesday, June 26, 2013

Oracle 12c Limitations to RESOURCE, SELECT ANY DICTIONARY

Oracle 12c has implemented a few improvements to the existing system privileges.  

Dictionary tables containing password hashes (DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$XS$VERIFIERSare no longer included in the SELECT ANY DICTIONARY system privilege.  This makes it safer to give developers access to dictionary tables for tuning and debugging, without giving them the chance to run brute force attacks ...

Unlimited Tablespace is no longer included in the RESOURCE role. This should reduce the number of times developers create segments in SYSTEM tablespace ....

SQL> grant select any dictionary to god;

Grant succeeded.

SQL> grant resource to god;

Grant succeeded.

SQL> connect god/god@T12P1
Connected.
SQL> select password from user$;
select password from user$
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select default_tablespace from dba_users where username = 'GOD';

DEFAULT_TABLESPACE
------------------------------
USERS

SQL> create table test(id integer) tablespace system;

Table created.

SQL> insert into test values (1);
insert into test values (1)
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'