【PL/SQL】DBMS_SESSION.SET_CONTEXTの使い方｜SYS_CONTEXT・VPD・接続プール対応まで

-- DBAまたはCREATE ANY CONTEXT権限を持つユーザーで実行
CREATE OR REPLACE CONTEXT app_ctx USING app_ctx_pkg;

-- 作成後、APP_CTXの値は app_ctx_pkg 経由で設定する

CREATE OR REPLACE PACKAGE app_ctx_pkg AUTHID DEFINER AS
  PROCEDURE set_login_context(
    p_app_user_id IN NUMBER,
    p_tenant_id   IN NUMBER,
    p_role_name   IN VARCHAR2
  );

  PROCEDURE clear_login_context;
END app_ctx_pkg;
/

CREATE OR REPLACE PACKAGE BODY app_ctx_pkg AS
  PROCEDURE set_login_context(
    p_app_user_id IN NUMBER,
    p_tenant_id   IN NUMBER,
    p_role_name   IN VARCHAR2
  ) IS
  BEGIN
    IF p_app_user_id IS NULL OR p_tenant_id IS NULL THEN
      RAISE_APPLICATION_ERROR(-20001, 'login context is required');
    END IF;

    DBMS_SESSION.SET_CONTEXT('APP_CTX', 'APP_USER_ID', TO_CHAR(p_app_user_id));
    DBMS_SESSION.SET_CONTEXT('APP_CTX', 'TENANT_ID',   TO_CHAR(p_tenant_id));
    DBMS_SESSION.SET_CONTEXT('APP_CTX', 'ROLE_NAME',   UPPER(p_role_name));
  END;

  PROCEDURE clear_login_context IS
  BEGIN
    DBMS_SESSION.CLEAR_ALL_CONTEXT('APP_CTX');
  END;
END app_ctx_pkg;
/

SELECT SYS_CONTEXT('APP_CTX', 'APP_USER_ID') AS app_user_id,
       SYS_CONTEXT('APP_CTX', 'TENANT_ID')   AS tenant_id,
       SYS_CONTEXT('APP_CTX', 'ROLE_NAME')   AS role_name
FROM dual;

CREATE OR REPLACE FUNCTION current_tenant_id
RETURN NUMBER
IS
BEGIN
  RETURN TO_NUMBER(SYS_CONTEXT('APP_CTX', 'TENANT_ID'));
EXCEPTION
  WHEN VALUE_ERROR THEN
    RETURN NULL;
END;
/

-- リクエスト開始時
BEGIN
  app_ctx_pkg.clear_login_context;

  app_ctx_pkg.set_login_context(
    p_app_user_id => :app_user_id,
    p_tenant_id   => :tenant_id,
    p_role_name   => :role_name
  );

  DBMS_SESSION.SET_IDENTIFIER(:request_id);
END;
/

-- リクエスト終了時、または接続をプールへ返す直前
BEGIN
  app_ctx_pkg.clear_login_context;
  DBMS_SESSION.CLEAR_IDENTIFIER;
END;
/

-- app_ctx_pkg パッケージボディ内の実装例
PROCEDURE clear_tenant IS
BEGIN
  DBMS_SESSION.CLEAR_CONTEXT('APP_CTX', NULL, 'TENANT_ID');
END;

PROCEDURE clear_login_context IS
BEGIN
  DBMS_SESSION.CLEAR_ALL_CONTEXT('APP_CTX');
END;

SET SERVEROUTPUT ON
DECLARE
  l_contexts DBMS_SESSION.APPCTXTABTYP;
  l_count    NUMBER;
BEGIN
  DBMS_SESSION.LIST_CONTEXT(l_contexts, l_count);

  FOR i IN 1 .. l_count LOOP
    DBMS_OUTPUT.PUT_LINE(
      l_contexts(i).namespace || '.' ||
      l_contexts(i).attribute || '=' ||
      l_contexts(i).value
    );
  END LOOP;
END;
/

CREATE OR REPLACE FUNCTION f_order_rls(
  p_schema IN VARCHAR2,
  p_object IN VARCHAR2
) RETURN VARCHAR2
AS
  l_tenant_id NUMBER;
BEGIN
  l_tenant_id := TO_NUMBER(SYS_CONTEXT('APP_CTX', 'TENANT_ID'));

  RETURN 'tenant_id = ' || TO_CHAR(l_tenant_id);
EXCEPTION
  WHEN VALUE_ERROR THEN
    RETURN '1 = 2';
END;
/

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => USER,
    object_name     => 'ORDERS',
    policy_name     => 'ORDERS_BY_TENANT',
    function_schema => USER,
    policy_function => 'F_ORDER_RLS',
    statement_types => 'SELECT,UPDATE,DELETE',
    update_check    => TRUE
  );
END;
/

CREATE OR REPLACE CONTEXT app_global_ctx
USING app_global_ctx_pkg
ACCESSED GLOBALLY;

CREATE OR REPLACE PACKAGE BODY app_global_ctx_pkg AS
  PROCEDURE set_global_value(
    p_client_id IN VARCHAR2,
    p_tenant_id IN NUMBER
  ) IS
  BEGIN
    DBMS_SESSION.SET_CONTEXT(
      namespace => 'APP_GLOBAL_CTX',
      attribute => 'TENANT_ID',
      value     => TO_CHAR(p_tenant_id),
      username  => SYS_CONTEXT('USERENV', 'SESSION_USER'),
      client_id => p_client_id
    );
  END;
END;
/