查看Oracle用戶權限的七大標準方法：從用戶枚舉到特權審計

方法一：枚舉所有用戶

-- 全庫用戶（需DBA）
SELECT username, user_id, created, default_tablespace, temporary_tablespace, profile, account_status
FROM DBA_USERS ORDER BY username;

-- 當前用戶可見用戶
SELECT * FROM ALL_USERS ORDER BY USERNAME;

-- 當前用戶自身信息
SELECT * FROM USER_USERS;

方法二：查看系統權限（直接授予）

-- 全庫（需DBA）
SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION FROM DBA_SYS_PRIVS ORDER BY GRANTEE, PRIVILEGE;

-- 當前用戶
SELECT PRIVILEGE, ADMIN_OPTION FROM USER_SYS_PRIVS ORDER BY PRIVILEGE;

方法三：查看角色系統權限（角色定義層面）

-- 僅當前登錄用戶擁有的角色（需該角色密碼/非密碼角色）
SELECT ROLE, PRIVILEGE, ADMIN_OPTION FROM ROLE_SYS_PRIVS ORDER BY ROLE, PRIVILEGE;

方法四：查看對象權限

-- 全庫（需DBA）
SELECT GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE, GRANTABLE, HIERARCHY
FROM DBA_TAB_PRIVS ORDER BY GRANTEE, OWNER, TABLE_NAME;

-- 當前用戶可見
SELECT * FROM ALL_TAB_PRIVS ORDER BY GRANTEE, OWNER, TABLE_NAME;

-- 當前用戶擁有/授予
SELECT * FROM USER_TAB_PRIVS ORDER BY OWNER, TABLE_NAME;

方法五：查看所有角色

SELECT ROLE, PASSWORD_REQUIRED, AUTHENTICATION_TYPE, COMMON, ORACLE_MAINTAINED, INHERITED
FROM DBA_ROLES ORDER BY ROLE;

方法六：查看用戶/角色擁有的角色

-- 全庫（需DBA）
SELECT GRANTEE, GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE, COMMON, INHERITED
FROM DBA_ROLE_PRIVS ORDER BY GRANTEE, GRANTED_ROLE;

-- 當前用戶
SELECT GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE FROM USER_ROLE_PRIVS ORDER BY GRANTED_ROLE;

方法七：查看特權用戶（口令文件成員）

SELECT USERNAME, SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, SYSKM, CON_ID
FROM V$PWFILE_USERS
ORDER BY USERNAME;

一鍵權限掃描腳本（生成用戶權限報表）

SET LINES 300 PAGES 1000 TRIMSPOOL ON
SPOOL user_priv_report_&_CONNECT_IDENTIFIER._&_DATE.lst

PROMPT ===== 1. ALL USERS =====
SELECT * FROM DBA_USERS ORDER BY USERNAME;

PROMPT ===== 2. SYSTEM PRIVILEGES =====
SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION FROM DBA_SYS_PRIVS ORDER BY GRANTEE, PRIVILEGE;

PROMPT ===== 3. ROLE PRIVILEGES =====
SELECT GRANTEE, GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE FROM DBA_ROLE_PRIVS ORDER BY GRANTEE, GRANTED_ROLE;

PROMPT ===== 4. OBJECT PRIVILEGES =====
SELECT GRANTEE, OWNER, TABLE_NAME, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS ORDER BY GRANTEE, OWNER, TABLE_NAME;

PROMPT ===== 5. PASSWORD FILE USERS =====
SELECT * FROM V$PWFILE_USERS;

SPOOL OFF

總結

七大方法覆蓋用戶枚舉 → 系統權限 → 角色權限 → 對象權限 → 角色定義 → 角色授予 → 特權用戶全鏈路。將腳本納入月度巡檢，配合DIFF對比歷史報表，實現權限變更零感知發現。