Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or SELECT ANY TABLE

In this topic I will try to make you understand the differences between SELECT ANY DICTIONARY privilege, SELECT ANY TABLE privilege and SELECT_CATALOG_ROLE.

Before proceed it is nice if you remember that ,

•If you have O7_DICTIONARY_ACCESSIBILITY=TRUE then SELECT ANY TABLE privilege provides access to all SYS and non-SYS objects.

•If you have O7_DICTIONARY_ACCESSIBILITY=FALSE then SELECT ANY TABLE privilege provides access only to non-SYS objects.

•If only SELECT_CATALOG_ROLE is enabled then it provides access to all SYS views only.

•If only SELECT ANY DICTIONARY privilege is enabled then it provides access to SYS schema objects only.

•If both SELECT ANY TABLE and SELECT any DICTIONARY privilege is enabled then it allow access to all SYS and non-SYS objects.

•SELECT ANY DICTIONARY privilege and SELECT_CATALOG_ROLE has no affect over O7_DICTIONARY_ACCESSIBILITY settings.

To make the scenario more clear I will demonstrate an example over
1)ARJU schmea table named A. And over two
2)SYS schema objects OBJ$ Table and
3)SYS schema DBA_USERS view.

SQL> select object_type , object_name from dba_objects where object_name in ('OBJ$' ,'DBA_USERS') and owner='SYS';

OBJECT_TYPE OBJECT_NAME
------------------- --------------------
VIEW DBA_USERS
TABLE OBJ$

Workaround Example:
----------------------

A)Secnario 1:(When O7_DICTIONARY_ACCESSIBILITY is set to FALSE)
------------------
SQL> create user t identified by t;
User created.

SQL> grant create session to t;
Grant succeeded.

Have only Create Session Privilege
-------------------------------------
SQL> conn t/t
Connected.

SQL> select * from user_tables;
no rows selected

SQL> select * from arju.a;
select * from arju.a
*
ERROR at line 1:
ORA-00942: table or view does not exist

Have only Select Any Table Privilege
-----------------------------------
SQL> conn arju/a
Connected.

SQL> grant select any table to t;
Grant succeeded.

SQL> conn t/t
Connected.

User T can select Arju schema's obejct but failed on SYS schema objects.

SQL> select count(*) from arju.a;

COUNT(*)
----------
1
SQL> select count(*) from dba_users;
select count(*) from dba_users
*
ERROR at line 1:
ORA-00942: table or view does not exist


Have select_catalog_role only
---------------------------------

SQL> conn arju/a
Connected.

SQL> revoke select any table from t;
Revoke succeeded.

SQL> grant select_catalog_role to t;
Grant succeeded.

SQL> conn t/t
Connected.

User T can only select SYS schema Views.

SQL> select count(*) from dba_users;
COUNT(*)
----------
23

SQL> select * from arju.t;
select * from arju.t
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select count(*) from sys.obj$;
select count(*) from sys.obj$
*
ERROR at line 1:
ORA-00942: table or view does not exist



Have only Select Any Dictionary Privilege
-----------------------------------------------
SQL> conn arju/a
Connected.

SQL> revoke select_catalog_role from t;
Revoke succeeded.

SQL> grant select any dictionary to t;
Grant succeeded.

SQL> conn t/t
Connected.

User T can only select SYS schema objects.
SQL> select count(*) from dba_users;
COUNT(*)
----------
23

SQL> select count(*) from sys.obj$;
COUNT(*)
----------
51053

SQL> select * from arju.a;
select * from arju.a
*
ERROR at line 1:
ORA-00942: table or view does not exist

Have both SELECT ANY TABLE and SELECT ANY DICTIONARY Privilege
---------------------------------------------------------------------
Both system privileges together allow access to all SYS and non-SYS objects.

SQL> grant select any table , select any dictionary to t;
Grant succeeded.

SQL> conn t/t
Connected.

SQL> select count(*) from sys.obj$;
COUNT(*)
----------
51053

SQL> select count(*) from dba_users;
COUNT(*)
----------
23

SQL> select count(*) from arju.a;
COUNT(*)
----------
1


B)Scenario 2:(When O7_DICTIONARY_ACCESSIBILITY is set to TRUE)
----------------------------------------------------------------
Has only SELECT ANY TABLE privilege
-----------------------------------------
User T can now select all SYS and NO-SYS objects.

Related Documents:
---------------------

What is O7_DICTIONARY_ACCESSIBILITY