Friday, October 23, 2015

O7_DICTIONARY_ACCESSIBILITY

Long ago, in Oracle 7 I believe, a user with the SELECT ANY TABLE privilege could access tables and views owned by SYS. Also, a user with the EXECUTE ANY  PROCEDURE privilege could run code owned by SYS. Nowadays, this behaviour is controlled by the O7_DICTIONARY_ACCESSIBILITY initialisation parameter. The default value for this is FALSE, as you can see in the query below, which I ran in an Oracle 11.1 database:
 
SQL> conn / as sysdba
Connected.
SQL> l
  1  select value, isdefault
  2  from v$parameter
  3* where name = 'O7_DICTIONARY_ACCESSIBILITY'
SQL> /
 
VALUE                ISDEFAULT
-------------------- ---------
FALSE                TRUE
 
SQL>
 
I created a user in this database and granted it the SELECT ANY TABLE and EXECUTE ANY PROCEDURE privileges:
 
SQL> create user andrew
  2  identified by reid
  3  /
 
User created.
 
SQL> grant create session,
  2  select any table,
  3  execute any procedure
  4  to andrew
  5  /
 
Grant succeeded.
 
SQL>
 
I logged in with this user and found that it could neither SELECT from DBA_TABLES nor EXEC SYS.DBMS_LOCK.SLEEP:
 
SQL> conn andrew/reid
Connected.
SQL> select count(*) from dba_tables
  2  /
select count(*) from dba_tables
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> exec sys.dbms_lock.sleep(10);
BEGIN sys.dbms_lock.sleep(10); END;
 
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_LOCK' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
 
SQL>
 
I changed O7_DICTIONARY_ACCESSIBILITY to TRUE:
 
SQL> conn / as sysdba
Connected.
SQL> l
  1  alter system
  2  set o7_dictionary_accessibility = true
  3* scope = spfile
SQL> /
 
System altered.
 
SQL> startup force
ORACLE instance started.
 
Total System Global Area  158703616 bytes
Fixed Size                  2086736 bytes
Variable Size             104859824 bytes
Database Buffers           46137344 bytes
Redo Buffers                5619712 bytes
Database mounted.
Database opened.
SQL>
 
Then when I logged in with my user, it could SELECT from DBA_TABLES and EXEC SYS.DBMS_LOCK.SLEEP:
 
SQL> conn andrew/reid
Connected.
SQL> select count(*) from dba_tables
  2  /
 
  COUNT(*)
----------
      1021
 
SQL> exec sys.dbms_lock.sleep(10);
 
PL/SQL procedure successfully completed.
 
SQL>

2 comments:

Laurent Schneider said...

Until 12c DBFIPS_140 this was the only uppercase parameter, don't ask why...

Andrew Reid said...

Dear Laurent,
Good to hear from you again. Yes, it has always been a mystery to me too why this parameter is in upper case.
Andrew