advertisements
_____________________________________________________________________________________________________________________
O7_DICTIONARY_ACCESSIBILITY Parameter: It is a Boolean parameter which controls the access to the objects in the sys schema. O7 indicates Oracle 7. It is a Boolean variable with values true or false. The default value for this parameter is false which will restrict the access to the sys objects.
SELECT_CATALOG_ROLE: As the name indicates it is role which will provide the SELECT access ONLY on all data dictionary views to the users.
SELECT_ANY_DICTIONARY: It is system privilege which allow the user to select or query access to any object in the SYS schema.
SELECT ANY TABLE: It is a privilege which allows the users to select or access the tables and views from the other schemas. It does not allow the access to any of the system objects or data dictionary views.
How These Work With O7_DICTIONARY_ACCESSIBILITY parameter?
Scenario 1: O7_DICTIONARY_ACCESSIBILITY = False (Default)
SQL> connect scott/tiger
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
CANDIDATE TABLE
OBJECT_LIST TABLE
SQL> connect temp/temp
Connected.
a. With SELECT ANY TABLE Privilege
SQL> connect / as sysdba
Connected.
SQL> grant select any table to temp;
Grant succeeded.
SQL> connect temp/temp
Connected.
SQL> select count(*) from scott.candidate;
COUNT(*)
----------
2
SQL> select count(*) from dba_tables;
select count(*) from dba_tables
*
ERROR at line 1:
ORA-00942: table or view does not exist
b. With SELECT_CATALOG_ROLE
SQL> connect / as sysdba
Connected.
SQL> revoke select any table from temp;
Revoke succeeded.
SQL> grant select_catalog_role to temp;
Grant succeeded.
SQL> connect temp/temp
Connected.
SQL> select count(*) from sys.v$parameter;
select count(*) from sys.v$parameter
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from v$parameter;
COUNT(*)
----------
350
SQL> select count(*) from scott.candidate;
select count(*) from scott.candidate
*
ERROR at line 1:
ORA-00942: table or view does not exist
c. With SELECT ANY TABLE and SELECT ANY DICTIONARY
SQL> connect / as sysdba
Connected.
SQL> revoke select_catalog_role from temp;
Revoke succeeded.
SQL> grant select any table, select any dictionary to temp;
Grant succeeded.
SQL> connect temp/temp
Connected.
SQL> select count(*) from scott.candidate;
COUNT(*)
----------
2
SQL> select count(*) from v$parameter;
COUNT(*)
----------
350
Scenario 2: O7_DICTIONARY_ACCESSIBILITY = TRUE
There is no complication if the parameter is set as true. You just need to give SELECT ANY TABLE privilege to temp user to access any objects (sys and non-sys).
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment