前回に引き続き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句が追加されているからです。
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句が追加されているからです。