ITPub博客

首页 > Linux操作系统 > Linux操作系统 > select any dictionary与select_catalog_role的区别

select any dictionary与select_catalog_role的区别

原创 Linux操作系统 作者:perfychi 时间:2012-07-13 17:49:04 0 删除 编辑

select any dictionary与select_catalog_role的区别

分类: Oracle - PL/SQL 374人阅读 评论(0) 收藏 举报
 

select any dictionary与select_catalog_role

相同之处,有了这两个中的一个,基本就可以查询数据字典

不同之处:

1、select any dictionary是一种系统权限(system privilege),而select_catalog_role 是一种角色(a role)。

2、角色的话需要重新登录或者显式的set role 来生效,而赋予系统权限是立即生效的。(P.S. 同样revoke权限也是立即生效)

3、select_catalog_role可以查看一些数据字典的视图·(可以看role的定义),如dba_之类的,而select any dictionary可以查看sys的表,select_catalog_role看不到。

下面具体验证一下:

2、角色的话需要重新登录或者显式的set role 来生效,而赋予系统权限是立即生效的。(P.S. 同样revoke权限也是立即生效)

select any dictionary立即生效

同时开两个会话,查看情况。从上至下,按顺序。

 sys@test10gr2> select * from dba_role_privs where grantee = 'TEST_USER';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
TEST_USER                    CONNECT                        NO  YES
TEST_USER                    RESOURCE                       NO  YES

1、TEST_USER 只有最基本的CONNECT和RESOURCE  角色,其它的表权限也没有。

 
 

TEST_USER@test10gr2> select count(*) from v$session;   
select count(*) from v$session
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

2、此时看不到 v$session


sys@test10gr2> GRANT SELECT ANY DICTIONARY TO TEST_USER; 

Grant succeeded.

3、赋予SELECT ANY DICTIONARY系统权限

 
 

 TEST_USER@test10gr2>  select count(*) from v$session;  

  COUNT(*)
----------
        73

4、立即生效,可以查看到v$session

 sys@test10gr2> REVOKE SELECT ANY DICTIONARY  FROM TEST_USER;

Revoke succeeded.

5、收回SELECT ANY DICTIONARY系统权限

 
 

 TEST_USER@test10gr2>  select count(*) from v$session;
 select count(*) from v$session
                      *
ERROR at line 1:
ORA-00942: table or view does not exist

6、立即生效,无法查看到v$session

select_catalog_role 无法立即生效

 sys@test10gr2> GRANT SELECT_CATALOG_ROLE TO TEST_USER;   

Grant succeeded.

1、赋予SELECT ANY DICTIONARY角色

 
 

TEST_USER@test10gr2>  select count(*) from v$session;
 select count(*) from v$session
                      *
ERROR at line 1:
ORA-00942: table or view does not exist

2、无法立即生效,使用set role即可,

revoke role也相同,不即时生效。


TEST_USER@test10gr2> set role SELECT_CATALOG_ROLE;

Role set.

TEST_USER@test10gr2>  select count(*) from v$session;

  COUNT(*)
----------
        74 

3、select_catalog_role可以查看一些数据字典的视图·,如dba_之类的,而select any dictionary可以查看sys的表。

select any dictionary 可以看到 SYS.ACCESS$表

 test_user@test10gr2> select * from dba_sys_privs where grantee = 'TEST_USER';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
TEST_USER                     UNLIMITED TABLESPACE                     NO
TEST_USER                     SELECT ANY DICTIONARY                    NO


test_user@test10gr2>  select * from dba_role_privs where grantee = 'TEST_USER';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
TEST_USER                      CONNECT                        NO  YES
TEST_USER                     RESOURCE                       NO  YES
 

test_user@test10gr2> desc SYS.ACCESS$
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 D_OBJ#                                                NOT NULL NUMBER
 ORDER#                                                NOT NULL NUMBER
 COLUMNS                                                        RAW(126)
 TYPES                                                 NOT NULL NUMBER

select_catalog_role 看不到

 test_user@test10gr2> select * from dba_sys_privs where grantee = 'TEST_USER';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
TEST_USER                     UNLIMITED TABLESPACE                     NO

test_user@test10gr2>  select * from dba_role_privs where grantee = 'TEST_USER';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
TEST_USER                    CONNECT                        NO  YES
TEST_USER                    RESOURCE                       NO  YES
TEST_USER                    SELECT_CATALOG_ROLE            NO  YES

test_user@test10gr2>  desc SYS.ACCESS$
ERROR:
ORA-04043: object SYS.ACCESS$ does not exist

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

请登录后发表评论 登录
全部评论

注册时间:2012-06-06

  • 博文量
    486
  • 访问量
    2696060