Skip to content

Difference between Select_Catalog_Role, Select Any Dictionary, Select Any Table and O7_DICTIONARY_ACCESSIBILITY

February 4, 2014

(A) SELECT/UPDATE ANY TABLE:

(1) Allows ANY user to select/update ANY user’s data.

(2) It will not allow access to select dictionary objects (base tables (like USER$, OBJ$) of dynamic performance views, views, packages, and synonyms) or any objects in the SYS schema. As by default parameter O7_DICTIONARY_ACCESSIBILITY=FALSE set to FALSE, so that malicious user with ANY (such as SELECT/UPDATE ANY TABLE) privilege could not access or alter data dictionary tables.

(3) But If you need to access to dictionary objects (base tables of dynamic performance views, views, packages, and synonyms) or any objects in the SYS schema, then you need to Set O7_DICTIONARY_ACCESSIBILITY=TRUE along with SELECT/UPDATE ANY TABLE privilege, Parameter change would need downtime.

Note: O7_DICTIONARY_ACCESSIBILITY=TRUE along with SELECT/UPDATE ANY TABLE is Risky. Since the integrity of your system can be compromised by their misuse.

(4) Access to data dictionary or any objects in the SYS schema with help of SELECT/UPDATE ANY TABLE would need parameter O7_DICTIONARY_ACCESSIBILITY to be TRUE it needs down time to set the same, as it cannot be changed dynamically.

(5)To access data dictionary without any Downtime, We have alternative to this, user would need to have System Privilege SELECT ANY DICTIONARY Or Role SELECT_CATALOG_ROLE.

SQL> Show USER

USER is “SYS”

SQL> grant SELECT ANY TABLE to mtest;

Grant succeeded.

When O7_DICTIONARY_ACCESSIBILITY =TRUE and SELECT ANY TABLE to mtest

==================================================================

SQL> conn mtest/mtest
Connected.

SQL> select name from v$datafile where rownum<=2;
 NAME
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\D041\SYSTEM01.DBF
C:\ORACLE\ORADATA\D041\SYSAUX01.DBF

SQL> select username from dba_users where rownum<=3;
 USERNAME
------------------------------
SYS
SYSTEM
OUTLN

SQL> select NAME,PASSWORD from sys.user$ where rownum<=3;
 NAME                           PASSWORD
------------------------------ ------------------------------
SYS                            8A8F025737A9097A
PUBLIC
CONNECT

SQL> set line 200
SQL>  select * from scott.emp where rownum<=3;
      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
SQL> create or replace procedure tp as
  2      v_num number;
  3  begin
  4      select count(1)
  5      into v_num
  6      from v$datafile;
  7  end;
  8  /
 
Procedure created.

When O7_DICTIONARY_ACCESSIBILITY =FALSE and SELECT ANY TABLE to mtest

==================================================================

SQL> conn mtest/mtest
Connected.
 
SQL> select name from v$datafile where rownum<=2;
select name from v$datafile where rownum<=2
                 *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> create or replace procedure tp as
  2      v_num number;
  3  begin
  4      select count(1)
  5      into v_num
  6      from v$datafile;
  7  end;
  8  /
Warning: Procedure created with compilation errors.
 
SQL> select username from dba_users where rownum<=3;
select username from dba_users where rownum<=3
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> select NAME,PASSWORD from sys.user$ where rownum<=3;
select NAME,PASSWORD from sys.user$ where rownum<=3
                              *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> set line 200
SQL> select * from scott.emp where rownum<=3;
EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH      CLERK           7902 17-DEC-80        800                    20
7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

(B) So what is the difference between SELECT ANY DICTIONARY and SELECT_CATALOG_ROLE?

1)      SELECT ANY DICTIONARY is a System privileges.

It allows user to access any object (like Views V$XX , DBA_XX  view etc) in the SYS schema, including tables (like USER$,OBJ$ owned by SYS) created in that schema.

It must be granted individually to each user requiring the privilege.

It is not included in GRANT ALL PRIVILEGES, nor can it be granted through a role.

Grant SELECT ANY DICTIONARY  To SCOTT;

2)      SELECT_CATALOG_ROLE is a Role.

It allows user to access ONLY data dictionary views (like Views V$XX , DBA_XX  view etc) in the SYS schema.

It will not allow access to SYS tables (like USER$, OBJ$ owned by SYS).

Grant SELECT_CATALOG_ROLE To SCOTT;

For more details for difference between SELECT ANY DICTIONARY and SELECT_CATALOG_ROLE, check below link.

http://arup.blogspot.in/2011/07/difference-between-select-any.html

http://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG124

http://docs.oracle.com/cd/E25054_01/network.1111/e16543/authorization.htm

Advertisements

From → General

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: