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