前回は、Oracle Virtual Private Database（VPD）が、表、ビュー、シノニムにポリシーを関連付け、実行されたSQLに述語を適用することで、行や列へのアクセスを制御する仕組みを紹介しました。

本記事では、Oracle Virtual Private Database（VPD）を実際に構成し、接続ユーザーによって参照できる行が変わることを確認してみます。

ここで行う構成は、SALES_APP ユーザーには営業職の行だけ（値が SA_ から始まる行）を返し、HR ユーザーには全行を返すようにします。

また、次回の記事では、同じ表と行制御ポリシーを前提に、SALARY 列の制御と統合監査に記録されるVPD述語を確認してみます。

0. はじめに

本記事で実施する内容は以下のとおりです。

実施内容VPD による行制御の設定と動作確認
・HR.GET_SALES_PREDICATE ポリシー関数の作成
・HR.EMPLOYEES 表へのVPDポリシーの追加
・HR と SALES_APP で同じ SELECT 文を実行し、表示行を比較
・作成したポリシーと関数の削除
作業時間の目安環境構築済みの状態で 約 30 分
実行環境・Oracle AI Database 26ai FREE
・PDB 名: FREEPDB1
・HR サンプルスキーマ
・クライアントツール: SQLcl または SQL*Plus

本記事では、見やすさのために実行結果の一部を省略または整形しています。
そのため、実際の出力と異なる場合があります。

1. 環境の準備

1-1. Oracle Database の準備

この手順では、次の環境を前提にします。

  • Oracle AI Database 26ai FREE の環境があること
  • PDB 名は FREEPDB1
  • HR サンプルスキーマを準備していること
  • SQLcl または SQL*Plus で接続できる
  • 管理者ユーザー（SYS）で接続できる

管理者ユーザーで PDB に直接接続する場合は、以下のように実行します。

$ sql sys/<password>@localhost:1521/freepdb1 AS SYSDBA

CDB に接続してから PDB に切り替える場合は、以下を実行します。

ALTER SESSION SET CONTAINER = freepdb1;

1-2. クライアントツールの用意

本手順では、Oracle Database に接続できるクライアントツールが必要です。
SQLcl、SQL*Plus、SQL Developer、VS Code の SQL Developer 拡張機能など、任意のツールで freepdb1 に接続できる環境を準備してください。本記事では SQLcl の形式でコマンドを記載します。

接続に必要なサービス名やリスナーの状態は、DB サーバーで以下のコマンドを実行することで確認できます。

$ lsnrctl status

1-3. SALES_APP ユーザーの準備

動作確認には、営業アプリケーションを想定した SALES_APP ユーザーを使用します。
未作成の場合は、SYS ユーザーで FREEPDB1 へ接続して作成し、HR.EMPLOYEES 表の参照権限を付与します。

create user sales_app identified by "<password>";

grant create session to sales_app;
grant select on hr.employees to sales_app;

すでに SALES_APP ユーザーが存在する場合は、作成処理を省略してください。

権限だけを確認する場合、SYS ユーザーで次を実行します。

SELECT grantee, owner, table_name, privilege
FROM DBA_TAB_PRIVS
WHERE grantee = 'SALES_APP'
  AND owner = 'HR'
  AND table_name = 'EMPLOYEES';

2. デモ構成の概要

このハンズオンでは、HR.EMPLOYEES 表に EMPLOYEES_VPD_POLICY を追加します。

ポリシーは HR.GET_SALES_PREDICATE 関数を呼び出し、接続ユーザーに応じて次の述語を返すことを想定します。

接続ユーザーポリシー関数が返す述語期待する結果
HR1=1全行すべてを参照できる
SALES_APPJOB_ID LIKE 'SA_%'SA_MAN と SA_REP の行だけを参照できる
それ以外1=1VPD は追加の制限をせず、通常のオブジェクト権限に従う

そのため、共有データベース・アカウントと接続プールを使うアプリケーションでは、SESSION_USER だけでは実利用者を区別できません。
認証済みの利用者情報を信頼されたパッケージからアプリケーション・コンテキストへ設定し、その値をポリシー関数で参照します。
これについては第7節で解説します。

3. 事前確認

3-1. 接続先と元データを確認する

SYS ユーザーで FREEPDB1 に接続し、現在のユーザーとコンテナを確認します。

SHOW USER
SHOW CON_NAME

実行例です。

USER is "SYS"

CON_NAME
------------------------------
FREEPDB1

続いて、HR.EMPLOYEES 表の総行数と営業職の行数を確認します。

SELECT count(*) FROM hr.employees;

SELECT job_id, count(*) AS employee_cnt
  FROM hr.employees
  WHERE job_id like 'SA_%' 
  GROUP BY job_id;
   COUNT(*)
___________
        107

JOB_ID       EMPLOYEE_CNT
_________ _______________
SA_MAN                  5
SA_REP                 30

3-2. 既存の VPD ポリシーと関数を確認する

このあと作成するポリシーと関数が存在しないことを確認します。

HR.EMPLOYEES に別用途の VPD ポリシーがすでにある場合は、影響を評価できるまで本手順を実行しないでください。

SELECT object_owner, object_name, policy_name, function, enable
  FROM DBA_POLICIES
  WHERE object_owner = 'HR'
    AND object_name = 'EMPLOYEES'
  ORDER BY policy_name;

SELECT object_name, object_type, status
  FROM DBA_OBJECTS
  WHERE owner = 'HR'
    AND object_name = 'GET_SALES_PREDICATE';

初めて実行する環境では、どちらの問合せでも何も返却されないことを確認します。

no rows selected

4. VPD による行制御を設定する

4-1. ポリシー関数を作成する

引き続き SYS で実行します。

以下を実行し、ポリシー関数 GET_SALES_PREDICATE を作成します。
この関数では、SALES_APP ユーザーで接続した場合にだけ営業職を表す JOB_ID の条件を返し、それ以外のユーザーには 1=1 を返します。

CREATE OR REPLACE FUNCTION HR.GET_SALES_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_%''';
  ELSE
    V_PREDICATE := '1=1';
  END IF;
  
  RETURN V_PREDICATE;
END GET_SALES_PREDICATE;
/

Function HR.GET_SALES_PREDICATE compiled と表示され、正しくコンパイルされたことを確認します。

4-2. HR.EMPLOYEES 表へポリシーを追加する

SYS ユーザーで FREEPDB1 へ接続し、HR.GET_SALES_PREDICATE を HR.EMPLOYEES 表に関連付けます。

本記事では、意図しない更新操作へ影響を与えないよう、チュートリアルの関数と構成を使いながら、対象SQLを SELECT に限定します。

$ sql sys@localhost:1521/freepdb1 AS SYSDBA

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => 'HR',
    object_name     => 'EMPLOYEES',
    policy_name     => 'EMPLOYEES_VPD_POLICY',
    function_schema => 'HR',
    policy_function => 'GET_SALES_PREDICATE',
    statement_types => 'SELECT'
  );
END;
/

DBMS_RLS.ADD_POLICY は、操作の前後でコミットを実行します。

未コミットの変更があるセッションでは実行しないよう注意してください。

4-3. 設定内容を確認する

作成したポリシーを ALL_POLICIES で確認します。

SELECT object_owner,
       object_name,
       policy_name,
       function,
       sel,
       ins,
       upd,
       del,
       idx,
       policy_type,
       common
FROM ALL_POLICIES
WHERE object_owner = 'HR'
  AND object_name = 'EMPLOYEES'
  AND policy_name = 'EMPLOYEES_VPD_POLICY';

実行例です。

OBJECT_OWNER  OBJECT_NAME  POLICY_NAME           FUNCTION               SEL  INS  UPD  DEL  IDX  POLICY_TYPE  COMMON
------------- ------------ --------------------- ---------------------- ---- ---- ---- ---- ---- ------------ ------
HR            EMPLOYEES    EMPLOYEES_VPD_POLICY  GET_SALES_PREDICATE    YES  NO   NO   NO   NO   DYNAMIC       NO

SEL が YES で、INS、UPD、DEL、IDX が NO なら、SELECT にだけポリシーが適用されます。

5. 動作確認

5-1. HR ユーザーからの実行結果

新しいセッションで HR ユーザーとして接続します。

$ sql hr@localhost:1521/freepdb1
SHOW USER
SHOW CON_NAME

SELECT employee_id, first_name, salary, job_id
  FROM EMPLOYEES
  ORDER BY employee_id;

実行例です。

USER is "HR"

CON_NAME
------------------------------
FREEPDB1

   EMPLOYEE_ID FIRST_NAME        SALARY JOB_ID
______________ ______________ _________ _____________
           100 Steven             24000 AD_PRES
           101 Neena              17000 AD_VP
           102 Lex                17000 AD_VP
           103 Alexander           9000 IT_PROG
           104 Bruce               6000 IT_PROG
           105 David               4800 IT_PROG
           106 Valli               4800 IT_PROG
           107 Diana               4200 IT_PROG
           108 Nancy              12008 FI_MGR
           109 Daniel              9000 FI_ACCOUNT
...省略...
           198 Donald             2600 SH_CLERK
           199 Douglas            2600 SH_CLERK
           200 Jennifer           4400 AD_ASST
           201 Michael           13000 MK_MAN
           202 Pat                6000 MK_REP
           203 Susan              6500 HR_REP
           204 Hermann           10000 PR_REP

107 rows selected.

HR では関数が 1=1 を返すため、VPD は追加の行フィルターを適用しません。

5-2. SALES_APP ユーザーからの実行結果

続いて、新しいセッションで SALES_APP ユーザーとして接続し、HR と同じSQLを実行します。

$ sql sales_app@localhost:1521/freepdb1
SHOW USER
SHOW CON_NAME

SELECT employee_id, first_name, salary, job_id
  FROM HR.EMPLOYEES
  ORDER BY employee_id;

実行例です。

USER is "SALES_APP"

CON_NAME
------------------------------
FREEPDB1

   EMPLOYEE_ID FIRST_NAME        SALARY JOB_ID
______________ ______________ _________ _________
           145 John               14000 SA_MAN
           146 Karen              13500 SA_MAN
           147 Alberto            12000 SA_MAN
           148 Gerald             11000 SA_MAN
           149 Eleni              10500 SA_MAN
           150 Sean               10000 SA_REP
           151 David               9500 SA_REP
           152 Peter               9000 SA_REP
           153 Christopher         8000 SA_REP
           154 Nanette             7500 SA_REP
           155 Oliver              7000 SA_REP
           156 Janette            10000 SA_REP
...省略...
           172 Elizabeth          7300 SA_REP
           173 Sundita            6100 SA_REP
           174 Ellen             11000 SA_REP
           175 Alyssa             8800 SA_REP
           176 Jonathon           8600 SA_REP
           177 Jack               8400 SA_REP
           178 Kimberely          7000 SA_REP
           179 Charles            6200 SA_REP

35 rows selected.

SALES_APP では、ポリシー関数が JOB_ID LIKE 'SA_%' を返します。

そのため、実行したSQLは先程と同じものにもかかわらず、この条件が加わったものとして処理され、営業職の 35 行だけが返っていることがわかります。

このように、アプリケーション側のSQLに同じ WHERE 句を追加していなくても、保護対象表へのアクセス時にデータベースが条件を適用することを確認できます。

6. 別条件で確認する

同じ SALES_APP セッションで、SQLに業務条件を加えて確かめてみます。

SELECT employee_id, first_name, salary, job_id
  FROM HR.EMPLOYEES
  WHERE salary >= 10000
  ORDER BY employee_id;

このSQLでは、アプリケーションが指定した SALARY >= 10000 と、VPDが適用した JOB_ID LIKE 'SA_%' の両方を満たす行だけが返ります。

   EMPLOYEE_ID FIRST_NAME       SALARY JOB_ID
______________ _____________ _________ _________
           145 John              14000 SA_MAN
           146 Karen             13500 SA_MAN
           147 Alberto           12000 SA_MAN
           148 Gerald            11000 SA_MAN
           149 Eleni             10500 SA_MAN
           150 Sean              10000 SA_REP
           156 Janette           10000 SA_REP
           162 Clara             10500 SA_REP
           168 Lisa              11500 SA_REP
           169 Harrison          10000 SA_REP
           174 Ellen             11000 SA_REP

11 rows selected.

このように、VPD では保護対象へのアクセスにポリシー関数の述語を追加していることがわかります。

7. （任意）同一のデータベース・ユーザーを利用する場合

ここまでの手順では、HR と SALES_APP という別々のデータベース・ユーザーを使い、SESSION_USER の値によって表示する行を分けました。

一方、接続プールを使用するアプリケーションでは、複数の利用者が同じデータベース・ユーザーで接続する構成があります。
この構成では、SESSION_USER が同じ値になるため、SESSION_USER だけではアプリケーションの利用者を区別できません。

同じデータベース・ユーザーを使いながら利用者ごとに異なる条件を適用する場合は、クライアント識別子またはアプリケーション・コンテキストの値をVPDポリシーの判定に利用できます。
本記事ではこれらを使った設定手順は実施しませんが、仕組みだけを整理します。

クライアント識別子とアプリケーション・コンテキストは、どちらもセッションに設定した値を SYS_CONTEXT で参照し、VPDのポリシー関数が返す述語の判定に使用する点は同じです。簡単な違いとしては以下のようになります。

項目クライアント識別子アプリケーション・コンテキスト
保持する情報組み込みの USERENV 名前空間に保持する一つの識別値独自の名前空間に保持する複数の属性と値
設定方法DBMS_SESSION.SET_IDENTIFIER、OCIまたはクライアント・ドライバから設定CREATE CONTEXT で名前空間とPL/SQLパッケージを関連付け、そのパッケージから DBMS_SESSION.SET_CONTEXT を実行
参照方法SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER')SYS_CONTEXT('<名前空間>', '<属性名>')

詳しくは以下のドキュメントを参照ください。

3.11.2 データベースに認識されないアプリケーション・ユーザーの識別でのクライアント識別子の使用
https://docs.oracle.com/cd/G47991_01/dbseg/configuring-authentication.html#GUID-18333ED1-0FE1-4110-A3A5-EA9B1B989DA4

VPDのポリシー関数はこれらの値を SYS_CONTEXT で取得し、利用者へ適用する述語を返します。

アプリケーションは、認証した利用者の情報をこれらの値へ設定します。
そのため接続プールのセッションを別の利用者へ再利用するときは、前の利用者の値が残らないように消去または再設定する必要があります。

以下のサイトでは、アプリケーション・コンテキストは作成せず、共通の APP ユーザーへ VIEWER、EDITOR、ADMIN のクライアント識別子を設定する手順を案内していますので、ご興味のある方は参考にしてみてください。

https://koi141.github.io/dbsec-tutorials/access-control/virtual-private-database/client-identifier/

8. 後片付け

次の記事の手順（列制御と統合監査）を続けて実施する場合は、この後片付けを行わず、EMPLOYEES_VPD_POLICY と GET_SALES_PREDICATE を残してください。

このハンズオンをここで終了する場合は、先にVPDポリシーを削除し、そのあとでポリシー関数を削除します。

8-1. VPD ポリシーを削除する

SYS ユーザーで FREEPDB1 に接続し、ポリシーを削除します。

$ sql sys@localhost:1521/freepdb1 AS SYSDBA
BEGIN
  DBMS_RLS.DROP_POLICY(
    object_schema => 'HR',
    object_name   => 'EMPLOYEES',
    policy_name   => 'EMPLOYEES_VPD_POLICY'
  );
END;
/

8-2. ポリシー関数を削除する

その後、関数を削除します。

DROP FUNCTION HR.GET_SALES_PREDICATE;

削除後は、ポリシーと関数が残っていないことを確認します。

SELECT policy_name
FROM DBA_POLICIES
WHERE object_owner = 'HR'
  AND object_name = 'EMPLOYEES'
  AND policy_name = 'EMPLOYEES_VPD_POLICY';

SELECT object_name
FROM DBA_OBJECTS
WHERE owner = 'HR'
  AND object_name = 'GET_SALES_PREDICATE';

どちらの問合せも次のように表示され、今回作成したVPD設定は削除されていることを確認します。

no rows selected

SALES_APP をこのハンズオンのためだけに作成した場合は、ほかの検証で使用していないことを確認してから削除してください。

DROP USER SALES_APP CASCADE;

9. まとめ

本記事では、HR.EMPLOYEES 表へVPDポリシーを追加し、接続ユーザーに応じて返る行が変わることを確認しました。

  • VPDは、ポリシー関数が返す述語を保護対象へのSQLへ適用します。
  • HR ユーザーからの問い合わせには 107 行すべてが表示されました。
  • 一方、SALES_APP ユーザーからの問い合わせには、同じ SELECT 文でも営業職の 35 行だけが表示されました。

次の記事では、この行制御ポリシーを残した状態で SALARY 列を制御し、統合監査の RLS_INFO から適用されたVPD述語を確認します。