Oracle 權限管理全景圖：角色、系統/物件權限、授權回收與生效機制

Oracle 權限體系採用「使用者 → 角色 → 權限」三層架構，支援細粒度到欄位級的存取控制。本文系統梳理角色建立、系統權限與物件權限授權語法、WITH ADMIN/GRANT OPTION 傳遞機制、回收規則、生效時機差異、預設角色設定，並釐清常見誤區，助您建立符合最小權限原則的安全模型。

核心概念對照

一、角色管理

1. 建立角色

-- 需密碼驗證啟用
CREATE ROLE clerk IDENTIFIED BY bicentennial;

-- 無需密碼 (預設 NOT IDENTIFIED)
CREATE ROLE app_readonly;

2. 角色授權系統權限

-- 授予建立 Session、Table、Procedure 權限給角色
GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO clerk;

-- 授予角色給使用者 (若角色有密碼需驗證)
GRANT clerk TO jward;
-- 或指定密碼啟用
GRANT clerk TO jward IDENTIFIED BY bicentennial;

3. WITH ADMIN OPTION 傳遞效力

GRANT new_dba TO michael WITH ADMIN OPTION;

獲得者可：

1. 將該角色/權限授予其他使用者/角色
2. 回收其他使用者的該角色/權限
3. 修改或 DROP 該角色

二、物件權限授權

1. 基本授權

-- 單一物件多權限
GRANT SELECT, INSERT, DELETE ON emp TO jfee, tsmith;

-- 全部物件權限 (ALL = SELECT/INSERT/UPDATE/DELETE/REFERENCES/ALTER/INDEX...)
GRANT ALL ON emp TO jfee;

2. WITH GRANT OPTION 傳遞效力

GRANT SELECT ON emp TO jfee WITH GRANT OPTION;

獲得者可：

1. 將該物件的 SELECT 權限授予第三方
2. 若擁有 CREATE VIEW，可基於此表建 View 並授權他人查詢 View

3. 欄位級授權 (細粒度控制)

-- 僅允許插入/修改 acct_no 欄位
GRANT INSERT (acct_no), UPDATE (acct_no) ON accounts TO scott;

三、權限回收規則

1. 系統權限回收

REVOKE CREATE TABLE FROM tsmith;

• 擁有 ADMIN OPTION 或 GRANT ANY PRIVILEGE 者可回收。
• ADMIN OPTION 本身不可單獨回收，需連同權限一起回收再重新授予 (不含 ADMIN OPTION)。

2. 物件權限回收

REVOKE ALL ON dept FROM human_resource;

• 回收者必須是原授予者，或擁有 GRANT ANY OBJECT PRIVILEGE。
• 級聯效應：若 A 授予 B (含 GRANT OPTION)，B 再授予 C。回收 A→B 時，C 的權限也會被級聯回收。

四、生效時機與 Session 角色控制

Session 中動態切換角色

-- 啟用特定角色 (若有密碼需帶密碼)
SET ROLE clerk IDENTIFIED BY bicentennial;

-- 啟用所有預設角色
SET ROLE ALL;

-- 停用所有角色
SET ROLE NONE;

-- 停用特定角色
SET ROLE ALL EXCEPT clerk;

五、預設角色設定

-- 登入時自動啟用的角色清單
ALTER USER jane DEFAULT ROLE payclerk, pettycash;

-- 取消所有預設角色
ALTER USER jane DEFAULT ROLE NONE;

-- 全部角色設為預設 (慎用)
ALTER USER jane DEFAULT ROLE ALL;

實務最佳實踐

1. 角色導向設計：定義 APP_READ、APP_WRITE、APP_ADMIN 等應用角色，而非直接授權給使用者。
2. 最小權限原則：避免授予 DBA、ANY 類系統權限；物件權限精確到欄位。
3. 審計追蹤：啟用 AUDIT GRANT, REVOKE, CREATE ROLE, ALTER ROLE 記錄權限變更。
4. 定期稽核：查詢 DBA_ROLE_PRIVS、DBA_SYS_PRIVS、DBA_TAB_PRIVS 產出權限矩陣報表。
5. 密碼角色謹慎用：需應用程式管理密碼，增加複雜度；優先使用 SECURE APPLICATION ROLE (PL/SQL 包驗證)。

快速查詢權限視圖

-- 使用者系統權限
SELECT * FROM USER_SYS_PRIVS;
-- 使用者物件權限
SELECT * FROM USER_TAB_PRIVS;
-- 使用者角色
SELECT * FROM USER_ROLE_PRIVS;
-- 角色系統權限
SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'RESOURCE';
-- 角色物件權限
SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'RESOURCE';

權限管理是資料庫安全的基石。透過角色封裝權限、善用 ADMIN/GRANT OPTION 控制傳遞鏈、理解生效時機差異，即可構建清晰、可稽核、可維運的授權體系。