Oracle中几个常见的用于查权限的视图
在Oracle中有很多用于查权限的视图，但很多人在需要查权限时会很困惑，不知道该用哪个视图去查，这里我列出几个常见的用于查权限的视图及其用法：
1. DBA_ROLE_PRIVS
该视图主要有以下2个作用：
1） 查某个user或role拥有哪些role：
select * from DBA_ROLE_PRIVS where GRANTEE='FIRGTRS';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
FIRGTRS GTRS_DMM_UPDATE_ROLE NO YES
2） 查看某个role赋给了哪些user或role：
select * from DBA_ROLE_PRIVS where GRANTED_ROLE='GTRS_DMM_UPDATE_ROLE';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
GTRSOSA GTRS_DMM_UPDATE_ROLE NO YES
FIRGTRS GTRS_DMM_UPDATE_ROLE NO YES
GTRSSUP GTRS_DMM_UPDATE_ROLE NO YES
SYSTEM GTRS_DMM_UPDATE_ROLE YES YES
2. DBA_TAB_PRIVS
该视图的名字包含‘TAB’，且其中有一个column叫TABLE_NAME容易造成误解，其实该视图是用于查询在object上的权限，不仅仅table的权限。
select GRANTOR,GRANTEE,TABLE_NAME,PRIVILEGE from DBA_TAB_PRIVS where TABLE_NAME='PAYAGENT' order by GRANTEE;
GRANTOR GRANTEE TABLE_NAME PRIVILEGE
--------------- ------------------------------ ------------------------------ ---------------
GTRS DMM_ROLE PAYAGENT INSERT
GTRS DMM_ROLE PAYAGENT UPDATE
GTRS DMM_ROLE PAYAGENT DELETE
GTRS DMM_ROLE PAYAGENT SELECT
GTRS GTRS_DMM_READONLY_ROLE PAYAGENT SELECT
GTRS GTRS_DMM_UPDATE_ROLE PAYAGENT INSERT
GTRS GTRS_DMM_UPDATE_ROLE PAYAGENT DELETE
GTRS GTRS_DMM_UPDATE_ROLE PAYAGENT UPDATE
GTRS GTRS_DMM_UPDATE_ROLE PAYAGENT SELECT
GTRS GTRS_SUPPORT_READONLY_ROLE PAYAGENT SELECT
GTRS GTRS_SUPPORT_UPDATE_ROLE PAYAGENT UPDATE
GTRS GTRS_SUPPORT_UPDATE_ROLE PAYAGENT INSERT
GTRS GTRS_SUPPORT_UPDATE_ROLE PAYAGENT DELETE
GTRS GTRS_SUPPORT_UPDATE_ROLE PAYAGENT SELECT
GTRS SUPPORT_ROLE PAYAGENT SELECT
3. DBA_SYS_PRIVS
该视图用于查询某个user拥有哪些系统权限：
select * from DBA_SYS_PRIVS where GRANTEE='FIRGTRS';
GRANTEE PRIVILEGE ADM
------------------------------ --------------- ---
FIRGTRS CREATE SESSION NO
4. ROLE_SYS_PRIVS
该视图用于查询某个role拥有哪些系统权限：
select * from ROLE_SYS_PRIVS where ROLE='DBA_SUPPORT';
ROLE PRIVILEGE ADM
------------------------------ ------------------------------ ---
DBA_SUPPORT SELECT ANY SEQUENCE NO
DBA_SUPPORT SELECT ANY DICTIONARY NO
5. SESSION_PRIVS
该视图用于查询当前user拥有哪些系统权限：
select * from SESSION_PRIVS;
PRIVILEGE
------------------------------
CREATE SESSION
SELECT ANY SEQUENCE
SELECT ANY DICTIONARY
6. SESSION_ROLES
该视图用于查询当前user拥有哪些role：
select * from SESSION_ROLES;
ROLE
------------------------------
DBA_SUPPORT
CONNECT
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
7. 附注： WITH ADMIN OPTION和WITH GRANT OPTION
WITH ADMIN OPTION 是针对系统权限的，它的作用可以用下面这句话说明：
Only users who have been granted a specific system privilege with the ADMIN OPTION or users with the system privileges GRANT ANY PRIVILEGE or GRANT ANY OBJECT PRIVILEGE can grant or revoke system privileges to other users.
也就是说，对于某些权限大的user来说（比如DBA，一般拥有GRANT ANY PRIVILEGE和GRANT ANY OBJECT PRIVILEGE），WITH ADMIN OPTION对它们没有影响，因为它们本身就具有给其它user或role赋系统权限的权力；而对于一般的user来说，它们的权限都是DBA赋给它们的，如果在DBA赋给它们权限时加了WITH ADMIN OPTION, 则它们还可以把这些权限再赋给其它的user，否则不能，请看以下实验：
1） 首先用DBA账号（a105024)登陆数据库，并创建两个测试账号(testuser1, testuser2)：
A105024@O02DMS1>create user testuser1 identified by test1;
User created.
A105024@O02DMS1>create user testuser2 identified by test2;
User created.
2) 用DBA账号把 create session权限赋给测试账号1：
A105024@O02DMS1>grant CREATE SESSION to testuser1;
Grant succeeded.
3） 用测试账号1登陆数据库，并查看测试账号1的系统权限：
TESTUSER1@O02DMS1>select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TESTUSER1 CREATE SESSION NO
4） 用测试账号1尝试把create session赋给其它user：
TESTUSER@O02DMS1>grant CREATE SESSION to testuser2;
grant CREATE SESSION to testuser2
*
ERROR at line 1:
ORA-01031: insufficient privileges
出现权限不足的错误，是因为ADM那列的值为NO.
5） 用DBA账号把create session权限赋给测试账号1，并加上with admin option：
A105024@O02DMS1>grant CREATE SESSION to testuser1 with admin option;
Grant succeeded.
6) 查看测试账号1的系统权限：
TESTUSER1@O02DMS1>select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TESTUSER1 CREATE SESSION YES
7) 用测试账号1把create session赋给其它user：
TESTUSER@O02DMS1>grant CREATE SESSION to testuser2;
Grant succeeded.
WITH GRANT OPTION类似，只是它是针对对象权限的。
当前文章：Oracle中几个常见的用于查权限的视图
文章路径：http://ybzwz.com/article/ijhcgi.html
1. DBA_ROLE_PRIVS
| Column | Datatype | NULL | Description |
|---|---|---|---|
| GRANTEE | VARCHAR2(30) | Name of the user or role receiving the grant | |
| GRANTED_ROLE | VARCHAR2(30) | NOT NULL | Granted role name |
| ADMIN_OPTION | VARCHAR2(3) | Indicates whether the grant was with the ADMIN OPTION (YES) or not (NO) | |
| DEFAULT_ROLE | VARCHAR2(3) | Indicates whether the role is designated as a DEFAULT ROLE for the user (YES) or not (NO) |
1） 查某个user或role拥有哪些role：
select * from DBA_ROLE_PRIVS where GRANTEE='FIRGTRS';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
FIRGTRS GTRS_DMM_UPDATE_ROLE NO YES
2） 查看某个role赋给了哪些user或role：
select * from DBA_ROLE_PRIVS where GRANTED_ROLE='GTRS_DMM_UPDATE_ROLE';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
GTRSOSA GTRS_DMM_UPDATE_ROLE NO YES
FIRGTRS GTRS_DMM_UPDATE_ROLE NO YES
GTRSSUP GTRS_DMM_UPDATE_ROLE NO YES
SYSTEM GTRS_DMM_UPDATE_ROLE YES YES
2. DBA_TAB_PRIVS
| Column | Datatype | NULL | Description |
|---|---|---|---|
| GRANTEE | VARCHAR2(30) | NOT NULL | Name of the user to whom access was granted |
| OWNER | VARCHAR2(30) | NOT NULL | Owner of the object |
| TABLE_NAME | VARCHAR2(30) | NOT NULL | Name of the object. The object can be any object, including tables, packages, indexes, sequences, and so on. |
| GRANTOR | VARCHAR2(30) | NOT NULL | Name of the user who performed the grant |
| PRIVILEGE | VARCHAR2(40) | NOT NULL | Privilege on the object |
| GRANTABLE | VARCHAR2(3) | Indicates whether the privilege was granted with the GRANT OPTION (YES) or not (NO) | |
| HIERARCHY | VARCHAR2(3) | Indicates whether the privilege was granted with the HIERARCHY OPTION (YES) or not (NO) |
select GRANTOR,GRANTEE,TABLE_NAME,PRIVILEGE from DBA_TAB_PRIVS where TABLE_NAME='PAYAGENT' order by GRANTEE;
GRANTOR GRANTEE TABLE_NAME PRIVILEGE
--------------- ------------------------------ ------------------------------ ---------------
GTRS DMM_ROLE PAYAGENT INSERT
GTRS DMM_ROLE PAYAGENT UPDATE
GTRS DMM_ROLE PAYAGENT DELETE
GTRS DMM_ROLE PAYAGENT SELECT
GTRS GTRS_DMM_READONLY_ROLE PAYAGENT SELECT
GTRS GTRS_DMM_UPDATE_ROLE PAYAGENT INSERT
GTRS GTRS_DMM_UPDATE_ROLE PAYAGENT DELETE
GTRS GTRS_DMM_UPDATE_ROLE PAYAGENT UPDATE
GTRS GTRS_DMM_UPDATE_ROLE PAYAGENT SELECT
GTRS GTRS_SUPPORT_READONLY_ROLE PAYAGENT SELECT
GTRS GTRS_SUPPORT_UPDATE_ROLE PAYAGENT UPDATE
GTRS GTRS_SUPPORT_UPDATE_ROLE PAYAGENT INSERT
GTRS GTRS_SUPPORT_UPDATE_ROLE PAYAGENT DELETE
GTRS GTRS_SUPPORT_UPDATE_ROLE PAYAGENT SELECT
GTRS SUPPORT_ROLE PAYAGENT SELECT
3. DBA_SYS_PRIVS
| Column | Datatype | NULL | Description |
|---|---|---|---|
| GRANTEE | VARCHAR2(30) | NOT NULL | Grantee name, user, or role receiving the grant |
| PRIVILEGE | VARCHAR2(40) | NOT NULL | System privilege |
| ADMIN_OPTION | VARCHAR2(3) | Grant was with the ADMIN option |
select * from DBA_SYS_PRIVS where GRANTEE='FIRGTRS';
GRANTEE PRIVILEGE ADM
------------------------------ --------------- ---
FIRGTRS CREATE SESSION NO
4. ROLE_SYS_PRIVS
| Column | Datatype | NULL | Description |
|---|---|---|---|
| ROLE | VARCHAR2(30) | NOT NULL | Name of the role |
| PRIVILEGE | VARCHAR2(40) | NOT NULL | System privilege granted to the role |
| ADMIN_OPTION | VARCHAR2(3) | Signifies the grant was with the ADMIN option |
select * from ROLE_SYS_PRIVS where ROLE='DBA_SUPPORT';
ROLE PRIVILEGE ADM
------------------------------ ------------------------------ ---
DBA_SUPPORT SELECT ANY SEQUENCE NO
DBA_SUPPORT SELECT ANY DICTIONARY NO
5. SESSION_PRIVS
| Column | Datatype | NULL | Description |
|---|---|---|---|
| PRIVILEGE | VARCHAR2(40) | NOT NULL | Name of the privilege |
select * from SESSION_PRIVS;
PRIVILEGE
------------------------------
CREATE SESSION
SELECT ANY SEQUENCE
SELECT ANY DICTIONARY
6. SESSION_ROLES
| Column | Datatype | NULL | Description |
|---|---|---|---|
| ROLE | VARCHAR2(30) | NOT NULL | Name of the role |
select * from SESSION_ROLES;
ROLE
------------------------------
DBA_SUPPORT
CONNECT
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
7. 附注： WITH ADMIN OPTION和WITH GRANT OPTION
WITH ADMIN OPTION 是针对系统权限的，它的作用可以用下面这句话说明：
Only users who have been granted a specific system privilege with the ADMIN OPTION or users with the system privileges GRANT ANY PRIVILEGE or GRANT ANY OBJECT PRIVILEGE can grant or revoke system privileges to other users.
也就是说，对于某些权限大的user来说（比如DBA，一般拥有GRANT ANY PRIVILEGE和GRANT ANY OBJECT PRIVILEGE），WITH ADMIN OPTION对它们没有影响，因为它们本身就具有给其它user或role赋系统权限的权力；而对于一般的user来说，它们的权限都是DBA赋给它们的，如果在DBA赋给它们权限时加了WITH ADMIN OPTION, 则它们还可以把这些权限再赋给其它的user，否则不能，请看以下实验：
1） 首先用DBA账号（a105024)登陆数据库，并创建两个测试账号(testuser1, testuser2)：
A105024@O02DMS1>create user testuser1 identified by test1;
User created.
A105024@O02DMS1>create user testuser2 identified by test2;
User created.
2) 用DBA账号把 create session权限赋给测试账号1：
A105024@O02DMS1>grant CREATE SESSION to testuser1;
Grant succeeded.
3） 用测试账号1登陆数据库，并查看测试账号1的系统权限：
TESTUSER1@O02DMS1>select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TESTUSER1 CREATE SESSION NO
4） 用测试账号1尝试把create session赋给其它user：
TESTUSER@O02DMS1>grant CREATE SESSION to testuser2;
grant CREATE SESSION to testuser2
*
ERROR at line 1:
ORA-01031: insufficient privileges
出现权限不足的错误，是因为ADM那列的值为NO.
5） 用DBA账号把create session权限赋给测试账号1，并加上with admin option：
A105024@O02DMS1>grant CREATE SESSION to testuser1 with admin option;
Grant succeeded.
6) 查看测试账号1的系统权限：
TESTUSER1@O02DMS1>select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TESTUSER1 CREATE SESSION YES
7) 用测试账号1把create session赋给其它user：
TESTUSER@O02DMS1>grant CREATE SESSION to testuser2;
Grant succeeded.
WITH GRANT OPTION类似，只是它是针对对象权限的。
当前文章：Oracle中几个常见的用于查权限的视图
文章路径：http://ybzwz.com/article/ijhcgi.html
