如何使用策略組來實現行級別的VPD

我們知道可以使用基於行的VPD來隱式修改使用者發出的SQL語句，從而限制使用者能夠檢視到的資料。
VPD常用的是策略，不過使用策略組能夠實現更大的功能。

1、建立驅動上下文
SQL> connect hsj/oracle
Connected.
SQL> CREATE or replace CONTEXT app_driver USING hsj.apps_context;

Context created.

2、建立設定驅動上下文的包：
SQL> CREATE OR REPLACE PACKAGE hsj.apps_context
is
  PROCEDURE set_driver ( policy_group varchar2 );
end;
/
  2    3    4    5
Package created.

SQL> CREATE OR REPLACE PACKAGE BODY hsj.apps_context
is
  PROCEDURE set_driver ( policy_group varchar2 ) is
    BEGIN
      DBMS_SESSION.SET_CONTEXT('APP_DRIVER', 'ACTIVE_APP', policy_group );
    END;
END;
/
  2    3    4    5    6    7    8
Package body created.

3、設定驅動上下文相關屬性：
SQL> begin
  2    begin
  3      dbms_rls.drop_policy_context(
  4        object_schema =>'OE',
  5        object_name => 'ORDERS' ,
  6        namespace => 'APP_DRIVER',
  7        attribute => 'ACTIVE_APP');
  8     exception when others then
  9        null;
 10     end;
 11     dbms_rls.add_policy_context(
 12        object_schema =>'OE',
 13        object_name => 'ORDERS' ,
 14        namespace => 'APP_DRIVER',
 15        attribute => 'ACTIVE_APP');
 16  end;
 17  /

PL/SQL procedure successfully completed.

4、建立oe_app上下文以及設定屬性的包
SQL> CREATE or replace CONTEXT oe_app USING hsj.oe_context;

Context created.

SQL> CREATE OR REPLACE PACKAGE oe_context IS
  PROCEDURE set_cust_id;
  FUNCTION cust_order (
    object_schema       VARCHAR2,
    object_name VARCHAR2 )
    RETURN VARCHAR2;
END;
/  2    3    4    5    6    7    8

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY oe_context IS
  2    PROCEDURE set_cust_id
  3    is
  4    BEGIN
  5      apps_context.set_driver('OE_GRP');     -- set the driver
  6    EXCEPTION
  7      WHEN no_data_found THEN
  8        apps_context.set_driver('XX');       -- set the driver
  9    END;
 10
 11    FUNCTION cust_order (
 12      object_schema  VARCHAR2,
 13      object_name    VARCHAR2 )
 14      RETURN VARCHAR2
 15    IS
 16    BEGIN
 17      RETURN 'customer_id = 102';
 18    END cust_order;
 19  END;
 20  /

Package body created.

5、建立ac_app上下文以及設定屬性的包
SQL> CREATE or replace CONTEXT ac_app USING hsj.ac_context;

Context created.

SQL> CREATE OR REPLACE PACKAGE ac_context IS
  PROCEDURE set_cust_id;
  FUNCTION cust_order (
    object_schema       VARCHAR2,
    object_name VARCHAR2 )
    RETURN VARCHAR2;
END;
/  2    3    4    5    6    7    8

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY ac_context IS
  2    PROCEDURE set_cust_id
  3    is
  4    BEGIN
  5      apps_context.set_driver('AC_GRP');     -- set the driver
  6    EXCEPTION
  7      WHEN no_data_found THEN
  8        apps_context.set_driver('XX');       -- set the driver
  9    END;
 10
 11    FUNCTION cust_order (
 12      object_schema  VARCHAR2,
 13      object_name    VARCHAR2 )
 14      RETURN VARCHAR2
 15    IS
 16    BEGIN
 17      RETURN 'customer_id = 101';
 18    END cust_order;
 19  END;
 20  /

Package body created.

6、建立策略組：
SQL> begin
  DBMS_RLS.CREATE_POLICY_GROUP('OE', 'ORDERS', 'OE_GRP');
end;
/
  2    3    4
PL/SQL procedure successfully completed.

SQL> begin
  DBMS_RLS.CREATE_POLICY_GROUP('OE', 'ORDERS', 'AC_GRP');
end;
/
  2    3    4
PL/SQL procedure successfully completed.

7、建立策略，並將該策略新增到策略組
SQL> begin
dbms_rls.add_grouped_policy (
 object_schema=>'oe',  object_name=>'orders',
 policy_group =>'oe_grp',
 policy_name => 'oe_security',
 function_schema =>'hsj',
 policy_function => 'oe_context.cust_order');
end;
/
  2    3    4    5    6    7    8    9
PL/SQL procedure successfully completed.

SQL> exec dbms_rls.add_grouped_policy ('oe', 'orders', 'ac_grp', 'ac_security','hsj', 'ac_context.cust_order');

PL/SQL procedure successfully completed.

8、賦予許可權
SQL> connect hsj/oracle
Connected.
SQL> grant execute on ac_context to public;

Grant succeeded.

SQL> grant execute on oe_context to public;

Grant succeeded.

9、測試策略組是否生效。
SQL> connect oe/oe
Connected.
SQL> exec hsj.ac_context.set_cust_id;
SQL> select customer_id,order_total from orders;

CUSTOMER_ID ORDER_TOTAL
----------- -----------
        101     78279.6
        101     33893.6
        101       48552
        101     29669.9

SQL> exec hsj.oe_context.set_cust_id;

PL/SQL procedure successfully completed.

SQL> select customer_id,order_total from orders;

CUSTOMER_ID ORDER_TOTAL
----------- -----------
        102     42283.2
        102       10523
        102     10794.6
        102      5610.6

而事實上，oe.orders表裡有105條記錄：
SQL> connect / as sysdba
Connected.
SQL> select count(*) from oe.orders;

  COUNT(*)
----------
       105

因此很明顯，我們設定的策略組生效了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9842/viewspace-469871/，如需轉載，請註明出處，否則將追究法律責任。