e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第102回「VPD(バーチャル・プライベート・データベース) (2)」

2014.07.24

こんにちは。インストラクターの蓑島です。

前回に引き続き、VPD(バーチャル・プライベート・データベース)の続きです。
SCOTTスキーマのEMP表に対して、VPDをなるべく簡単に実装します。

前回はVPDの概要と、実装手順(5つのコンポーネント)を説明し、手順3までを作りました。【1.アプリケーションコンテキスト】【2.パッケージ】【3.ログオントリガー】です。
今回は残り2つのコンポーネント【4.WHERE句に追加する条件を返すファンクション】【5.ファイングレイン・アクセス・コントロール・ポリシー】を作成します。

さっそくはじめましょう。

4. WHERE句に追加する条件を返すファンクション
アプリケーションコンテキストの属性をもとに、WHERE句の条件を返すファンクションを作成します。
そのファンクションには、対象となる表を指定する必要があります。今回の表はSCOTTスキーマのEMP表です。その指定のために二つの引数をもつ必要があります。
一つはスキーマ名を示す引数(P_SCHEMA)であり、もう一つはオブジェクト名(表名)を示す引数(P_NAME)です。
そのファンクションはスタンドアロン(パッケージに追加しない)で作成してもいいのですが、VPDでは必ずパッケージを使うので、そのパッケージに追加することが自然です。
したがって、前回作ったSCOTT.PAC1パッケージに追加します。ファンクション名をFUNC1にします。

まず、仕様部です。
SQL> SHOW USER
ユーザーは"SYSTEM"です。

1
2
3
4
5
6
7
8
9
10
11
CREATE OR REPLACE PACKAGE SCOTT.PAC1
IS
    PROCEDURE PROC1;
    -- ★★追加したファンクション
    FUNCTION  FUNC1 (P_SCHEMA IN VARCHAR2,   -- スキーマ名
                     P_NAME   IN VARCHAR2)   -- 表名
    RETURN  VARCHAR2;
END ;
/
 
パッケージが作成されました。

ここで5~7行目がパッケージに追加したファンクションです。
WHERE句に追加する条件を文字列で返しますので RETURN VARCHAR2 としてます。

次に本体です。本体ではファンクションFUNC1を完全に定義します。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
CREATE OR REPLACE PACKAGE BODY SCOTT.PAC1
IS
    PROCEDURE PROC1
    IS
       V_DEPTNO  VARCHAR2(10);
    BEGIN
       -- ユーザに応じて保持する値を用意して
       IF USER = 'USER01' THEN
          V_DEPTNO := '10' ;
       ELSIF USER = 'USER02' THEN
          V_DEPTNO := '20' ;
       ELSE
          V_DEPTNO := NULL ;
       END IF;
       -- その値をアプリケーションコンテキストCTX1に'DEPTNO'という属性名で値(V_DEPTNO)をセット
       DBMS_SESSION.SET_CONTEXT( 'CTX1' , 'DEPTNO' ,V_DEPTNO);
    END PROC1;
       -- ★★追加したファンクション
    FUNCTION  FUNC1 (P_SCHEMA IN VARCHAR2,
                     P_NAME   IN VARCHAR2)
    RETURN  VARCHAR2
    IS
       V_DEPTNO  VARCHAR2(10);
    BEGIN
       IF P_SCHEMA = 'SCOTT' AND P_NAME = 'EMP' THEN
          V_DEPTNO := SYS_CONTEXT( 'CTX1' , 'DEPTNO' );
          IF V_DEPTNO IS NULL THEN
             RETURN NULL ;
          ELSE
             RETURN 'DEPTNO = ' || V_DEPTNO;
          END IF;
       ELSE
          RETURN NULL ;
       END IF;
    END FUNC1;
END PAC1;
/
 
パッケージ本体が作成されました。

上記の19-35行目が追加したファンクションFUNC1の完全な定義です。
このファンクションは次に説明するファイングレイン・アクセス・コントロール・ポリシーによって、SCOTT.EMP表に関連付られます。
また文の種類(SELECT/INSERT/UPDATE/DELETE)も関連づけられます。
その結果、どのデータベースユーザでも、SCOTT.EMP表を操作(SELECT/UPDATE/DELETE)するときにこのファンクションが自動的に起動します。
そして、このファンクションが返すユーザセッション毎の条件式がWHERE句に自動的に追加され、VPDの機能を果たすわけです。

まずこのファンクションは、引数としてスキーマ名(P_SCHEMA)と、表名(P_NAME)を持ちます(19-20行目)。そして、スキーマ名がSCOTTで、表名がEMPでないときは、NULLを返します(33行目)。 NULLを返すということは、具体的な処理をしないということです。
スキーマ名がSCOTTで、表名がEMPのときは、該当ユーザセッションのアプリケーションコンテキスト(CTX1)から、属性(DEPTNO)の値を取得します(25-26行目)。 そして、その属性値がNULLでない場合は、'DEPTNO = ~' という文字列を返します(30行目)。
例えば、該当ユーザセッションがUSER01である場合は、前回までの実装によって、DEPTNO属性の値は10であるので、'DEPTNO = 10'という文字列がリターンする。 USER02ユーザであれば、'DEPTNO = 20'という文字列がリターンする。
・・・・というような条件式がSCOTT.EMPに対するSELECT文などのWHERE句に自動的に追加されるわけです。
もしも該当ユーザがUSER01やUSER02でなければ、前回までの実装によって、DEPTNO属性の値はNULLなので、このファンクションはNULLをリターンします(28行目)。 
繰り返しますが、NULLを返すということは具体的な処理をしないということです。

ここで、リターンする条件式は文字列連結で生成していることに注目してください。
文字列連結で条件を生成するということは、プログラミング可能でさえあれば、いくらでも詳細な条件式を生成できるということです。使用する列名も条件式も固定せずに、プログラムでいかようにでも組み立てることができます。そのために必要なアプリケーションコンテキストの属性も、SCOTT.PAC1パッケージのPROC1プロシージャを修正すれば、いくらでも詳細に持たせることができます。

この点が単なるビューによるデータの制限とは大きく違う点です。
ビューによるデータの制限は、ビュー定義のSELECT文に依存する固定的なものですが、このファンクションによるデータの制限はビューのような固定的なものではありません。

また、このファンクションは引数でオブジェクト名、表名を持っていることにも注目してください。つまり、このファンクションは、条件を追加すれば、SCOTT.EMP表以外であっても利用することができるわけです。ロジックの中で、25行目のIF文で行っているように、スキマー名、表名を判定し、それ毎のロジックを記述することで、他の表であっても利用できます。
ではこのファンクションがコールされるときに、引数のスキマー名、表名は誰がこのファンクションに渡してくるのか? 
それは次に説明するファイングレイン・アクセス・コントロール・ポリシーが行ってくれます。

5. ファイングレイン・アクセス・コントロール・ポリシー
これは、特定の表と、文の種類と、ファンクションを関連付けるもので設定は容易です。
以下のポリシーの作成例をご覧ください。

SQL> SHOW USER
ユーザーは"SYSTEM"です。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
BEGIN
   DBMS_RLS.ADD_POLICY(
      'SCOTT' ,                -- スキーマ名
      'EMP' ,                  -- 表名
      'SCOTT_EMP_POLICY' ,     -- ポリシー名
      'SCOTT' ,                -- ファンクションスキーマ名
      'PAC1.FUNC1' ,           -- ファンクション名
      'SELECT, UPDATE, DELETE' ,     -- 文の種類
      FALSE ,                  --更新のチェック
      TRUE );                  --有効化
END ;
/
 
PL/SQLプロシージャが正常に完了しました。

このポリシーは、SCOTT.EMP表に対して、SELECT, UPDATE, DELETE が行われる時に、SCOTTスキーマのPAC1パッケージのFUNC1ファンクションが起動するという関連づけです。このファンクションの起動により、WHERE句に自動的に条件が追加されるわけです。
なお、ご覧のようにポリシーには名前がついています。上記の例では、「SCOTT_EMP_POLICY」という名前です。

上記のポリシーによって、USER01ユーザの場合は、SCOTT.EMP表の「DEPTNO = 10」の行だけがアクセス可能であり、USER02ユーザの場合は、「DEPTNO = 20」の行だけがアクセス可能となります。
早速確認してみましょう。

1
2
3
4
5
6
7
8
9
CONNECT USER01/USER01
 
SELECT * FROM  SCOTT.EMP;
 
   EMPNO ENAME   JOB            MGR HIREDATE      SAL    COMM   DEPTNO
------- ------- ----------- ------ -------- -------- ------- --------
    7782 CLARK   MANAGER       7839 81-06-09     2450               10
    7839 KING    PRESIDENT          81-11-17     5000               10
    7934 MILLER  CLERK         7782 82-01-23     1300               10

いかがですか? 右端のDEPTNO列の値に注目してください。確かに、「DEPTNO = 10」 の行だけが問い合わせされました。SELECT文に条件を指定していないのに、自動的に「DEPTNO = 10」の条件で制限されていることがわかります。

続いて、USER02です。

1
2
3
4
5
6
7
8
9
10
11
CONNECT USER02/USER02
 
SELECT * FROM  SCOTT.EMP;
 
   EMPNO ENAME   JOB            MGR HIREDATE      SAL    COMM   DEPTNO
------- ------- ----------- ------ -------- -------- ------- --------
    7369 SMITH   CLERK         7902 80-12-17      800               20
    7566 JONES   MANAGER       7839 81-04-02     2975               20
    7788 SCOTT   ANALYST       7566 87-04-19     3000               20
    7876 ADAMS   CLERK         7788 87-05-23     1100               20
    7902 FORD    ANALYST       7566 81-12-03     3000               20

USER02の場合は、自動的に「DEPTNO = 20」の条件で制限されていますね。

確かにユーザセッションに応じた自動的な制限が行われていることがわかります。これでSCOTT.EMP表に対して、VPDの機能が実装されました。

なお、場合によっては制限を一時的に無効にしたいこともあるかもしれません。そのような場合は、ポリシーを無効(FALSE)にすることで可能です。

SQL> SHOW USER
ユーザーは"SYSTEM"です。

1
2
3
4
5
6
BEGIN
   DBMS_RLS.ENABLE_POLICY( 'SCOTT' , 'EMP' , 'SCOTT_EMP_POLICY' , FALSE );  --  FALSE:無効  TRUE:有効
END ;
/
 
PL/SQLプロシージャが正常に完了しました。

最後の引数がFALSEであればそのポリシーを無効に、TRUEであれば有効にします。
また、ポリシー名(SCOTT_EMP_POLICY)でけでなく、スキーマ名(SCOTT)、オブジェクト名(EMP)も指定する必要があります。
上記の例はポリシーを無効にしていますので、これにより、例えばUSER01ユーザが DEPTNO = 10 の条件で制限されることがなくなります。

参考までにポリシーそのものを削除する場合は以下のようにします。

1
2
3
4
5
6
BEGIN
    DBMS_RLS.DROP_POLICY( 'SCOTT' , 'EMP' , 'SCOTT_EMP_POLICY' );  -- ポリシーの削除
END ;
/
 
PL/SQLプロシージャが正常に完了しました。

それでは、最後に、事前定義のアプリケーション・コンテキストが実はあることについて簡単に解説します。

今回は、明示的にアプリケーションコンテキストを作成し、属性をセットしました。しかし、事前に定義されたアプリケーション・コンテキスト「USERENV」があり、ここに多くのユーザセッションの属性が格納されています。

以下の例をご覧ください。

1
2
3
4
5
SELECT SYS_CONTEXT( 'USERENV' , 'SESSION_USER' ) FROM DUAL;
 
SYS_CONTEXT( 'USERENV' , 'SESSION_USER' )
---------------------------------------------------------------------
USER01

ここでは、USERENVアプリケーションコンテキストの、SESSION_USER属性の値を問い合わせて、その値がUSER01となっています。
これはこのユーザセッションのユーザ名を表すわけです。

USERENVコンテキストの属性については、Oracleマニュアル「SQL言語リファレンス」のSYS_CONTEXTの頁を参考にしてください。

今回、VPDのためセッション毎に値を保持するアプリケーション・コンテキストを利用しましたが、セッションの枠を超えて、グローバルに値を保持するアプリケーションコンテキストもあります。
よってアプリケーション・コンテキストは、さまざまな局面でアプリケーションで利用可能な幅の広い機能です。その応用例の一つとしてVPDがあるにすぎないわけです。

以上でVPDについて解説を終わります。
また次回、ご期待ください。

先頭へ戻る