【PL/SQL】DBMS_RLSの使い方｜VPDで行レベルセキュリティを実装する方法

CREATE TABLE sales (
  sales_id     NUMBER PRIMARY KEY,
  dept_id      NUMBER NOT NULL,
  customer_id  NUMBER NOT NULL,
  amount       NUMBER NOT NULL,
  note         VARCHAR2(200),
  created_at   DATE DEFAULT SYSDATE
);

INSERT INTO sales (sales_id, dept_id, customer_id, amount, note)
VALUES (1, 10, 1001, 50000, 'dept 10 order');

INSERT INTO sales (sales_id, dept_id, customer_id, amount, note)
VALUES (2, 20, 2001, 75000, 'dept 20 order');

COMMIT;

CREATE OR REPLACE PACKAGE app_ctx_pkg AS
  PROCEDURE set_dept_id(p_dept_id IN NUMBER);
  PROCEDURE clear_context;
END app_ctx_pkg;
/

CREATE OR REPLACE PACKAGE BODY app_ctx_pkg AS
  PROCEDURE set_dept_id(p_dept_id IN NUMBER) IS
  BEGIN
    IF p_dept_id IS NULL OR p_dept_id < 1 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Invalid department id');
    END IF;

    DBMS_SESSION.SET_CONTEXT(
      namespace => 'APP_CTX',
      attribute => 'DEPT_ID',
      value     => TO_CHAR(p_dept_id)
    );
  END;

  PROCEDURE clear_context IS
  BEGIN
    DBMS_SESSION.CLEAR_CONTEXT('APP_CTX');
  END;
END app_ctx_pkg;
/

CREATE CONTEXT app_ctx USING app_ctx_pkg;

CREATE OR REPLACE FUNCTION f_sales_rls(
  p_schema IN VARCHAR2,
  p_object IN VARCHAR2
) RETURN VARCHAR2
AS
  v_dept_id_text VARCHAR2(30);
  v_dept_id      NUMBER;
BEGIN
  v_dept_id_text := SYS_CONTEXT('APP_CTX', 'DEPT_ID');

  IF v_dept_id_text IS NULL THEN
    RETURN '1 = 2';
  END IF;

  v_dept_id := TO_NUMBER(v_dept_id_text);

  RETURN 'dept_id = ' || TO_CHAR(v_dept_id);
EXCEPTION
  WHEN VALUE_ERROR THEN
    RETURN '1 = 2';
END;
/

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => USER,
    object_name     => 'SALES',
    policy_name     => 'SALES_RLS_BY_DEPT',
    function_schema => USER,
    policy_function => 'F_SALES_RLS',
    statement_types => 'SELECT,UPDATE,DELETE',
    update_check    => TRUE,
    policy_type     => DBMS_RLS.CONTEXT_SENSITIVE,
    enable          => TRUE
  );
END;
/

BEGIN
  app_ctx_pkg.set_dept_id(10);
END;
/

SELECT sales_id, dept_id, customer_id, amount
FROM sales
ORDER BY sales_id;

BEGIN
  app_ctx_pkg.set_dept_id(20);
END;
/

SELECT sales_id, dept_id, customer_id, amount
FROM sales
ORDER BY sales_id;

BEGIN
  app_ctx_pkg.set_dept_id(10);
END;
/

-- 部門10の利用者が部門20へ移動させようとする例
UPDATE sales
SET dept_id = 20
WHERE sales_id = 1;

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => USER,
    object_name     => 'SALES',
    policy_name     => 'SALES_RLS_DML',
    function_schema => USER,
    policy_function => 'F_SALES_RLS',
    statement_types => 'SELECT,INSERT,UPDATE,DELETE',
    update_check    => TRUE,
    policy_type     => DBMS_RLS.CONTEXT_SENSITIVE
  );
END;
/

CREATE OR REPLACE FUNCTION f_customer_mask(
  p_schema IN VARCHAR2,
  p_object IN VARCHAR2
) RETURN VARCHAR2
AS
BEGIN
  IF SYS_CONTEXT('APP_CTX', 'ROLE_NAME') = 'SUPPORT_MANAGER' THEN
    RETURN NULL;
  END IF;

  RETURN '1 = 2';
END;
/

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema         => USER,
    object_name           => 'CUSTOMERS',
    policy_name           => 'MASK_CUSTOMER_PHONE',
    function_schema       => USER,
    policy_function       => 'F_CUSTOMER_MASK',
    statement_types       => 'SELECT',
    sec_relevant_cols     => 'PHONE_NUMBER',
    sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS
  );
END;
/

-- 現在セッションのコンテキスト値
SELECT SYS_CONTEXT('APP_CTX', 'DEPT_ID') AS dept_id
FROM dual;

-- ユーザーが所有するポリシー定義
SELECT object_name,
       policy_name,
       policy_function,
       sel,
       ins,
       upd,
       del,
       enable
FROM user_policies
WHERE object_name = 'SALES';

-- 実行中カーソルに適用されたVPD述語
SELECT sql_id,
       object_owner,
       object_name,
       policy,
       predicate
FROM v$vpd_policy
WHERE object_name = 'SALES';

BEGIN
  DBMS_RLS.ENABLE_POLICY(
    object_schema => USER,
    object_name   => 'SALES',
    policy_name   => 'SALES_RLS_BY_DEPT',
    enable        => FALSE
  );

  DBMS_RLS.ENABLE_POLICY(
    object_schema => USER,
    object_name   => 'SALES',
    policy_name   => 'SALES_RLS_BY_DEPT',
    enable        => TRUE
  );
END;
/

BEGIN
  DBMS_RLS.DROP_POLICY(
    object_schema => USER,
    object_name   => 'SALES',
    policy_name   => 'SALES_RLS_BY_DEPT'
  );
END;
/