傳統 Grant 權限模式痛點
- 需對每個使用者/角色逐一授予表級 SELECT/INSERT/UPDATE/DELETE。
- 權限分散、難以追蹤、易發生過度授權。
- 應用程式直接拼裝 SQL，注入風險高、難以統一管控。
Grant Execute 模式核心：定義者權限
將所有資料存取邏輯封裝於 PL/SQL Package/Procedure/Function，使用者僅獲授 EXECUTE ON pkg_api。執行時以定義者 (Definer) 身分擁有底層物件權限，使用者本身無任何直接物件權限。
-- 1. 建立 API 套件 (Definer's Rights 預設)
CREATE OR REPLACE PACKAGE pkg_hr_api AUTHID DEFINER IS
PROCEDURE hire_emp(p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER);
FUNCTION get_emp_sal(p_empno NUMBER) RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_hr_api IS
PROCEDURE hire_emp(p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER) IS
BEGIN
INSERT INTO emp(empno, ename, job, sal, hiredate) VALUES (p_empno, p_ename, p_job, p_sal, SYSDATE);
END;
FUNCTION get_emp_sal(p_empno NUMBER) RETURN NUMBER IS
v_sal NUMBER;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno = p_empno;
RETURN v_sal;
END;
END;
/
-- 2. 僅授予 EXECUTE
GRANT EXECUTE ON pkg_hr_api TO app_user;
-- 3. 使用者呼叫
BEGIN pkg_hr_api.hire_emp(9999, 'NEW', 'CLERK', 3000); END;
SELECT pkg_hr_api.get_emp_sal(7788) FROM dual;
核心優勢
| 優勢 | 說明 |
|---|
| 程式碼集中管理 | 所有 SQL/PLSQL 存於資料字典，易審查、修改、調優、版本控制 |
| 執行效能極致 | 11g+ 可 DBMS_SHARED_POOL.KEEP('PKG_HR_API') 鎖定 Library Cache，消除 Hard Parse |
| 資料與行為封裝 | 類似 OO 封裝，Member Method 綁定物件，業務邏輯內聚 |
| 資料庫程式碼隔離 | 前端無 SQL，移植資料庫只需重寫 Package 體，前端不變 |
| 精細存取控制 | 行級/列級/條件式權限寫在 PL/SQL 內 (如 VPD、自訂檢查) |
| 拒絕後門 | 使用者無直接物件權限，無法繞過 API 任意查詢/修改 |
對比：調用者權限
CREATE OR REPLACE PACKAGE pkg_util AUTHID CURRENT_USER IS ... -- Invoker's Rights
- 執行時使用調用者自身權限，需額外 GRANT 底層物件權限。
- 適用於通用工具套件 (如動態 DDL、中繼資料查詢)，不適合核心業務 API。
局限性與挑戰
| 局限 | 影響 | 緩解策略 |
|---|
| 需詳盡前期設計 | 敏捷/原型開發不適合 | 核心穩定模組採用，邊緣功能保留直連 |
| 程式語言限於 PL/SQL/Java | 無法直接用 C/Python 寫邏輯 | 外部程序 (Extproc) / Java Stored Procedure 橋接 |
| 審計困難 | 難以產生「誰能存取什麼表」矩陣 | 建立審計 Package 解析 DBA_DEPENDENCIES、USER_SOURCE 產出報表 |
| 動態 SQL 風險 | 若 Package 內大量 EXECUTE IMMEDIATE 失去靜態分析優勢 | 嚴格 Code Review、禁止拼接用戶輸入、使用 DBMS_ASSERT |
適用場景判斷
- 核心交易系統、高安全合規要求 (金融、醫療、政府)。
- 多前端應用 (Web、Mobile、Batch) 共用資料庫存取邏輯。
- 需實作複雜行級安全 (VPD/OLS) 或跨表業務不變式。
- 資料庫版本升級/移植頻繁，需屏蔽物件結構變更。
實務建議
- 採用 API Layer 設計模式：每個業務域一個 Package (pkg_hr, pkg_fin, pkg_inv)。
- 所有 DML 走 Package，嚴禁 前端直發 INSERT/UPDATE/DELETE。
- 查詢提供 Ref Cursor 回傳，支援分頁、排序參數化。
- 錯誤碼標準化：
RAISE_APPLICATION_ERROR(-20xxx, 'msg') 統一前端處理。
- 定期執行
DBMS_SHARED_POOL.KEEP 釘住熱點 Package，監控 V$DB_OBJECT_CACHE。
