{アプリケーションコンテキスト}Oracle Virtual Private Databaseポリシーを作成（ファイングレインアクセス制御ポリシー）

演習

 

https://docs.oracle.com/cd/F19136_01/dbseg/using-oracle-vpd-to-control-data-access.html#GUID-52A959FA-E021-4C3A-B532-0C6289BDD50F

 

--手順1: ユーザー・アカウントとサンプル表の作成
conn / as sysdba

GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE TRIGGER, ADMINISTER DATABASE TRIGGER 
TO sysadmin_vpd IDENTIFIED BY oracle;

GRANT EXECUTE ON DBMS_SESSION TO sysadmin_vpd;
GRANT EXECUTE ON DBMS_RLS TO sysadmin_vpd;

GRANT CREATE SESSION TO tbrooke IDENTIFIED BY oracle;
GRANT CREATE SESSION TO owoods IDENTIFIED BY oracle;

conn test/test
CREATE TABLE customers (
 cust_no    NUMBER(4), 
 cust_email VARCHAR2(20),
 cust_name  VARCHAR2(20));

INSERT INTO customers VALUES (1234, 'TBROOKE', 'Thadeus Brooke');
INSERT INTO customers VALUES (5678, 'OWOODS', 'Oberon Woods');

GRANT SELECT ON customers TO sysadmin_vpd;

CREATE TABLE orders_tab (
  cust_no  NUMBER(4),
  order_no NUMBER(4));

INSERT INTO orders_tab VALUES (1234, 9876);
INSERT INTO orders_tab VALUES (5678, 5432);
INSERT INTO orders_tab VALUES (5678, 4592);

GRANT SELECT ON orders_tab TO tbrooke;
GRANT SELECT ON orders_tab TO owoods;

--手順2: データベース・セッション・ベースのアプリケーション・コンテキストの作成
conn sysadmin_vpd/oracle

CREATE OR REPLACE CONTEXT orders_ctx USING orders_ctx_pkg;

--手順3: アプリケーション・コンテキストを設定するPL/SQLパッケージの作成

CREATE OR REPLACE PACKAGE orders_ctx_pkg IS 
  PROCEDURE set_custnum;
 END;
/
CREATE OR REPLACE PACKAGE BODY orders_ctx_pkg IS
  PROCEDURE set_custnum
  AS
    custnum NUMBER;
  BEGIN
     SELECT cust_no INTO custnum FROM test.CUSTOMERS
        WHERE cust_email = SYS_CONTEXT('USERENV', 'SESSION_USER');
     DBMS_SESSION.SET_CONTEXT('orders_ctx', 'cust_no', custnum);
  EXCEPTION
   WHEN NO_DATA_FOUND THEN NULL;
  END set_custnum;
END;
/

--手順4: アプリケーション・コンテキストのPL/SQLパッケージを実行するログイン・トリガーの作成
CREATE TRIGGER set_custno_ctx_trig AFTER LOGON ON DATABASE
 BEGIN
  sysadmin_vpd.orders_ctx_pkg.set_custnum;
 END;
/

--手順5: ログオン・トリガーのテスト

conn tbrooke/oracle

SELECT SYS_CONTEXT('orders_ctx', 'cust_no') custnum FROM DUAL;

--手順6: ユーザー・アクセスを自分の注文に制限するPL/SQLポリシー関数の作成

conn sysadmin_vpd/oracle
CREATE OR REPLACE FUNCTION get_user_orders(
  schema_p   IN VARCHAR2,
  table_p    IN VARCHAR2)
 RETURN VARCHAR2
 AS
  orders_pred VARCHAR2 (400);
 BEGIN
  orders_pred := 'cust_no = SYS_CONTEXT(''orders_ctx'', ''cust_no'')'; 
 RETURN orders_pred;
END;
/

--手順7: 新しいセキュリティ・ポリシーの作成

BEGIN
 DBMS_RLS.ADD_POLICY (
  object_schema    => 'test', 
  object_name      => 'orders_tab', 
  policy_name      => 'orders_policy', 
  function_schema  => 'sysadmin_vpd',
  policy_function  => 'get_user_orders', 
  statement_types  => 'select');
END;
/

--手順8: 新しいポリシーのテスト

conn tbrooke/oracle
SELECT * FROM test.orders_tab;


conn owoods/oracle
SELECT * FROM test.orders_tab;