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

前回に引き続き、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"です。

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

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

上記の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"です。

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

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

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

続いて、USER02です。

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

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

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

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

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

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

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

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

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

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

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

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

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