PolarDB:DBMS_RLS

セッションユーザーによる行レベルのフィルタリング

このポリシー関数は、authid 列が現在のセッションユーザーと一致する行へのアクセスを制限します。

CREATE OR REPLACE FUNCTION verify_session_user (
    p_schema  VARCHAR2,
    p_object  VARCHAR2
)
RETURN VARCHAR2
IS
BEGIN
    RETURN 'authid = SYS_CONTEXT(''USERENV'', ''SESSION_USER'')';
END;

この関数は、authid = SYS_CONTEXT('USERENV', 'SESSION_USER') という述語を返します。これは、条件を満たす各 SQL ステートメントに AND 条件として動的に追加されます。

以下の匿名ブロックは、vpemp テーブルに secure_update という名前のポリシーを作成します。このポリシーは INSERT、UPDATE、および DELETE ステートメントに適用され、update_check が有効になっているため、新しく挿入または変更された行も述語を満たす必要があります。

DECLARE
    v_object_schema    VARCHAR2(30) := 'public';
    v_object_name      VARCHAR2(30) := 'vpemp';
    v_policy_name      VARCHAR2(30) := 'secure_update';
    v_function_schema  VARCHAR2(30) := 'polardb';
    v_policy_function  VARCHAR2(30) := 'verify_session_user';
    v_statement_types  VARCHAR2(30) := 'INSERT,UPDATE,DELETE';
    v_update_check     BOOLEAN      := TRUE;
    v_enable           BOOLEAN      := TRUE;
BEGIN
    DBMS_RLS.ADD_POLICY(
        v_object_schema,
        v_object_name,
        v_policy_name,
        v_function_schema,
        v_policy_function,
        v_statement_types,
        v_update_check,
        v_enable
    );
END;

salesmgr ユーザーとして接続し、SELECT を実行して現在のテーブルを確認します。

\c polardb salesmgr
Password for user salesmgr:
You are now connected to database "polardb" as user "salesmgr".
SELECT * FROM vpemp;

出力：

 empno | ename  |    job    |   sal   |  comm   | deptno |   authid
-------+--------+-----------+---------+---------+--------+-------------
  7782 | CLARK  | MANAGER   | 2450.00 |         |     10 |
  7839 | KING   | PRESIDENT | 5000.00 |         |     10 |
  7934 | MILLER | CLERK     | 1300.00 |         |     10 |
  7369 | SMITH  | CLERK     |  800.00 |         |     20 | researchmgr
  7566 | JONES  | MANAGER   | 2975.00 |         |     20 | researchmgr
  7788 | SCOTT  | ANALYST   | 3000.00 |         |     20 | researchmgr
  7876 | ADAMS  | CLERK     | 1100.00 |         |     20 | researchmgr
  7902 | FORD   | ANALYST   | 3000.00 |         |     20 | researchmgr
  7499 | ALLEN  | SALESMAN  | 1600.00 |  300.00 |     30 | salesmgr
  7521 | WARD   | SALESMAN  | 1250.00 |  500.00 |     30 | salesmgr
  7654 | MARTIN | SALESMAN  | 1250.00 | 1400.00 |     30 | salesmgr
  7698 | BLAKE  | MANAGER   | 2850.00 |         |     30 | salesmgr
  7844 | TURNER | SALESMAN  | 1500.00 |    0.00 |     30 | salesmgr
  7900 | JAMES  | CLERK     |  950.00 |         |     30 | salesmgr
(14 rows)

salesmgr として非修飾 UPDATE を発行します。

UPDATE 6

出力：

UPDATE 6

ポリシーは、authid = 'salesmgr' の行に UPDATE を制限し、セッションユーザーと一致させます。これらの 6 行のみが変更されます。

結果を確認します。

SELECT * FROM vpemp;

出力：

 empno | ename  |    job    |   sal   |  comm   | deptno |   authid
-------+--------+-----------+---------+---------+--------+-------------
  7782 | CLARK  | MANAGER   | 2450.00 |         |     10 |
  7839 | KING   | PRESIDENT | 5000.00 |         |     10 |
  7934 | MILLER | CLERK     | 1300.00 |         |     10 |
  7369 | SMITH  | CLERK     |  800.00 |         |     20 | researchmgr
  7566 | JONES  | MANAGER   | 2975.00 |         |     20 | researchmgr
  7788 | SCOTT  | ANALYST   | 3000.00 |         |     20 | researchmgr
  7876 | ADAMS  | CLERK     | 1100.00 |         |     20 | researchmgr
  7902 | FORD   | ANALYST   | 3000.00 |         |     20 | researchmgr
  7499 | ALLEN  | SALESMAN | 1600.00 | 1200.00 |     30 | salesmgr
  7521 | WARD   | SALESMAN | 1250.00 |  937.50 |     30 | salesmgr
  7654 | MARTIN | SALESMAN | 1250.00 |  937.50 |     30 | salesmgr
  7698 | BLAKE  | MANAGER   | 2850.00 | 2137.50 |     30 | salesmgr
  7844 | TURNER | SALESMAN | 1500.00 | 1125.00 |     30 | salesmgr
  7900 | JAMES  | CLERK    |  950.00 |  712.50 |     30 | salesmgr
(14 rows)

update_check が TRUE のため、authid = 'researchmgr' の行を挿入すると失敗します。これは、新しい行が salesmgr セッションユーザーのポリシー述語を満たさないためです。

INSERT INTO vpemp VALUES (9001,'SMITH','ANALYST',3200.00,NULL,20, 'researchmgr');

出力：

ERROR:  policy with check option violation
DETAIL:  Policy predicate was evaluated to FALSE with the updated values

update_check が FALSE に設定されている場合、同じ INSERT は成功します。

列レベルの VPD

sec_relevant_cols を使用して、特定の列が SQL ステートメントに表示される場合にのみポリシーを適用します。この例では、sal または comm が選択されたときに返される行を制限し、給与が 2000 未満の従業員のみを保持します。

CREATE OR REPLACE FUNCTION sal_lt_2000 (
    p_schema  VARCHAR2,
    p_object  VARCHAR2
)
RETURN VARCHAR2
IS
BEGIN
    RETURN 'sal < 2000';
END;

sec_relevant_cols を 'sal,comm' に設定してポリシーを作成します。

DECLARE
    v_object_schema     VARCHAR2(30) := 'public';
    v_object_name       VARCHAR2(30) := 'vpemp';
    v_policy_name       VARCHAR2(30) := 'secure_salary';
    v_function_schema   VARCHAR2(30) := 'polardb';
    v_policy_function   VARCHAR2(30) := 'sal_lt_2000';
    v_statement_types   VARCHAR2(30) := 'SELECT';
    v_sec_relevant_cols VARCHAR2(30) := 'sal,comm';
BEGIN
    DBMS_RLS.ADD_POLICY(
        v_object_schema,
        v_object_name,
        v_policy_name,
        v_function_schema,
        v_policy_function,
        v_statement_types,
        sec_relevant_cols => v_sec_relevant_cols
    );
END;

sal または comm を参照しないクエリは、14 行すべてを返します。ポリシーはトリガーされません。

SELECT empno, ename, job, deptno, authid FROM vpemp;

出力：

 empno | ename  |    job    | deptno |   authid
-------+--------+-----------+--------+-------------
  7782 | CLARK  | MANAGER   |     10 |
  7839 | KING   | PRESIDENT |     10 |
  7934 | MILLER | CLERK     |     10 |
  7369 | SMITH  | CLERK     |     20 | researchmgr
  7566 | JONES  | MANAGER   |     20 | researchmgr
  7788 | SCOTT  | ANALYST   |     20 | researchmgr
  7876 | ADAMS  | CLERK     |     20 | researchmgr
  7902 | FORD  | ANALYST   |     20 | researchmgr
  7499 | ALLEN  | SALESMAN |     30 | salesmgr
  7521 | WARD   | SALESMAN |     30 | salesmgr
  7654 | MARTIN | SALESMAN |     30 | salesmgr
  7698 | BLAKE  | MANAGER   |     30 | salesmgr
  7844 | TURNER | SALESMAN |     30 | salesmgr
  7900 | JAMES  | CLERK    |     30 | salesmgr
(14 rows)

sal または comm を含むクエリはポリシーをトリガーし、sal >= 2000 の行をフィルタリングします。

SELECT empno, ename, job, sal, comm, deptno, authid FROM vpemp;

出力：

 empno | ename  |   job    |   sal   |  comm   | deptno |   authid
-------+--------+----------+---------+---------+--------+-------------
  7934 | MILLER | CLERK    | 1300.00 |         |     10 |
  7369 | SMITH  | CLERK    |  800.00 |         |     20 | researchmgr
  7876 | ADAMS  | CLERK    | 1100.00 |         |     20 | researchmgr
  7499 | ALLEN  | SALESMAN | 1600.00 | 1200.00 |     30 | salesmgr
  7521 | WARD   | SALESMAN | 1250.00 |  937.50 |     30 | salesmgr
  7654 | MARTIN | SALESMAN | 1250.00 |  937.50 |     30 | salesmgr
  7844 | TURNER | SALESMAN | 1500.00 | 1125.00 |     30 | salesmgr
  7900 | JAMES  | CLERK    |  950.00 |  712.50 |     30 | salesmgr
(8 rows)