3. クライアント識別子を用いた制御

3. クライアント識別子を用いた制御#

クライアント識別子やアプリケーション・コンテキストをアプリケーションから指定することで、同じDBユーザでも異なるwhere句を追加することができます。 ここで示すデモ手順では、クライアント識別子を設定することで、同じデータベースユーザーでも異なる行を表示させる制御を行います。

APPユーザーの作成#

ここでは新しくAPPユーザーを作成します。作成の流れはSALES_APPユーザーと同じです。

-- APPユーザーを作成
CREATE USER APP IDENTIFIED BY <password>
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

-- セッション作成権限を付与
GRANT CREATE SESSION TO APP;

-- HRスキーマ内のすべてのテーブルに対するSELECT権限を付与
GRANT SELECT ANY TABLE ON SCHEMA HR TO APP;

これにより、APP ユーザーはHRスキーマ内のデータにアクセスできます。

VPD関数の作成#

クライアント識別子に基づいて、表示する行を制御するVPD関数を作成します。 ここでは、例として以下の3つを識別子として作成および制御することにします。

  • Viewer: 一般スタッフやコントリビューター職のみ(_ACCOUNT, _REP, _CLERK)

  • Editor: Viewerが表示可能な行に加え、ミドルマネジメント職を表示(_MGR, _MAN, _PROG)

  • Admin: すべての行を表示(_PRES, _VP, _ASST)

VPD関数#
CREATE OR REPLACE FUNCTION hr.get_app_predicate(
    p_schema IN VARCHAR2,
    p_table  IN VARCHAR2
    )
    RETURN VARCHAR2
    IS
        v_predicate VARCHAR2 (400);
    BEGIN
        IF SYS_CONTEXT('USERENV', 'SESSION_USER') = 'SALES_APP' THEN
            v_predicate := 'JOB_ID LIKE ''SA_%''';
        ELSIF SYS_CONTEXT('USERENV', 'SESSION_USER') = 'APP' THEN   -- APPユーザーの場合、ユーザー識別子でwhere句を決定する
            IF SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') = 'VIEWER' THEN
                v_predicate := 'REGEXP_LIKE(JOB_ID, ''_(ACCOUNT|CLERK|REP)$'')';
            ELSIF SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') = 'EDITOR' THEN
                v_predicate := 'NOT REGEXP_LIKE(JOB_ID, ''_(PRES|VP|ASST)$'')';
            ELSIF SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') = 'ADMIN' THEN
                v_predicate := '1=1';
            ELSE
                v_predicate := '1=2'; -- どの識別子にも該当しない場合、何も表示しない
            END IF;
        ELSE
            v_predicate := '1=1';
        END IF;
    RETURN v_predicate;
END get_app_predicate;
/

VPDポリシーの作成#

作成したVPD関数を指定してVPDポリシーを作成していきます。 既存のポリシーがあるため、削除したのちに作成した関数を使用して新しいポリシーを作成します。

既存ポリシーを削除#
BEGIN
    DBMS_RLS.DROP_POLICY (
        object_schema   => 'HR',
        object_name     => 'EMPLOYEES',
        policy_name     => 'employees_vpd_policy'
    );
END;
/
新規ポリシーを作成#
BEGIN
    DBMS_RLS.ADD_POLICY (
        object_schema   => 'HR',
        object_name     => 'EMPLOYEES',
        policy_name     => 'employees_vpd_policy',
        function_schema => 'HR',
        policy_function => 'get_app_predicate'
    );
END;
/

各クライアント識別子での確認#

以下のコマンドはすべてAPPユーザーで実行します。

-- ユーザーがAPPであることを確認
SQL> set pages 200
SQL> show user
USER is "APP"

-- クライアント識別子を設定していない場合は何も結果が返されない
SQL> SELECT employee_id, first_name, job_id FROM hr.employees;

no rows selected

(1) Viewerの場合#

クライアント識別子「VIEWER」を設定し、APP ユーザーでクエリを実行します。

-- クライアント識別子の設定
SQL> EXEC DBMS_SESSION.SET_IDENTIFIER('VIEWER');

-- データの確認
SQL> SELECT employee_id, first_name, job_id FROM hr.employees;

EMPLOYEE_ID FIRST_NAME           JOB_ID
----------- -------------------- ----------
        109 Daniel               FI_ACCOUNT
        110 John                 FI_ACCOUNT
        111 Ismael               FI_ACCOUNT
        112 Jose Manuel          FI_ACCOUNT
        ...
        206 William              AC_ACCOUNT

84 rows selected.

(2) Editorの場合#

クライアント識別子「EDITOR」を設定し、APP ユーザーでクエリを実行します。

-- クライアント識別子の設定
SQL> EXEC DBMS_SESSION.SET_IDENTIFIER('EDITOR');

-- データの確認
SQL> SELECT employee_id, first_name, job_id FROM hr.employees;

EMPLOYEE_ID FIRST_NAME           JOB_ID
----------- -------------------- ----------
        103 Alexander            IT_PROG
        104 Bruce                IT_PROG
        105 David                IT_PROG
        106 Valli                IT_PROG
        107 Diana                IT_PROG
        ...
    206 William              AC_ACCOUNT

103 rows selected.

(3) Adminの場合#

クライアント識別子「Admin」を設定し、APP ユーザーでクエリを実行します。

-- クライアント識別子の設定
SQL> EXEC DBMS_SESSION.SET_IDENTIFIER('ADMIN');

-- データの確認
SQL> SELECT employee_id, first_name, job_id FROM hr.employees;

EMPLOYEE_ID FIRST_NAME           JOB_ID
----------- -------------------- ----------
        100 Steven               AD_PRES
        101 Neena                AD_VP
        102 Lex                  AD_VP
        103 Alexander            IT_PROG
        104 Bruce                IT_PROG
            ...
        206 William              AC_ACCOUNT

107 rows selected.
目次