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

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

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

2014.07.17

こんにちは。インストラクターの蓑島です。
だんだんと夏らしくなってきましたね。暑い夏が待ち遠しいです。

さて、今回から2回にわたり、「VPD(バーチャル・プライベート・データベース)」というセキュリティ機能を解説します。
VPDはOracle 8iで登場し、Enterprise Editionで可能な機能です。
なお、12cではVPDに類似した「RAS(Real Application Security)」という発展機能もありますが、それは別の機会に触れます。

VPDは、簡単に言えば、特定の表に対する問い合わせを行うときに、ログインしているユーザに応じたWHERE句の条件が自動的に付加されるという機能です。それにより、そのユーザは自分の気づかぬうちにそのWHERE句で制限された行だけを参照することになります。
たとえ、そのWHERE句の条件に制限されない他の行がたくさんあったとしても、そのユーザはそれらの行の存在に気づくことがありません。
わかりやすい機能ですね。
行単位のアクセス制限という意味では「ビュー」に似ています。
しかし、VPDは、そのセッションで使用される様々なもの(IPアドレス、プログラム名、ユーザー名などなど)を組合せたWHERE句の条件を生成できます。そしてその条件は、どんな方法で表にアクセスしても付加されるため、「SQL*Plusから表にアクセスしてコッソリ全行見よう・・・」としても、参照する範囲は制限されます。
つまり、ビュー以上に高度なセキュリティ要件を満たすことができる機能です。

しかし、この機能の実装は様々なコンポーネントの組み合わせになりますので、少し面倒です。
今回と次回でSCOTTスキーマのEMP表を例にとって、なるべく簡単にこの機能を実装したいと思います。

ではその様々なコンポーネントとは、具体的にどのようなものか? 以下にそれらを列挙します。

  • アプリケーションコンテキスト
  • パッケージ
  • ログオントリガー
  • WHERE句に追加する条件を返すファンクション
  • ファイングレイン・アクセス・コントロール・ポリシー

このような5つのコンポーネントを用意してそれらを関連づける必要があります。

ではそれぞれ簡単に解説します。

1. アプリケーションコンテキスト
アプリケーションコンテキストとは、各ユーザーセッションに応じた情報を格納するための大きな入れ物のようなものです。
具体的にはセッションごとの様々な属性の集まりです。属性の名前は任意に決めます。
そしてそれぞれの属性の値をセットしてセッションごとに保持します。
これらの情報は WHERE句に追加される条件を生成するために使われます。

2. パッケージ
上記1.のアプリケーションコンテキストは、PL/SQLパッケージと関連づける必要があります。
そのPL/SQLパッケージ内のプロシージャやファンクションを使うことによってのみ、アプリケーションコンテキストに属性とその値をセットできます。
よってパッケージを作り、その中にアプリケーションコンテキストに属性をセットするプロシージャやファンクションを定義する必要があります。

3. ログオントリガー
アプリケーションコンテキストにそのセッションの属性を自動的にセットする手段として、ログオントリガーが可能です。
そのログオントリガー内で上記2のパッケージのプロシージャを実行します。そうすることで、ログオン時にそのセッションの属性がアプリケーションコンテキストに自動的にセットされます。

4. WWHERE句に追加する条件を返すファンクション
WHERE句に追加する条件をアプリケーションコンテキストの属性を参照しながら生成します。
具体的には、WHERE句に追加する条件そのものを文字列で生成し、リターンします。

5. ファイングレイン・アクセス・コントロール・ポリシー
特定の表と、上記のWHERE句の条件を返すファンクションと、操作(例 SELECT, UPDATEなど)を関連づけます。
これにより、特定の表にSELECTなどが行われるときに、自動的にそのファンクションが起動し、アプリケーションコンテキストの属性を参照しながら条件を生成し、WHERE句に追加されます。
ユーザ本人が意識していなくても、その条件で制限された行だけを参照することになります。

それでは実際に上記のコンポーネントを用意して実装してみましょう。以下に具体的なストーリーを述べます。
まず、データベースユーザ USER01とUSER02を作成します。
そして結論をいうと、USER01ユーザはSCOTT.EMP表(社員表)の、部門番号(DEPTNO列)の値が10の行だけを参照できるようにし、USER02ユーザは部門番号(DEPTNO列)が20の行だけを参照できるようにしたいと考えてます。
つまり、USER01ユーザがSOCTT.EMP表をSELECTすると、自動的に「DEPTNO = 10」という条件がWHERE句に付加され、USER02ユーザの場合は、「DEPTNO = 20」という条件が付加されるということです。

では早速、USER01ユーザとUSER02ユーザを作成し、データベースへの接続権限と、SCOTT.EMP表のSELECT権限を付与します。

1
2
3
4
5
6
7
8
9
10
SHOW USER
ユーザーは "SYSTEM" です。   -- SYSTEMユーザで
 
CREATE USER USER01 IDENTIFIED BY USER01;    -- USER01ユーザ作成
 
ユーザーが作成されました。
 
CREATE USER USER02 IDENTIFIED BY USER02;    -- USER02ユーザ作成
 
ユーザーが作成されました。

これでUSER01ユーザ、USER02ユーザが作成されました。
次に権限を付与します。

1
2
3
GRANT CREATE SESSION TO USER01, USER02;
 
権限付与が成功しました。

これでデータベースに接続可能です。

1
2
3
GRANT SELECT , UPDATE , DELETE ON SCOTT.EMP TO USER01, USER02;
 
権限付与が成功しました。

これで、 SCOTT.EMP表に対して、SELECT, UPDATE, DEPETE が可能です。

では、この状態で早速、上記の5つのコンポーネントを作成してVPD(バーチャル・プライベート・データベース)を実装してみましょう。

1. アプリケーションコンテキスト
では、まず最初はアプリケーションコンテキストの作成です。
名前は簡単に、CTX1 とし、関連付けるPL/SQLパッケージの名前をPAC1とします。PAC1は、SCOTTスキーマのパッケージとしますので、SCOTT.PAC1です。
この時点で、PL/SQLパッケージは作成されていなくても結構です。
では、以下のように作成します。

1
2
3
4
5
6
SHOW USER
ユーザーは "SYSTEM" です。     -- 管理者ユーザで
 
CREATE OR REPLACE CONTEXT CTX1  USING  SCOTT.PAC1;
 
コンテキストが作成されました。

これで、CTX1というアプリケーションコンテキストが作成されました。
このコンテキストに属性をセットできるのは、SCOTT.PAC1パッケージ内のサブブログラムのみです。
繰り返しになりますが、このパッケージはこの時点で存在していなくても大丈夫です。

2. パッケージ
では次に そのSCOTT.PAC1パッケージを作成します。
このパッケージに プロシージャ PROC1を含めます。このプロシージャはユーザがUSER01の場合はコンテキスCTX1に'DEPTNO'という属性名で値'10'をセットします。 USER02の場合は、'DEPTNO'という属性名で値'20'をセットします。

パッケージは仕様部から作成します。

1
2
3
4
5
6
7
CREATE OR REPLACE PACKAGE SCOTT.PAC1
IS
    PROCEDURE PROC1;
END ;
/
 
パッケージが作成されました。

これはパッケージ仕様部ですから、PROC1プロシージャの仕様部(ISよりも前の部分)だけなので完全な定義ではありません。

次に本体です。本体でPROC1プロシージャを完全に定義します。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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;
END PAC1;
/
 
パッケージ本体が作成されました。

これでPAC1プロシージャの完全な定義ができました。
8~14行目で、ユーザに応じて保持する値を用意します。例えば USER01ユーザならその値は 10です。
そして、注目していただきたいのは、16行目の記述です。
すなわち、DBMS_SESSIONパッケージのSET_CONTEXTプロシージャを使って、アプリケーションコンテキストに'DEPTNO'という名前で属性値(V_DEPTNO変数)をセットしています。

これにより、SCOTT.PAC1パッケージのPAC1プロシージャを実行すると、ログインユーザがUSER01の場合は、CTX1コンテキストにDEPTNO属性 = '10'がセットされ、USER02ユーザの場合は'20'がセットされ、それ以外のユーザの場合は、NULLがセットされます。

3. ログオントリガー
次にログオントリガーを作成します。
このログオントリガーの中で、上記のSCOTT.PAC1パッケージのPROC1プロシージャを実行します。
(ログオントリガーはバックナンバー 第71回「ログオントリガー」で解説しています)

このトリガーをSYSTEMユーザで作成しようと考えています。
トリガーは定義者権限なので、誰がログオンしても、定義者であるSYSTEMユーザの権限で実行されます。
SYSTEMユーザは管理者なので、DBAロールを付与されており、そのロールを通して「EXECUTE ANY PROCEDURE」システム権限を付与されています。
したがってSYSTEMユーザはSCOTT.PAC1パッケージをもちろん実行できます。
しかし、トリガーなど定義者権限のPL/SQLプログラム内では、実はすべてのロールが無効化されています。
したがって、SYSTEMユーザはSCOTT.PAC1の実行権限を直接付与されている必要があります。ではその権限を実際に直接付与しましよう。

1
2
3
4
5
6
7
CONNECT SCOTT/ password
show user
ユーザーは "SCOTT" です。      -- SCOTTユーザになって
 
GRANT EXECUTE ON SCOTT.PAC1 TO SYSTEM;   -- SYSTEMユーザにSCOTT.PAC1の実行権限(EXECUTE)を権限を与える
 
権限付与が成功しました。

ではこれで準備ができました。SYSTEMユーザになってログオントリガーを作成します。トリガー名をLOG_ON_TRIGとします。
SQL> CONNECT SYSTEM/password
SHOW USER
ユーザーは"SYSTEM"です。

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE TRIGGER LOG_ON_TRIG
AFTER LOGON
ON  DATABASE     -- 全ユーザが対象となる指定 (トリガー定義者のみを対象としたければ、 ON SCHEMA)
BEGIN
    SCOTT.PAC1.PROC1;
END ;
/
 
トリガーが作成されました。

これでトリガーが完成です。

なお、ログオントリガーがコンパイルエラーの場合は、SYSユーザ以外のユーザはログオンが失敗するようになりますので、放置せず必ずコンパイルを成功させてください。あるいは次のコマンドでトリガーを無効にしてください。
ALTER TRRIGGER トリガー名 DISABLE;

ではこれでログオントリガーもできました。
これにより、USER01ユーザでログオンすれば、アプリケーションコンテキストにDEPTNO属性が'10'という値でセットされます。
それを確認して、今回はここまでとしましよう。

では早速、USER01ユーザでログオンします。

1
2
3
4
5
CONNECT USER01/USER01
接続されました。
 
SHOW USER
ユーザーは "USER01" です。

現在、USER01ユーザです。
ではこのUSER01のセッションでさらに以下の問い合わせをして、アプリケーションコンテキストの属性を確認します。

1
2
3
4
5
SELECT SYS_CONTEXT( 'CTX1' , 'DEPTNO' ) FROM DUAL;
 
SYS_CONTEXT( 'CTX1' , 'DEPTNO' )
----------------------------------------------------------------
10

このSYS_CONTEXTというファンクションで、アプリケーションコンテキストの名前('CTX1')と、属性の名前('DEPTNO')を指定することにより、その値を確認しています。
確かに 10 という値ですね。もしもUSER02ユーザで接続していたのなら 20 という値になったはずです。

なお、このSYS_CONTEXTファンクションは、STANDARDパッケージに格納されているので、どのデータベースユーザでも使用可能です。どのユーザセッションでも常に自分に関係するアプリケーションコンテキストは参照できるのですが、変更することは原則できません。つまり、そのアプリケーションコンテキストに関連づけれらたパッケージに対する実行権限がない限りは、変更できません。
また、仮に実行権限があったとしても、そのパッケージのプロシージャやファンクションを通してしかアプリケーションコンテキストにアクセスできないので、自由に好きなように変更することはできません。

では今回は、ログインによりアプリケーションコンテキストに属性がセットされた、ということを確認できましたので、続きは次回にします。

次回は、このアプリケーションコンテキストの値を使ってWHERE句に条件を追加するファンクションを作成し、さらにファイングレイン・アクセス・コントロール・ポリシーで、SCOTTスキーマのEMP表に対して、SELECTやUPDATE, DELETEなどを行うとき、そのファンクションが起動するように関連づけします。
それにより、ユーザに応じたWHERE句の条件( DEPTNO = 10 や、 DEPTNO = 20 )が自動的に追加されて、VPD(バーチャル・プライベート・データベース)の機能が実現できます。

それではまた次回、ご期待ください。

先頭へ戻る