第4回：Oracle VPDについて

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

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

[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 ;					
					
権限付与が成功しました。					
					

[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行が作成されました。

[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;

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

[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  /	
	
ファンクションが作成されました。

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プロシージャが正常に完了しました。

[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