如何使用策略组来实现行级别的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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9842/viewspace-469871/