Differences between select any dictionary and select_catalog_role

Select any dictionary and select_catalog_role

Similarities,With one of the two, you can query the data dictionary.

Differences:

1. Select any dictionary is a system permission (system privilege), while select_catalog_role is a role (a role ).

2. If a role needs to be logged on again or set to take effect explicitly, the system permission is granted immediately. (P.s. The revoke permission also takes effect immediately)

3. select_catalog_role can view views of some data dictionaries (you can see the definition of role), such as DBA _. Select any dictionary can view sys tables, but select_catalog_role cannot see them.

The following is a specific verification:

2,The role must be logged on again or explicitly set role to take effect, and the system permission is granted immediately. (P.s. The revoke permission also takes effect immediately)

Select any dictionary takes effect immediately

Enable two sessions at the same time to check the situation. From top to bottom, in order.

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 has only the most basic connect and resource roles, and has no other table permissions.
	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. You cannot see the V $ session
Sys @ test10gr2> grant select any dictionary to test_user; Grant succeeded. 3. Grant select any dictionary system Permissions
	Test_user @ test10gr2> select count (*) from V $ session; Count (*) ---------- 73 4. take effect immediately. You can view v $ session
Sys @ test10gr2> revoke select any dictionary from test_user; Revoke succeeded. 5. Revoke select any dictionary system Permissions
	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. It takes effect immediately. You cannot view the V $ session

Select_catalog_role cannot take effect immediately

Sys @ test10gr2> grant select_catalog_role to test_user; Grant succeeded. 1. Grant the select any dictionary role

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. It cannot take effect immediately. Use set role, The revoke role is the same and does not take effect immediately. Test_user @ test10gr2> set role select_catalog_role; Role set. Test_user @ test10gr2> select count (*) from V $ session; Count (*) ---------- 74

3,Select_catalog_role can view some data dictionary views, such as DBA _, while select any dictionary can view sys tables.

Select any dictionary: the SYS. Access $ table is displayed.

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 not visible

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

From: http://space.itpub.net/23650854/viewspace-688668