Oracle 使用者權限一鍵查詢大全：DBA/ALL/USER 三層視圖速查手冊

掌握使用者擁有哪些權限，是安全稽核、問題排查、權限回收的基礎。Oracle 提供 DBA_ (全庫、需 DBA)、ALL_ (可存取)、USER_ (自身) 三層視圖族群。本文彙整使用者、系統權限、物件權限、角色、表空間、版本等 7 大類常用查詢，並說明三層視圖差異與適用場景，可直接收藏作巡檢腳本庫。

視圖層級差異速查

七大類核心查詢彙總

1. 使用者帳號資訊

-- 全庫使用者 (DBA)
SELECT username, account_status, default_tablespace, temporary_tablespace, created
FROM   dba_users;

-- 可存取使用者
SELECT * FROM all_users;

-- 當前使用者
SELECT * FROM user_users;

2. 系統權限

SELECT * FROM dba_sys_privs;   -- 全庫
SELECT * FROM all_sys_privs;   -- 可存取
SELECT * FROM user_sys_privs;  -- 自身

3. 物件權限

SELECT * FROM dba_tab_privs;   -- 全庫
SELECT * FROM all_tab_privs;   -- 可存取
SELECT * FROM user_tab_privs;  -- 自身

4. 角色資訊

-- 所有角色定義
SELECT * FROM dba_roles;

-- 使用者被授予的角色
SELECT * FROM dba_role_privs;   -- 全庫
SELECT * FROM user_role_privs;  -- 自身

5. 當前使用者預設表空間

SELECT username, default_tablespace FROM user_users;

6. 特定角色擁有的系統權限 (如 RESOURCE)

SELECT privilege
FROM   dba_sys_privs
WHERE  grantee = 'RESOURCE';

7. 進階：使用者完整權限矩陣 (系統+物件+角色)

-- 指定使用者
DEFINE target_user = 'SCOTT'

-- 系統權限 (直接+角色間接)
SELECT 'SYS' AS src, privilege FROM dba_sys_privs WHERE grantee = UPPER('&target_user')
UNION ALL
SELECT 'ROLE' AS src, rp.privilege
FROM   dba_role_privs rp, role_sys_privs rsp
WHERE  rp.grantee = UPPER('&target_user')
AND    rp.granted_role = rsp.role;

-- 物件權限 (直接+角色間接)
SELECT 'OBJ' AS src, owner, table_name, privilege
FROM   dba_tab_privs
WHERE  grantee = UPPER('&target_user')
UNION ALL
SELECT 'ROLE' AS src, tp.owner, tp.table_name, tp.privilege
FROM   dba_role_privs rp, dba_tab_privs tp
WHERE  rp.grantee = UPPER('&target_user')
AND    rp.granted_role = tp.grantee;

常用稽核腳本範例

帳號狀態巡檢

SELECT username, account_status, lock_date, expiry_date,
       TO_CHAR(last_login, 'YYYY-MM-DD HH24:MI') last_login
FROM   dba_users
WHERE  account_status NOT IN ('OPEN', 'EXPIRED(GRACE)')
ORDER  BY username;

高風險權限掃描

SELECT grantee, privilege
FROM   dba_sys_privs
WHERE  privilege IN ('DBA', 'SYSDBA', 'SYSOPER', 'SELECT ANY TABLE',
                     'CREATE ANY PROCEDURE', 'DROP ANY TABLE', 'GRANT ANY PRIVILEGE')
ORDER  BY privilege, grantee;

角色授權鏈追蹤

-- 遞歸查詢角色嵌套 (11gR2+)
WITH r_path(grantee, granted_role, lvl, path) AS (
  SELECT grantee, granted_role, 1, granted_role
  FROM   dba_role_privs
  WHERE  grantee = UPPER('&target_user')
  UNION ALL
  SELECT rp.grantee, rp.granted_role, rp.lvl+1, rp.path||'→'||rp.granted_role
  FROM   dba_role_privs rp, r_path r
  WHERE  rp.grantee = r.granted_role
  AND    r.lvl < 10  -- 防無限遞歸
)
SELECT * FROM r_path;

工具化建議

1. 將上述查詢封裝為 Shell/Python 巡檢腳本，定期產出 HTML/Excel 報表。
2. 對接 CMDB，自動比對「授權清單」與「實際權限」，差異即告警。
3. 納入 CI/CD 流程：部署前掃描權限腳本，禁止 ANY、DBA 類高風險授權上線。

熟練運用 DBA/ALL/USER 三層視圖，能以最低成本獲得最完整的權限可視性，是 DBA 與安全團隊的核心基本功。