深入理解 Oracle 存儲過程中的角色與權限機制

角色 (Role) 是權限集合，可授予用戶。但 在 PL/SQL 編譯/執行時角色被停用，僅認可直接授予的物件權限。這是為了效能 (避免動態檢查角色狀態) 與安全 (防止權限提升)。

直接授權 vs 角色授權

-- 直接授權：存儲過程可見
GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO abel;

-- 角色授權：存儲過程不可見
GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO hr_role;
GRANT hr_role TO abel;  -- abel 有角色權限，但無法在 PL/SQL 中引用 employees

驗證範例

CREATE OR REPLACE PROCEDURE update_emp (p_emp_id NUMBER, p_sal NUMBER) AS
  v_dept_id employees.department_id%TYPE;
BEGIN
  SELECT department_id INTO v_dept_id FROM employees WHERE employee_id = p_emp_id;
  UPDATE employees SET salary = p_sal WHERE employee_id = p_emp_id;
  IF v_dept_id = 100 THEN
    UPDATE local_employees SET salary = p_sal WHERE employee_id = p_emp_id;
  END IF;
END;
/
-- 情況 1：ABEL 直接擁有 employees 權限 → 編譯成功
-- 情況 2：ABEL 僅透過 hr_role 擁有權限 → 編譯失敗 (PL/SQL: ORA-00942)

特例：PUBLIC 權限

GRANT ... TO PUBLIC 與直接授權效果相同，因 PUBLIC 非角色而是「所有用戶集合」。但生產環境極不建議對敏感表授權 PUBLIC。

定義者權限 vs 執行者權限

• 定義者權限 (預設)：以建立者權限執行，呼叫者無需物件權限。編譯時檢查定義者直接授權。
• 執行者權限 (AUTHID CURRENT_USER)：以呼叫者權限執行，呼叫者需直接授權 (角色同樣無效)。

最佳實務

1. 物件權限直接授予給需建立 PL/SQL 的開發帳號/角色帳號。
2. 生產環境以角色管理業務權限，部署 PL/SQL 時由 DBA 以物件擁有者身分編譯 (定義者權限)，執行階段不依賴呼叫者權限。
3. 避免在 PL/SQL 中動態 SQL (EXECUTE IMMEDIATE) 依賴角色權限，改用定義者權限封裝。

理解「PL/SQL 忽略角色」機制，能避開權限相關的編譯失敗與執行時錯誤，設計安全可控的資料庫應用架構。