在Oracle中有很多用於查許可權的檢視，但很多人在需要查許可權時會很困惑，不知道該用哪個檢視去查，這裡我列出幾個常見的用於查許可權的檢視及其用法：
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)

該檢視主要有以下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

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)

該檢視的名字包含‘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

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

該檢視用於查詢某個user擁有哪些系統許可權：
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

該檢視用於查詢某個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

Column	Datatype	NULL	Description
PRIVILEGE	VARCHAR2(40)	NOT NULL	Name of the privilege

該檢視用於查詢當前user擁有哪些系統許可權：
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

該檢視用於查詢當前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中幾個常見的用於查許可權的檢視

相關文章