Oracle VPD (虛擬專用資料庫) 實戰完整教學：Context、Policy Function 與 DBMS_RLS 策略應用

VPD 核心原理

Virtual Private Database (VPD) 透過安全性策略，自動為用戶 SQL 附加 WHERE 子句，實現**資料列級存取控制** (Row-Level Security)。用戶查詢同一張表，看到的資料依權限動態過濾，應用端無需修改 SQL。

完整實作流程

1. 準備測試資料

-- 業務表：服裝、圖書
CREATE TABLE VPDSAMPLE_CLOTHING (CLOTHING_ID NUMBER, TYPE VARCHAR2(30), BRAND VARCHAR2(30), DESCRIPTION VARCHAR2(100));
CREATE TABLE VPDSAMPLE_BOOKS (BOOK_ID NUMBER, NAME VARCHAR2(30), AUTHOR VARCHAR2(20));

INSERT INTO VPDSAMPLE_CLOTHING VALUES (10002, 'JACKET', 'ABC', 'AUTUMN STYLE');
INSERT INTO VPDSAMPLE_CLOTHING VALUES (10003, 'T-SHIRT', 'XYZ', 'SUMMER STYLE');

INSERT INTO VPDSAMPLE_BOOKS VALUES (10005, 'COUNTRY DRIVING', 'PETER HESSLER');
INSERT INTO VPDSAMPLE_BOOKS VALUES (10006, 'LIFE WITHOUT LIMITS', 'NICK VUJICIC');

-- 用戶權限對應表
CREATE TABLE VPDSAMPLE_USERS (USER_NAME VARCHAR2(20), USER_PRIVILEGE NUMBER);
INSERT INTO VPDSAMPLE_USERS VALUES ('JACK', 1);
INSERT INTO VPDSAMPLE_USERS VALUES ('ROSE', 2);

-- 權限-物件對應表 (核心映射)
CREATE TABLE VPDSAMPLE_PRIVILEGES (PRIVILEGE_ID NUMBER, OBJECT_ID NUMBER, OBJECT_TYPE VARCHAR2(20));
-- PRIVILEGE 1 可見 CLOTHING 10002, BOOKS 10005
-- PRIVILEGE 2 可見 CLOTHING 10003, BOOKS 10006

2. 建立 Application Context (命名空間)

-- SYS 授權
GRANT CREATE ANY CONTEXT TO USER1;

-- 建立全域共享 Context
CREATE OR REPLACE CONTEXT VPD USING PKG_VPDSAMPLE ACCESSED GLOBALLY;
-- USING 後接套件名，限制只能透過該套件修改 Context (安全性)
-- ACCESSED GLOBALLY：跨 Session 共享，否則僅 Session 級

3. 建立政策套件 PKG_VPDSAMPLE

CREATE OR REPLACE PACKAGE PKG_VPDSAMPLE IS
  PROCEDURE ENABLE_VPD;
  PROCEDURE DISABLE_VPD;
  PROCEDURE SET_CONTEXT (P_USER_NAME IN VARCHAR2);
  FUNCTION GEN_VPD_PREDICATE (P_COLUMN_NAME IN VARCHAR2) RETURN VARCHAR2;
  FUNCTION APPLY_VPD_CLOTHING (P1 IN VARCHAR2, P2 IN VARCHAR2) RETURN VARCHAR2;
  FUNCTION APPLY_VPD_BOOKS (P1 IN VARCHAR2, P2 IN VARCHAR2) RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY PKG_VPDSAMPLE IS
  -- 啟用/停用 VPD 全域開關
  PROCEDURE ENABLE_VPD IS
  BEGIN
    DBMS_SESSION.SET_CONTEXT('VPD', 'ENABLE', '1');
  END;

  PROCEDURE DISABLE_VPD IS
  BEGIN
    DBMS_SESSION.SET_CONTEXT('VPD', 'ENABLE', '0');
  END;

  -- 應用登入後呼叫：寫入用戶權限至 USERENV.CLIENT_IDENTIFIER
  PROCEDURE SET_CONTEXT (P_USER_NAME IN VARCHAR2) IS
    L_PRIV VARCHAR2(10);
  BEGIN
    SELECT USER_PRIVILEGE INTO L_PRIV FROM VPDSAMPLE_USERS WHERE USER_NAME = P_USER_NAME;
    DBMS_SESSION.SET_IDENTIFIER(L_PRIV);  -- 寫入預定義 Context USERENV
  END;

  -- 核心：動態產生 WHERE 子句
  FUNCTION GEN_VPD_PREDICATE (P_COLUMN_NAME IN VARCHAR2) RETURN VARCHAR2 IS
    L_FLAG VARCHAR2(1);
    L_PRIV VARCHAR2(10);
  BEGIN
    L_FLAG := NVL(SYS_CONTEXT('VPD', 'ENABLE'), '0');
    IF L_FLAG = '0' THEN RETURN NULL; END IF;  -- VPD 未啟用

    L_PRIV := SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER');
    IF L_PRIV IS NULL THEN RETURN '1=2'; END IF; -- 無權限標識，回傳永假條件

    RETURN P_COLUMN_NAME || ' IN (SELECT OBJECT_ID FROM VPDSAMPLE_PRIVILEGES WHERE PRIVILEGE_ID = ' || L_PRIV || ')';
  END;

  -- 兩張表的 Policy Function，差別僅在傳入欄位名
  FUNCTION APPLY_VPD_CLOTHING (P1 IN VARCHAR2, P2 IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN RETURN GEN_VPD_PREDICATE('CLOTHING_ID'); END;

  FUNCTION APPLY_VPD_BOOKS (P1 IN VARCHAR2, P2 IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN RETURN GEN_VPD_PREDICATE('BOOK_ID'); END;
END;
/

4. 套用 VPD 策略 (DBMS_RLS)

GRANT EXECUTE ON DBMS_RLS TO USER1;

BEGIN
  DBMS_RLS.ADD_POLICY(
    OBJECT_SCHEMA => 'USER1',
    OBJECT_NAME   => 'VPDSAMPLE_CLOTHING',
    POLICY_NAME   => 'POL_CLOTHING',
    FUNCTION_SCHEMA => 'USER1',
    POLICY_FUNCTION => 'APPLY_VPD_CLOTHING',
    STATEMENT_TYPES => 'SELECT,INSERT,UPDATE,DELETE',
    UPDATE_CHECK => TRUE
  );

  DBMS_RLS.ADD_POLICY(
    OBJECT_SCHEMA => 'USER1',
    OBJECT_NAME   => 'VPDSAMPLE_BOOKS',
    POLICY_NAME   => 'POL_BOOKS',
    FUNCTION_SCHEMA => 'USER1',
    POLICY_FUNCTION => 'APPLY_VPD_BOOKS',
    STATEMENT_TYPES => 'SELECT,INSERT,UPDATE,DELETE',
    UPDATE_CHECK => TRUE
  );
END;
/

5. 測試驗證

-- 啟用 VPD
EXEC PKG_VPDSAMPLE.ENABLE_VPD;

-- 設定 JACK (Privilege 1)
EXEC PKG_VPDSAMPLE.SET_CONTEXT('JACK');
SELECT * FROM VPDSAMPLE_CLOTHING;  -- 僅回傳 10002
SELECT * FROM VPDSAMPLE_BOOKS;     -- 僅回傳 10005

-- 切換 ROSE (Privilege 2)
EXEC PKG_VPDSAMPLE.SET_CONTEXT('ROSE');
SELECT * FROM VPDSAMPLE_CLOTHING;  -- 僅回傳 10003
SELECT * FROM VPDSAMPLE_BOOKS;     -- 僅回傳 10006

6. 移除策略

BEGIN
  DBMS_RLS.DROP_POLICY('USER1', 'VPDSAMPLE_CLOTHING', 'POL_CLOTHING');
  DBMS_RLS.DROP_POLICY('USER1', 'VPDSAMPLE_BOOKS', 'POL_BOOKS');
END;
/

關鍵設計要點總結

總結

VPD 將資料存取控制從應用層下沉至資料庫核心，實現「同一 SQL、不同用戶、不同結果」的透明安全性。核心在於：Context 保存狀態 → Policy Function 依狀態產生謂詞 → DBMS_RLS 自動改寫查詢。適合多租戶、資料隔離、合規管控等場景。