Thursday, May 1, 2008

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

6 comments:

Anonymous said...

Hi, This is the best article I have seen on the system privileges. I have a question. Does 'select any table' apply to views/materialised views?
I have a view myView (built on multiple views and tables) and I granted 'select any table' to my reporting role. However, the repoting role is only able to 'see' the view and not any data in it. A select count(*) on the view always returns 0 rows. But if I create a temporary table on the view as create aTable as select * from myView; and then query it, my reporting role can see all the data. I am confused. Why is this happening?

Arju said...

Example?

Anonymous said...

For example -
1. I have a vew myView which is built on view1, view2,..etc. The list of these views increases every day and myView gets refreshed.

2. I have a reporting role 'crystalrep' that needs to read data from myView.

3. I gave
grant select any table to crystalrep
This allows crystalrep to see the myView, but not the data in it (There are 40000 rows in myView) -

SQL> select count(*) from icm.myView;

COUNT(*)
----------
0
4. After this, I created a new table as
create table aTable as select * from myView;

and queried rows from aTable. This shows me 40000 rows.

Why the select on myView shows 0 rows and select on aTable shows 40000? I need the crystalrep to be able to see the data in myView. Please suggest what I should do.

Balvant. said...

Excellent Article Arju!!

Martin Nash said...

@Arju - Nicely explained.

@Anonymous - I have just stumbled on this post and thought I'd have a go at explaing the issue you were facing...

Without more information I can only think that of the following:

1) VPD is used in this database and your CRYSTALREP user is not being configured appropriate by VPD policy (dbms_rls.add_policy) to allow it to select any data.

2) view1, view2, etc could contain a where clauses that specifies "AND USER = 'ICM' or something equally important.

3) view1, view2, etc or MyView references one or more ALL_* (or possibly USER_*) views.

Based on what you have said so far I would suggest that option 2 is the most likely, although without more information about the code defining the view1, view2, etc it is not possible to confirm this.

The point in option 3 is that what you see in the ALL_* views depends on what permissions you have.

It might be that you worked it out a long time ago and if so please let me know where the problem was... If not feel free provide more information.

Martin

sundar said...

If required, You will have to logoff and log back in for select_catalog_role to work.