オラクる。

oracle専門ブログにしてみようかな~っと

2016年08月

前回に引き続きVPDの作成です。
PL/SQLも深く関わってくるので、ちょっと面倒ですね。

まずはコンテキストの値を元に、WHEREの条件句を作成するファンクションを作成します。
前回作成したパッケージにファンクションを追加します。

CREATE OR REPLACE PACKAGE SCOTT.PAC1
IS
   PROCEDURE PROC1;
   FUNCTION FUNC1 (P_SCHEMA IN VARCHAR2,
                   P_NAME   IN VARCHAR2)
   RETURN VARCHAR2;
END;
/


まずは仕様部分
前回のプロシージャ、そして今回のファンクションが存在します。

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;
      DBMS_SESSION.SET_CONTEXT('CTX1','DEPTNO',V_DEPTNO);
   END PROC1;
   FUNCTION FUNC1 (P_SCHEMA IN VARCHAR2,
                   P_NAME   IN VARCHAR2)
   RETURN VARCHAR2
   IS
      V_DEPTNO VARCHAR2(10);
   BEGIN
      IF P_SCHEMA = 'SCOTT' AND P_NAME = 'EMP' THEN
         V_DEPTNO := SYS_CONTEXT('CTX1','DEPTNO');
         IF V_DEPTNO IS NULL THEN
            RETURN NULL;
         ELSE
            RETURN 'DEPTNO = ' || V_DEPTNO;
         END IF;
      ELSE
         RETURN NULL;
      END IF;
   END FUNC1;
END PAC1;
/


そして、本体
ファンクションはスキーマがscott、テーブル名がempの時に、アプリケーションコンテキストの内容に基づいて、「DEPTNO = xx」を作成します。
さらに、この後のファイングレイ・アクセス・コントロール・ポリシーで対象の表とファンクションを結びつけます。

BEGIN
  DBMS_RLS.ADD_POLICY(
     'SCOTT',
     'EMP',
     'SCOTT_EMP_POLICY',
     'SCOTT',
     'PAC1.FUNC1',
     'SELECT,UPDATE,DELETE',
     FALSE,
     TRUE);
END;
/

これでSCOTT.EMP表とファンクションが結び付けられました。
SCOTT.EMP表の条件式に「DEPTNO = xx」を追加します。
実際に確認してみましょう。

SQL> CONNECT USER01/USER01
接続されました。

SQL> SELECT * FROM SCOTT.EMP;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 81-06-01       7770                    10
      7839 KING       PRESIDENT            81-11-01       7770                    10
      7934 MILLER     CLERK           7782 82-01-01       7770                    10


user01で接続した時、EMP表で表示されるのはDEPTNO=10のみです。

SQL> CONNECT USER02/USER02
接続されました。
SQL> SELECT * FROM SCOTT.EMP;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80-12-01        777                    20
      7566 JONES      MANAGER         7839 81-04-01       7775                    20
      7902 FORD       ANALYST         7566 81-12-01       7770                    20


user02の時はDEPTNO=20しか表示されません。
それぞれ見えないところでWHERE句が追加されているからです。

VPDは表を検索したときに、検索したユーザーごとに表示する行を変える機能です。
WHERE句の検索条件を自動的に付与することによって実現します。

[oracle@DB01 ~]$ sqlplus / as sysdba

SQL> CREATE USER USER01 IDENTIFIED BY USER01;

ユーザーが作成されました。

SQL> CREATE USER USER02 IDENTIFIED BY USER02;

ユーザーが作成されました。

SQL> GRANT CREATE SESSION TO USER01,USER02;

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

SQL> GRANT SELECT,UPDATE,DELETE ON SCOTT.EMP TO USER01,USER02;

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


今回、USER01、USER02というユーザーを作り、それぞれに表示を変えるVPDを作成します。
対象はscottのemp表で、USER01はDEPTNO=10のみを表示し、USER02はDEPTNO=20のみを表示します。

SQL> CREATE OR REPLACE CONTEXT CTX1 USING SCOTT.PAC1;

コンテキストが作成されました。


コンテキストを作成します。
このコンテキストに値をセットできるのはscottユーザーのパッケージPAC1のみです。

SQL> CREATE OR REPLACE PACKAGE SCOTT.PAC1
  2  IS
  3     PROCEDURE PROC1;
  4  END;
  5  /

パッケージが作成されました。


SCOTT.PAC1を作成します。
まずは仕様部分

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;
      DBMS_SESSION.SET_CONTEXT('CTX1','DEPTNO',V_DEPTNO);
   END PROC1;
END PAC1;
/

パッケージ本体が作成されました。


次に本体部分
パッケージに含まれるプロシージャがPROC1になります。
このPROC1が実行されるとコンテキスト内のDEPTNOにユーザーに応じた値がセットされます。

SQL> CONNECT SCOTT/password
接続されました。
SQL> show user
ユーザーは"SCOTT"です。
SQL> GRANT EXECUTE ON SCOTT.PAC1 TO SYSTEM;

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


次にログオントリガーを作成します。
ユーザーがログオンした時に先のPROC1が実行されるようにします。

SQL> CONNECT system/password
接続されました。
SQL> show user
ユーザーは"SYSTEM"です。

CREATE OR REPLACE TRIGGER LOG_ON_TRIG
AFTER LOGON
ON DATABASE
BEGIN
  SCOTT.PAC1.PROC1;
END;
/

トリガーが作成されました。


systemユーザーにログインし、ログオントリガーを作成します。

SQL> CONNECT USER01/USER01
接続されました。
SQL> show USER
ユーザーは"USER01"です。
SQL> SELECT SYS_CONTEXT('CTX1','DEPTNO') FROM DUAL;

SYS_CONTEXT('CTX1','DEPTNO')
--------------------------------------------------------------------------------
10

SQL> CONNECT USER02/USER02
接続されました。
SQL> show USER
ユーザーは"USER02"です。
SQL> SELECT SYS_CONTEXT('CTX1','DEPTNO') FROM DUAL;

SYS_CONTEXT('CTX1','DEPTNO')
--------------------------------------------------------------------------------
20

それぞれのユーザーでログインし、コンテキストに格納される値を確認します。

今回はここまで。
次回はこの値を実際にwhere句に格納する所から始めます。

今回は前回設定した時刻有効性(Temporal Validity)の表を参照してみます。
以下の様な有効期限が表のデータに設定してあります。

     EMPNO ENAME
---------- ----------
       100 SCOTT       2000/10/1~
       200 KING        2002/4/1~2010/03/30
       300 ADAMS       2001/4/1~2012/12/31


時点を指定してSELECTを実行してみます。

SQL> SELECT * FROM emp3
  2  AS OF PERIOD FOR user_time
  3  TO_DATE('2002-01-01','YYYY-MM-DD');

     EMPNO ENAME
---------- ----------
       100 SCOTT
       300 ADAMS


まず、2002/01/01時点で有効なデータを参照しています。
AS OF PERIOD FOR句で時点を指定しています。

SQL> SELECT * FROM emp3
  2  VERSIONS PERIOD FOR user_time
  3  BETWEEN TO_DATE('2010-04-01','YYYY-MM-DD')
  4  AND SYSDATE;

     EMPNO ENAME
---------- ----------
       100 SCOTT
       300 ADAMS


次に2010/04/01~現時点で有効なデータを参照しています。
VERSIONS PERIOD FOR 句で範囲を指定しています。

SQL> exec DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('ALL');

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

SQL> SELECT * FROM scott.emp3;

     EMPNO ENAME
---------- ----------
       100 SCOTT
       200 KING
       300 ADAMS


さらにDBMS_FLASHBACK_ARCHIVEのENABLE_AT_VALID_TIMEプロシージャでデフォルトの動作を指定できます。
デフォルトは全データALLを表示します。

exec DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('CURRENT');

SQL> SELECT * FROM scott.emp3;

     EMPNO ENAME
---------- ----------
       100 SCOTT

CURRENTを指定すると現時点で有効なデータが表示されます。

「時刻有効性(Tmporal Validity)」という機能を利用することによってデータの有効期限をテーブルに付与することが出来ます。
有効期限内のデータを参照すると言ったことが出来ます。

CREATE TABLE emp3
(empno NUMBER(4),
 ename VARCHAR2(10),
PERIOD FOR user_time);

テーブル作成時にPERIOD_FOR句で有効期限列を指定します。

SQL> desc emp3
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)


descには有効期限列は表示されません。

SQL> column DATA_TYPE format a30
SQL> column COLUMN_NAME format a20
SQL> SELECT column_name, hidden_column, virtual_column, data_type
  2  FROM user_tab_cols WHERE table_name='EMP3';

COLUMN_NAME          HID VIR DATA_TYPE
-------------------- --- --- ------------------------------
USER_TIME_END        YES NO  TIMESTAMP(6) WITH TIME ZONE
USER_TIME_START      YES NO  TIMESTAMP(6) WITH TIME ZONE
USER_TIME            YES YES NUMBER
ENAME                NO  NO  VARCHAR2
EMPNO                NO  NO  NUMBER


xxx_tab_colsビューを参照すると有効期限列のstartとend列が追加されていることがわかります。

SQL> INSERT INTO emp3(empno,ename,user_time_start,user_time_end)
  2  VALUES(100,'SCOTT',TO_TIMESTAMP('2000-10-01','YYYY-MM-DD'),NULL);

1行が作成されました。

INSERT INTO emp3(empno,ename,user_time_start,user_time_end)
VALUES(200,'KING',TO_TIMESTAMP('2002-04-01','YYYY-MM-DD'),
TO_TIMESTAMP('2010-03-31','YYYY-MM-DD'));

1行が作成されました。


上記、有効期限列に日付データを挿入することが出来ます。

SQL> SELECT * FROM emp3;

     EMPNO ENAME
---------- ----------
       100 SCOTT
       200 KING

SELECT句では有効期限列は非表示です。

ALTER TABLE emp3 ADD (PERIOD FOR promo_time);

SQL> SELECT column_name, hidden_column, virtual_column, data_type
  2  FROM user_tab_cols WHERE table_name='EMP3';

COLUMN_NAME          HID VIR DATA_TYPE
-------------------- --- --- ------------------------------
USER_TIME_END        YES NO  TIMESTAMP(6) WITH TIME ZONE
USER_TIME_START      YES NO  TIMESTAMP(6) WITH TIME ZONE
USER_TIME            YES YES NUMBER
PROMO_TIME           YES YES NUMBER
PROMO_TIME_END       YES NO  TIMESTAMP(6) WITH TIME ZONE
PROMO_TIME_START     YES NO  TIMESTAMP(6) WITH TIME ZONE
ENAME                NO  NO  VARCHAR2
EMPNO                NO  NO  NUMBER

8行が選択されました。


有効期限列はALTER句で追加することが出来ます。

12cの新機能Data Redactioのうち、列内容を部分的に変換する部分リダクションは、複数の列にリダクションを設定することが出来ます。
また、それぞれの列に異なるリダクション方式を指定できます。

BEGIN
 DBMS_REDACT.ADD_POLICY(
  policy_name    =>    'EMP_P',
  object_schema    =>    'SCOTT',
  object_name    =>    'EMP',
  column_name    =>    'HIREDATE',
  expression    =>    '1=1',
  function_type    =>    DBMS_REDACT.PARTIAL,
  function_parameters    =>    'Md1YHMS');
END;
/


まずは、scottスキマーのemp表hiredate列に対して、日付を「1」にリダクションするポリシーを作成します。

SQL> SELECT EMPNO,ENAME,HIREDATE,SAL FROM EMP;

     EMPNO ENAME      HIREDATE        SAL
---------- ---------- -------- ----------
      7369 SMITH      80-12-01        800
      7499 ALLEN      81-02-01       1600
      7521 WARD       81-02-01       1250
      7566 JONES      81-04-01       2975
      7654 MARTIN     81-09-01       1250
      7698 BLAKE      81-05-01       2850
      7782 CLARK      81-06-01       2450
      7839 KING       81-11-01       5000
      7844 TURNER     81-09-01       1500
      7900 JAMES      81-12-01        950
      7902 FORD       81-12-01       3000

     EMPNO ENAME      HIREDATE        SAL
---------- ---------- -------- ----------
      7934 MILLER     82-01-01       1300

12行が選択されました。


その時点での表のSELECT結果です。
HIREDATE列がリダクションされてます。

BEGIN
 DBMS_REDACT.ALTER_POLICY(
  policy_name    =>    'EMP_P',
  object_schema    =>    'SCOTT',
  object_name    =>    'EMP',
  action    =>    DBMS_REDACT.ADD_COLUMN,
  column_name    =>    'SAL',
  function_type    =>    DBMS_REDACT.PARTIAL,
  function_parameters    =>    '7,1,3');
END;
/


ALTER_POLICYでポリシーを追加します。
同じEMP表SAL列に対して、先頭の1桁目から3桁目を7にリダクションするポリシーです。

SQL> SELECT EMPNO,ENAME,HIREDATE,SAL FROM EMP;

     EMPNO ENAME      HIREDATE        SAL
---------- ---------- -------- ----------
      7369 SMITH      80-12-01        777
      7499 ALLEN      81-02-01       7770
      7521 WARD       81-02-01       7770
      7566 JONES      81-04-01       7775
      7654 MARTIN     81-09-01       7770
      7698 BLAKE      81-05-01       7770
      7782 CLARK      81-06-01       7770
      7839 KING       81-11-01       7770
      7844 TURNER     81-09-01       7770
      7900 JAMES      81-12-01        777
      7902 FORD       81-12-01       7770

     EMPNO ENAME      HIREDATE        SAL
---------- ---------- -------- ----------
      7934 MILLER     82-01-01       7770

12行が選択されました。


HIREDATEとSALの両方がリダクションされました。

このページのトップヘ