第4回：Oracle VPDについて

はじめに

こんにちは、マルチDBソリューション部の川本です。Oracle Databaseのセキュリティ対策として、暗号化、監査、アクセス制御など様々なものがありますが、今回はその中でもデータベースユーザレベルでデータへのアクセスを制限することができる、Oracle Virtual Private Database（Oracle VPD）についてお話させていただきます。

Oracle VPDを使用すると、SQLが発行された際に、OracleがSQLに対して自動的にwhere句を付与し、行および列レベルでデータベース・アクセスを制御することができます。下記のようなイメージになります。

今回は3つのユーザを用意し、それぞれのユーザで特定のテーブル（SCOTT.EMP）をselectした際にセキュリティ・ポリシーで参照を許可された行のみを返す設定を検証しました。

検証環境としては、Oracle Database 12.2でマルチテナント機能を用いており、検証で使用したテーブル情報とユーザ情報は下記の通りとなります。

検証内容は以下の通りとなります。

1.ユーザ作成
sysユーザでPDBに接続し、SCOTT,TEST1,TEST2ユーザを作成後、必要最低限の権限を付与します。

[oracle@12c ~]$ sqlplus sys/sys@localhost:1521/pdb1 as sysdba					
--省略--					
SQL> show con_name					
					
CON_NAME					
------------------------------					
PDB1					
					
SQL> create user TEST1 identified by TEST1 ;					
					
ユーザーが作成されました。					
					
SQL> create user TEST2 identified by TEST2 ;					
					
ユーザーが作成されました。					
					
SQL> create user SCOTT identified by SCOTT ;					
					
ユーザーが作成されました。					
					
SQL> grant connect to TEST1 ;					
					
権限付与が成功しました。					
					
SQL> grant connect to TEST2 ;					
					
権限付与が成功しました。					
					
SQL> grant dba to SCOTT ;					
					
権限付与が成功しました。		

2.テーブル作成
SCOTTユーザでPDBに接続し、EMP表を作成します。また、5件のデータをインサートします。

[oracle@12c ~]$ sqlplus SCOTT/SCOTT@localhost:1521/pdb1	
--省略--	
SQL> CREATE TABLE EMP	
  2  (EMP_NO NUMBER(4) NOT NULL,	
  3   EMP_NAME VARCHAR2(10));	
	
表が作成されました。	
	
SQL> INSERT INTO EMP VALUES (1,'TARO');	
	
1行が作成されました。	
	
SQL> INSERT INTO EMP VALUES (2,'HANAKO');	
	
1行が作成されました。	
	
SQL> INSERT INTO EMP VALUES (3,'TAKASHI');	
	
1行が作成されました。	
	
SQL> INSERT INTO EMP VALUES (4,'AKIRA');	
	
1行が作成されました。	
	
SQL> INSERT INTO EMP VALUES (5,'YOSHIKO');	
	
1行が作成されました。

3.権限付与
TEST1,TEST2ユーザからSCOTT.EMPにアクセスするための権限を付与します。

[oracle@12c ~]$ sqlplus sys/sys@localhost:1521/pdb1 as sysdba
--省略--
SQL> GRANT ALL ON SCOTT.EMP TO TEST1;

権限付与が成功しました。

SQL> GRANT ALL ON SCOTT.EMP TO TEST2;

権限付与が成功しました。

4.ポリシー関数の作成
各ユーザに応じてwhere句が設定されるようなファンクションを作成します。

[oracle@12c ~]$ sqlplus sys/sys@localhost:1521/pdb1 as sysdba	
--省略--	
SQL> CREATE OR REPLACE FUNCTION auth_emp(	
  2    schema_var IN VARCHAR2,	
  3    table_var  IN VARCHAR2	
  4   )	
  5   RETURN VARCHAR2	
  6   IS	
  7    return_val VARCHAR2 (400);	
  8    username VARCHAR2 (20);	
  9   BEGIN	
 10    select SYS_CONTEXT('USERENV', 'SESSION_USER') into username from dual;	
 11     IF(username='TEST1') THEN	
 12      return_val := 'EMP_NO = 1';	
 13     ELSIF(username='TEST2') THEN	
 14      return_val := 'EMP_NO = 2';	
 15     ELSE	
 16      return_val := null;	
 17     END IF;	
 18     RETURN return_val;	
 19   END auth_emp;	
 20  /	
	
ファンクションが作成されました。

5.ポリシーの作成
SCOTT.EMPに対してポリシーを作成します。7行目のpolicy_function => ‘auth_emp’という記述により、[4.ポリシー関数の作成]で作成したファンクションが適用されます。

SQL> BEGIN
  2    DBMS_RLS.ADD_POLICY (
  3      object_schema    => 'SCOTT',
  4      object_name      => 'EMP',
  5      policy_name      => 'emp_policy',
  6      function_schema  => 'sys',
  7      policy_function  => 'auth_emp',
  8      statement_types  => 'select'
  9     );
 10   END;
 11  /

PL/SQLプロシージャが正常に完了しました。

6.各ユーザでSCOTT.EMPに対してselect文を発行
各ユーザでSCOTT.EMPに対して条件なしの同じselect文を発行し、返される結果を確認します。

[oracle@12c ~]$ sqlplus SCOTT/SCOTT@localhost:1521/pdb1	
--省略--	
SQL> select * from SCOTT.EMP ;	
	
    EMP_NO EMP_NAME	
---------- ------------------------------	
         1 TARO	
         2 HANAKO	
         3 TAKASHI	
         4 AKIRA	
         5 YOSHIKO	
	
[oracle@12c ~]$ sqlplus TEST1/TEST1@localhost:1521/pdb1	
--省略--	
SQL> select * from SCOTT.EMP ;	
	
    EMP_NO EMP_NAME	
---------- ------------------------------	
         1 TARO	
	
[oracle@12c ~]$ sqlplus TEST2/TEST2@localhost:1521/pdb1	
--省略--	
SQL> select * from SCOTT.EMP ;	
	
    EMP_NO EMP_NAME	
---------- ------------------------------	
         2 HANAKO

[6.各ユーザでSCOTT.EMPに対してselect文を発行]の結果から、SCOTTユーザは全ての行の値が返され、TEST1ユーザはEMP_NO=1、TEST2ユーザはEMP_NO=2の行のみが検索できていることが分かります。つまり、[4.ポリシー関数の作成]で作成したポリシー関数の返り値（return_val）が、各ユーザで実行されたselect文のwhere句に自動的に付与され、ユーザ毎に参照できるデータを制御できていることが分かります。