If O7_DICTIONARY_ACCESSIBILITY is set to TRUE, What happened?

“If you’re granted select any table to a user when O7_DICTIONARY_ACCESSIBILITY  is set to TRUE, the particular user can able to see the data dictionary tables.”

O7_DICTIONARY_ACCESSIBILITY controls restrictions on SYSTEM privileges. If the parameter is set to true, access to objects in the SYS schema is allowed (Oracle7 behavior). The default setting of false ensures that system privileges that allow access to objects in “any schema” do not allow access to objects in the SYS schema.

For example, if O7_DICTIONARY_ACCESSIBILITY is set to false, then the SELECT ANY TABLE privilege allows access to views or tables in any schema except the SYS schema (data dictionary tables cannot be accessed). The system privilege EXECUTE ANY PROCEDURE allows access on the procedures in any schema except the SYS schema.

If this parameter is set to false and you need to access objects in the SYS schema, then you must be granted explicit object privileges. The following roles, which can be granted to the database administrator, also allow access to dictionary objects:

• SELECT_CATALOG_ROLE
• EXECUTE_CATALOG_ROLE
• DELETE_CATALOG_ROLE

Example :

SQL> grant select any table to scott;

Grant succeeded.

SQL> show parameter O7_DICTIONARY_ACCESSIBILITY;

NAME                                 TYPE        VALUE

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

O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE

SQL> conn scott/tiger

Connected.

SQL> select * from dba_users;

select * from dba_users               *

ERROR at line 1: ORA-00942: table or view does not exist

SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                  2226912 bytes

Variable Size             687867168 bytes

Database Buffers          905969664 bytes

Redo Buffers                7348224 bytes

Database mounted. Database opened.

SQL> show parameter O7_DICTIONARY_ACCESSIBILITY;

NAME                                 TYPE        VALUE

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

O7_DICTIONARY_ACCESSIBILITY          boolean     TRUE

SQL> conn scott/tiger
 Connected.

SQL> select count(username) from dba_users;

COUNT(USERNAME)

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

45

Ref : Oracle Document