ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or SELECT ANY TABLE (ZT)

Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or SELECT ANY TABLE (ZT)

原创 Linux操作系统 作者:cqubityj 时间:2009-04-08 17:30:55 0 删除 编辑
http://arjudba.blogspot.com/2008/05/use-select-any-dictionary-or.html

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 wner='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.



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/228190/viewspace-586777/,如需转载,请注明出处,否则将追究法律责任。

下一篇: dbms_xplan in 10g(ZT)
请登录后发表评论 登录
全部评论

注册时间:2007-12-19

  • 博文量
    133
  • 访问量
    425969