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>