【Oracle】VPD（Virtual Private Database）完全ガイド｜DBMS_RLS で行レベルセキュリティを実装してマルチテナントデータ分離まで解説

-- ポリシー関数: 現在のログインユーザーの部門IDに応じてフィルタリングする
CREATE OR REPLACE FUNCTION dept_security_policy(
    p_schema IN VARCHAR2,
    p_table  IN VARCHAR2
) RETURN VARCHAR2 AS
    v_dept_id NUMBER;
    v_user    VARCHAR2(100);
BEGIN
    v_user := SYS_CONTEXT('USERENV', 'SESSION_USER');

    -- SYS や DBA はすべてのデータを見られるようにする
    IF v_user IN ('SYS', 'SYSTEM', 'DBA_USER') THEN
        RETURN '';   -- 空文字 = フィルタリングなし（全件）
    END IF;

    -- ユーザーの部門IDを取得する
    BEGIN
        SELECT department_id INTO v_dept_id
        FROM user_dept_mapping
        WHERE username = v_user;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            -- マッピングがない場合は1=0（全件非表示）
            RETURN '1=0';
    END;

    -- WHERE 句に追加する条件文字列を返す（列名はポリシー適用テーブルの列）
    RETURN 'department_id = ' || v_dept_id;
END dept_security_policy;
/

-- employees テーブルに SELECT ポリシーを適用する
BEGIN
    DBMS_RLS.ADD_POLICY(
        object_schema   => 'HR',                       -- テーブルのスキーマ
        object_name     => 'EMPLOYEES',                -- 対象テーブル（またはビュー）
        policy_name     => 'DEPT_SELECT_POLICY',       -- ポリシー名（スキーマ内で一意）
        function_schema => 'HR',                       -- ポリシー関数のスキーマ
        policy_function => 'DEPT_SECURITY_POLICY',    -- ポリシー関数名
        statement_types => 'SELECT',                   -- 適用する SQL タイプ
        enable          => TRUE                         -- 即時有効化
    );
END;
/

-- INSERT / UPDATE / DELETE にも適用する（DML ポリシー）
BEGIN
    DBMS_RLS.ADD_POLICY(
        object_schema   => 'HR',
        object_name     => 'EMPLOYEES',
        policy_name     => 'DEPT_DML_POLICY',
        function_schema => 'HR',
        policy_function => 'DEPT_SECURITY_POLICY',
        statement_types => 'INSERT, UPDATE, DELETE',  -- 複数指定はカンマ区切り
        enable          => TRUE
    );
END;
/

-- ポリシーを一時的に無効にする
BEGIN
    DBMS_RLS.ENABLE_POLICY(
        object_schema => 'HR',
        object_name   => 'EMPLOYEES',
        policy_name   => 'DEPT_SELECT_POLICY',
        enable        => FALSE    -- FALSE で無効化
    );
END;
/

-- ポリシーを削除する
BEGIN
    DBMS_RLS.DROP_POLICY(
        object_schema => 'HR',
        object_name   => 'EMPLOYEES',
        policy_name   => 'DEPT_SELECT_POLICY'
    );
END;
/

-- アプリケーションコンテキストの名前空間を作成する
CREATE OR REPLACE CONTEXT tenant_ctx
    USING set_tenant_context_pkg;   -- コンテキストを設定できるパッケージを指定

-- コンテキストを設定するパッケージ
CREATE OR REPLACE PACKAGE set_tenant_context_pkg AS
    PROCEDURE set_tenant(p_tenant_id IN NUMBER);
    PROCEDURE clear_tenant;
END set_tenant_context_pkg;
/

CREATE OR REPLACE PACKAGE BODY set_tenant_context_pkg AS
    PROCEDURE set_tenant(p_tenant_id IN NUMBER) AS
    BEGIN
        DBMS_SESSION.SET_CONTEXT(
            namespace => 'TENANT_CTX',
            attribute => 'TENANT_ID',
            value     => TO_CHAR(p_tenant_id)
        );
    END set_tenant;

    PROCEDURE clear_tenant AS
    BEGIN
        DBMS_SESSION.CLEAR_CONTEXT('TENANT_CTX', NULL, 'TENANT_ID');
    END clear_tenant;
END set_tenant_context_pkg;
/

-- Application Context を使ったポリシー関数
CREATE OR REPLACE FUNCTION tenant_isolation_policy(
    p_schema IN VARCHAR2,
    p_table  IN VARCHAR2
) RETURN VARCHAR2 AS
    v_tenant_id VARCHAR2(20);
BEGIN
    -- Application Context からテナントIDを取得する
    v_tenant_id := SYS_CONTEXT('TENANT_CTX', 'TENANT_ID');

    IF v_tenant_id IS NULL THEN
        -- テナントIDが設定されていない場合はアクセス拒否
        RETURN '1=0';
    END IF;

    RETURN 'tenant_id = ' || v_tenant_id;
END tenant_isolation_policy;
/

-- orders テーブルにテナント分離ポリシーを適用する
BEGIN
    DBMS_RLS.ADD_POLICY(
        object_schema   => 'APP',
        object_name     => 'ORDERS',
        policy_name     => 'TENANT_ISOLATION',
        function_schema => 'APP',
        policy_function => 'TENANT_ISOLATION_POLICY',
        statement_types => 'SELECT, INSERT, UPDATE, DELETE',
        enable          => TRUE
    );
END;
/

-- アプリケーションからの使用例
-- ① ログイン時にテナントIDをセット
EXEC set_tenant_context_pkg.set_tenant(1001);

-- ② この後の SQL にポリシーが自動適用される
SELECT * FROM app.orders;   -- WHERE tenant_id = 1001 が自動付加
INSERT INTO app.orders(order_id, item) VALUES (1, 'test');   -- tenant_id=1001 の行として扱われる
-- ※ INSERT の場合、ポリシーで追加された WHERE 条件で新規行が「見える」かどうかをチェックする

-- ③ セッション終了時にクリア
EXEC set_tenant_context_pkg.clear_tenant;

-- 全ポリシーを一覧表示する
SELECT
    object_owner,
    object_name,
    policy_group,
    policy_name,
    pf_owner,             -- ポリシー関数のスキーマ
    package,              -- ポリシー関数のパッケージ名（スタンドアロン関数の場合 NULL）
    function,             -- ポリシー関数名
    sel,                  -- SELECT に適用: YES/NO
    ins,                  -- INSERT に適用: YES/NO
    upd,                  -- UPDATE に適用: YES/NO
    del,                  -- DELETE に適用: YES/NO
    enable                -- 有効: YES / 無効: NO
FROM DBA_POLICIES
WHERE object_owner = 'HR'
ORDER BY object_name, policy_name;

-- ポリシー適用後の SQL 変換結果を確認する（デバッグ用）
-- V$VPD_POLICY: 現在実行中のSQL に適用されているVPDポリシーを確認できる
SELECT object_name, policy_name, predicate
FROM V$VPD_POLICY;
-- predicate 列に、ポリシー関数が返した WHERE 句の条件文字列が表示される