Oracle 用户、角色、权限（系统权限、对象权限）的数据字典表
1 三者的字典表
1.1 用户
select * from dba_users;
select * from all_users;
select * from user_users;
1.2 角色
select * from dba_roles;
1.3 权限
分为系统权限与对象权限：
select * from system_privilege_map;
select * from table_privilege_map;
2 三者之间关系的字典表
这类关系字典表的表名后缀都包含"_privs"
2.1 用户与角色
用户拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;
select * from role_role_privs;
user_role_privs和role_role_privs 都是dba_role_privs的子集.
dba_role_privs的grantee字段包括用户名与角色名.
user_role_privs的username字段包括操作用户的用户名.
role_role_privs的role字段只是角色名.
2.2 用户与权限
用户拥有的系统权限：
select * from dba_sys_privs;
select * from user_sys_privs;
用户拥有的对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
select * from dba_col_privs;
select * from all_col_privs;
select * from user_col_privs;
2.3 角色与权限
查询授出去的对象权限（通常是属主自己查）
select * from role_sys_privs;
select * from role_tab_privs;
3 其他
查询授出去的对象权限（通常是属主自己查）
select * from user_tab_privs_made;
select * from all_tab_privs_made;
用户拥有的对象权限
select * from user_tab_privs_recd;
select * from all_tab_privs_recd;
用户分配出去的列的对象权限
select * from user_col_privs_made;
select * from all_col_privs_made;
用户拥有的关于列的对象权限
select * from user_col_privs_recd;
select * from all_col_privs_recd;
关于unlimited tablespace权限的特殊性：
1. 系统权限unlimited tablespace是隐含dba, resource角色中的一个系统权限.
2. 系统权限unlimited tablespace不能被授予role, 可以被授予用户.
3. 系统权限unlimited tablespace不会随着resource, dba被授予role而授予给用户.
1 实验1
SQL> create user u1 identified by u1;
User created.
SQL> grant connect, resource to u1;
Grant succeeded.
SQL> select * from dba_role_privs a where a.grantee='U1';
GRANTEE GRANTED_ROLE ADM DEF
-------------------- -------------------- --- ---
U1 RESOURCE NO YES
U1 CONNECT NO YES
SQL> select * from dba_sys_privs a where a.grantee='U1';
GRANTEE PRIVILEGE ADM
-------------------- -------------------- ---
U1 UNLIMITED TABLESPACE NO
SQL> revoke unlimited tablespace from u1;
Revoke succeeded.
SQL> select * from dba_sys_privs a where a.grantee='U1';
no rows selected
2 实验2
SQL> create role r1;
Role created.
SQL> grant unlimited tablespace to r1;
ORA-01931: cannot grant UNLIMITED TABLESPACE to a role
不能受权给角色r1.
SQL> grant unlimited tablespace to u1;
Grant succeeded.
不能受权给用户u1.
3 实验3
SQL> revoke resource from u1;
Revoke succeeded.
SQL> grant resource to r1;
Grant succeeded.
SQL> grant r1 to u1;
Grant succeeded.
SQL> select * from dba_role_privs a where a.grantee='U1';
GRANTEE GRANTED_ROLE ADM DEF
-------------------- -------------------- --- ---
U1 R1 NO YES
U1 CONNECT NO YES
SQL> select * from dba_sys_privs a where a.grantee='U1';
no rows selected
系统权限中没有unlimit tablespace系统权限.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/17179887/viewspace-702011/ ，如需转载，请注明出处，否则将追究法律责任。
