VPDで保護された表にOACから接続する

はじめに

OAC（Oracle Analytics Cloud)でエンタープライズデータを検索する際、行レベルでセキュリティを設定したいことはあると思います。
OACで行レベルセキュリティを実現する方法は複数ありますが、データベース側でセキュリティを実装することで、OAC管理者を含むOACの利用者に対して行レベルセキュリティを実現することができます。

本記事で実現すること：

• Autonomous Database（ADB）にある特定の表に行レベルセキュリティを設定する
• ADBにログインするユーザーごとに参照できる範囲（行）を制限する
• OACのセルフサービス用のデータセットで実現する
• OACユーザーは何も意識せずに自分に権限のあるデータだけが参照できる

仮想プライベートデータベース（VPD）

Oracle Databaseの仮想プライベートデータベース（VPD）機能を使用します。
概略を説明すると、、、
行レベルセキュリティを設定する関数を作成します。
次に、対象の表に対してポリシーを設定します。
表へのアクセス時にポリシーにより関数が呼び出されて、行レベルセキュリティを実現します。

こちらのチュートリアルがわかりやすいです。

前半の設定編では、このチュートリアルに沿った設定をします。
その後、対象の表をOACから参照して、どのように見えるかを確認していこうと思います。

設定

ADBにVPD設定用のユーザー（VPDADMIN）と、表の所有者となるユーザー（ADBUSER）を作成しておきます。
※ SQLの実行にはSQL Developerを使用しています。

テスト用の表を作成

ADBUSERでADBに接続します。
サンプルのSSBスキーマのCUSTOMER表からデータを抽出してテスト用とします。

テスト用の表作成

CREATE TABLE adbuser.vpd_customer AS SELECT * FROM ssb.customer WHERE ROWNUM<10000;

確認用のSQLを実行してみます。

SELECT c_region,COUNT(*) FROM adbuser.vpd_customer GROUP BY c_region;

テストユーザーの作成

adminユーザーで接続し、アジアのデータだけを検索するテストユーザーを作成します。
パスワードは適宜指定してください。

テストユーザーの作成

CREATE USER asia IDENTIFIED by "*************";
GRANT CONNECT TO asia;
GRANT SELECT ON adbuser.vpd_customer TO asia;

asiaユーザーで接続し、確認用のSQLを実行してみます。

SELECT c_region,COUNT(*) FROM adbuser.vpd_customer GROUP BY c_region;

まだ何も設定していないので、先ほどと同じ結果となります。

VPDの設定

VPDADMINユーザーで接続します。
ポリシー関数を作成します。

ポリシー関数の作成

CREATE OR REPLACE FUNCTION vpdfunc
  (v_schema VARCHAR2, v_objname VARCHAR2)
  RETURN VARCHAR2
AS
  v_text VARCHAR2(400);
BEGIN
-- 条件となる文字列を変数v_textに代入し、RETURNで返すように設定
  v_text := 'c_region='''||UPPER(SYS_CONTEXT('USERENV','SESSION_USER')||'''');
  RETURN v_text;
end;
/

VPDポリシーを表に適用します。

ポリシーの適用

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema     => 'ADBUSER',
    object_name       => 'VPD_CUSTOMER',
    policy_name       => 'VPDPOL',
    function_schema   => 'VPDADMIN',
    policy_function   => 'VPDFUNC',
    statement_types   => 'SELECT',
    policy_type       => DBMS_RLS.CONTEXT_SENSITIVE
    );
END;
/

適用したポリシーを削除する場合は、以下を実行します。

ポリシーの削除

BEGIN
    DBMS_RLS.DROP_POLICY(
        object_schema => 'ADBUSER',
        object_name   => 'VPD_CUSTOMER',
        policy_name   => 'VPDPOL'
    );
END;
/

VPDを試してみる

asiaユーザーで接続し、確認用のSQLを実行します。

SELECT c_region,COUNT(*) FROM adbuser.vpd_customer GROUP BY c_region;

SQLに検索条件がなくても、VPDにより自動的に行が制限される点がポイントです。

OACから接続

では、ADBに作成したユーザーでアクセスして、動作確認してみます。

接続の作成

「作成」ボタンをクリックして、「接続」を選択します。

「Oracle Autonomous Data Warehouse」をクリックします。

ASIAユーザーで接続する設定にします。

「保存」をクリックします。

データセットの作成

「作成」ボタンをクリックして、「データセット」を選択します。

今作った「接続」をクリックします。

「ADBUSER」スキーマを展開し「VPD_CUSTOMER」をドラッグ＆ドロップします。
ASIAユーザーは、この表の検索権限を持っていますが、表自体を所有しているのはADBUSERとなります。
プレビューを確認してみると、C_REGIONがASIAの列だけが表示されているのがわかります。

データセットに名前を付けて保存します。

検索してみる

データセットのプレビューを見た時点で結果は明らかですが、ワークブックも作ってみました。
ASIAのデータのみが検索されました。

別のユーザーで接続して検索してみる

SQL Developerに戻って、EUROPEユーザーを作成してみます。

新たなテストユーザーを作成

CREATE USER europe IDENTIFIED by "*************";
GRANT CONNECT TO europe;
GRANT SELECT ON adbuser.vpd_customer TO europe;

OACでこのユーザーを使用する接続を作成します。

新しく作成した接続をクリックします。

C_REGIONがEUROPEのデータだけが検索されているのがわかります。

おわりに

OAC側でフィルタを設定することなく、データベース側の設定で行レベルセキュリティを実現することができました。

参考情報

• Oracle Virtual Private Databaseを使用したデータ・アクセスの制御