select any dictionary与select_catalog_role区别
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@TEST0924> desc dba_role_privs Name Null? Type ----------------------------------------------------- -------- ------------------------------------ GRANTEE VARCHAR2(30) GRANTED_ROLE NOT NULL VARCHAR2(30) ADMIN_OPTION VARCHAR2(3) DEFAULT_ROLE VARCHAR2(3) 同时开两个会话,查看情况。从上至下,按顺序。 sys@TEST0924> select * from dba_role_privs where grantee='TEST'; GRANTEE GRANTED_ROLE ADM DEF ------------------------------ ------------------------------ --- --- TEST RESOURCE NO YES TEST CONNECT NO YES
1、此时test用户只有resource和connect权限。 test@TEST0924> select file_id,file_name,tablespace_name from dba_data_files; select file_id,file_name,tablespace_name from dba_data_files * ERROR at line 1: ORA-00942: table or view does not exist 2、此时test用户无法访问到dba_data_files这个数据字典 sys@TEST0924> grant select any dictionary to test; Grant succeeded. 3、将select any dictionary这个权限给test用户 test@TEST0924> select file_id,file_name,tablespace_name from dba_data_files; FILE_ID FILE_NAME TABLESPACE_NAME ---------- -------------------------------------------------- ------------------------------ 4 +DATA/test0924/datafile/users.260.829650045 USERS 3 +DATA/test0924/datafile/inventory.266.829658135 INVENTORY 2 /u01/app/oracle/oradata/test0924/sysaux01.dbf SYSAUX 1 /u01/app/oracle/oradata/test0924/system01.dbf SYSTEM 5 /u01/app/oracle/oradata/test0924/example01.dbf EXAMPLE 6 +DATA/test0924/datafile/inventory03.dbf INVENTORY 8 /u01/app/oracle/oradata/test0924/undotbs02.dbf UNDOTBS2 9 /u01/app/oracle/oradata/test0924/undotbs01.dbf UNDOTBS1 10 /u01/app/oracle/oradata/test0924/inventory01.dbf INVENTORY 9 rows selected. 4、立即生效,可以查看到dba_data_files这个数据字典里面的内容 sys@TEST0924> revoke select any dictionary from test; Revoke succeeded. 5、回收select any dictionary权限 test@TEST0924> select file_id,file_name,tablespace_name from dba_data_files; select file_id,file_name,tablespace_name from dba_data_files * ERROR at line 1: ORA-00942: table or view does not exist 6、立即生效,已经无法查看到dba_data_files这个数据字典里面的内容 select_catalog_role 无法立即生效 sys@TEST0924> grant select_catalog_role to test; Grant succeeded. 1、授予test用户select_catalog_role角色 test@TEST0924> select file_id,file_name,tablespace_name from dba_data_files; select file_id,file_name,tablespace_name from dba_data_files * ERROR at line 1: ORA-00942: table or view does not exist 2、test用户无法访问 test@TEST0924> SELECT * FROM USER_ROLE_PRIVS; USERNAME GRANTED_ROLE ADM DEF OS_ ------------------------------ ------------------------------ --- --- --- TEST CONNECT NO YES NO TEST RESOURCE NO YES NO TEST SELECT_CATALOG_ROLE NO YES NO 3、即使他的SELECT_CATALOG_ROLE为yes也不能查询到。 test@TEST0924> set role SELECT_CATALOG_ROLE; Role set. test@TEST0924> select file_id,file_name,tablespace_name from dba_data_files; FILE_ID FILE_NAME TABLESPACE_NAME ---------- -------------------------------------------------- ------------------------------ 4 +DATA/test0924/datafile/users.260.829650045 USERS 3 +DATA/test0924/datafile/inventory.266.829658135 INVENTORY 2 /u01/app/oracle/oradata/test0924/sysaux01.dbf SYSAUX 1 /u01/app/oracle/oradata/test0924/system01.dbf SYSTEM 5 /u01/app/oracle/oradata/test0924/example01.dbf EXAMPLE 6 +DATA/test0924/datafile/inventory03.dbf INVENTORY 8 /u01/app/oracle/oradata/test0924/undotbs02.dbf UNDOTBS2 9 /u01/app/oracle/oradata/test0924/undotbs01.dbf UNDOTBS1 10 /u01/app/oracle/oradata/test0924/inventory01.dbf INVENTORY 9 rows selected.
4、必须使用set role,才能及时生效。