【Oracle】SYS_CONTEXT・アプリケーションコンテキスト完全ガイド｜USERENV 組み込み情報・カスタムコンテキスト・VPD 連携まで解説

-- セッションのユーザー情報
SELECT SYS_CONTEXT('USERENV', 'SESSION_USER')   FROM DUAL;  -- ログインユーザー名（変更不可）
SELECT SYS_CONTEXT('USERENV', 'CURRENT_USER')   FROM DUAL;  -- 現在の実行ユーザー（PL/SQL INVOKER RIGHTS で変わる）
SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;  -- デフォルトスキーマ（ALTER SESSION SET CURRENT_SCHEMA で変更可能）
SELECT SYS_CONTEXT('USERENV', 'PROXY_USER')     FROM DUAL;  -- プロキシ認証のプロキシユーザー名（通常は NULL）

-- 接続・ネットワーク情報
SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS')   FROM DUAL;   -- クライアント IP アドレス
SELECT SYS_CONTEXT('USERENV', 'HOST')         FROM DUAL;   -- クライアントのホスト名
SELECT SYS_CONTEXT('USERENV', 'OS_USER')      FROM DUAL;   -- クライアントの OS ユーザー名
SELECT SYS_CONTEXT('USERENV', 'TERMINAL')     FROM DUAL;   -- クライアントのターミナル名
SELECT SYS_CONTEXT('USERENV', 'SERVICE_NAME') FROM DUAL;   -- 接続サービス名

-- データベース情報
SELECT SYS_CONTEXT('USERENV', 'DB_NAME')       FROM DUAL;  -- データベース名
SELECT SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME') FROM DUAL; -- Data Guard 環境でのユニーク名
SELECT SYS_CONTEXT('USERENV', 'INSTANCE')      FROM DUAL;  -- インスタンス番号（RAC）
SELECT SYS_CONTEXT('USERENV', 'INSTANCE_NAME') FROM DUAL;  -- インスタンス名

-- セッション情報
SELECT SYS_CONTEXT('USERENV', 'SID')            FROM DUAL;  -- セッション ID
SELECT SYS_CONTEXT('USERENV', 'SESSIONID')      FROM DUAL;  -- セッションの監査 ID
SELECT SYS_CONTEXT('USERENV', 'LANGUAGE')       FROM DUAL;  -- 現在の言語設定（例: JAPANESE）
SELECT SYS_CONTEXT('USERENV', 'NLS_TERRITORY')  FROM DUAL;  -- テリトリ（例: JAPAN）
SELECT SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT') FROM DUAL; -- 日付フォーマット
SELECT SYS_CONTEXT('USERENV', 'ISDBA')          FROM DUAL;  -- DBA 権限の有無（TRUE/FALSE）
SELECT SYS_CONTEXT('USERENV', 'AUTHENTICATION_TYPE') FROM DUAL;  -- 認証方式（DATABASE, OS, NETWORK など）

-- ステップ1: コンテキストの名前空間を作成する
-- ACCESSED GLOBALLY: セッション間で共有（接続プールに適している）
-- ACCESSED LOCALLY (デフォルト): セッション単位で保持
CREATE OR REPLACE CONTEXT app_ctx USING app_ctx_pkg;
-- app_ctx: コンテキスト名, app_ctx_pkg: 値をセットできるパッケージ名（信頼されたパッケージ）

-- ステップ2: コンテキストを設定するパッケージを作成する
CREATE OR REPLACE PACKAGE app_ctx_pkg AS
    PROCEDURE set_app_user(p_user_id NUMBER, p_username VARCHAR2, p_tenant_id NUMBER);
    PROCEDURE clear_ctx;
END app_ctx_pkg;
/

CREATE OR REPLACE PACKAGE BODY app_ctx_pkg AS
    PROCEDURE set_app_user(p_user_id NUMBER, p_username VARCHAR2, p_tenant_id NUMBER) IS
    BEGIN
        -- DBMS_SESSION.SET_CONTEXT(namespace, attribute, value)
        DBMS_SESSION.SET_CONTEXT('app_ctx', 'user_id',   TO_CHAR(p_user_id));
        DBMS_SESSION.SET_CONTEXT('app_ctx', 'username',  p_username);
        DBMS_SESSION.SET_CONTEXT('app_ctx', 'tenant_id', TO_CHAR(p_tenant_id));
    END set_app_user;

    PROCEDURE clear_ctx IS
    BEGIN
        -- コンテキストのすべての属性をクリアする
        DBMS_SESSION.CLEAR_CONTEXT('app_ctx');
    END clear_ctx;
END app_ctx_pkg;
/

-- ステップ3: アプリが DB に接続したらコンテキストをセットする
EXEC app_ctx_pkg.set_app_user(p_user_id => 42, p_username => 'yamada_taro', p_tenant_id => 100);

-- SYS_CONTEXT でカスタム値を取得する
SELECT SYS_CONTEXT('app_ctx', 'user_id')   AS app_user_id,
       SYS_CONTEXT('app_ctx', 'username')  AS app_username,
       SYS_CONTEXT('app_ctx', 'tenant_id') AS tenant_id
FROM   DUAL;
-- 出力: 42, yamada_taro, 100

-- SET_CONTEXT の注意点:
-- ・値は必ず VARCHAR2 として格納される（最大 256 バイト）
-- ・コンテキストを設定できるのは CREATE CONTEXT で指定したパッケージのみ
--   （他のパッケージや匿名ブロックからは設定できない）

-- GLOBAL コンテキストの作成（セッション間で属性を共有できる）
CREATE OR REPLACE CONTEXT app_global_ctx USING app_ctx_pkg ACCESSED GLOBALLY;

-- GLOBAL コンテキストでは client_id でユーザーごとに値を分離する
-- DBMS_SESSION.SET_IDENTIFIER でクライアント ID を設定する
EXEC DBMS_SESSION.SET_IDENTIFIER('request_user_42');  -- 各リクエストでユニークなIDを設定

-- GLOBAL コンテキストの値をクライアント ID ごとに設定する
EXEC DBMS_SESSION.SET_CONTEXT(
    namespace  => 'app_global_ctx',
    attribute  => 'tenant_id',
    value      => '100',
    username   => USER,
    client_id  => 'request_user_42'   -- この client_id を持つセッションのみ参照可能
);

-- クライアント ID に紐づいたコンテキスト値を取得する
SELECT SYS_CONTEXT('app_global_ctx', 'tenant_id') FROM DUAL;
-- SYS_CONTEXT は現在のセッションの CLIENT_IDENTIFIER に紐づいた値を返す

-- リクエスト終了時にクライアント ID をクリアする
EXEC DBMS_SESSION.CLEAR_IDENTIFIER;

-- テナント ID に基づいてアクセスできる行を制限する VPD ポリシー関数
CREATE OR REPLACE FUNCTION tenant_policy(
    p_schema  VARCHAR2,
    p_object  VARCHAR2
)
RETURN VARCHAR2
AS
    v_tenant_id VARCHAR2(100);
BEGIN
    v_tenant_id := SYS_CONTEXT('app_ctx', 'tenant_id');
    IF v_tenant_id IS NULL THEN
        RETURN '1 = 0';  -- コンテキスト未設定のアクセスをすべて拒否
    END IF;
    RETURN 'tenant_id = ' || TO_NUMBER(v_tenant_id);
    -- 例: tenant_id = 100 という述語が WHERE 句に追加される
END tenant_policy;
/

-- DBMS_RLS で orders テーブルに VPD ポリシーを適用する
BEGIN
    DBMS_RLS.ADD_POLICY(
        object_schema   => 'HR',
        object_name     => 'ORDERS',
        policy_name     => 'orders_tenant_policy',
        function_schema => 'HR',
        policy_function => 'TENANT_POLICY',
        statement_types => 'SELECT, INSERT, UPDATE, DELETE'
    );
END;
/

-- app_ctx.tenant_id = 100 がセットされているセッションからの SELECT は
-- 自動的に WHERE tenant_id = 100 が追加される
-- → アプリケーション側で WHERE を書かなくても行レベルのマルチテナント分離が実現できる