Oracle VPD策略示例

1.未创建前使用oe用户登录查询：

+ View Code?

1 2 3 4 5 6 7 8 9 10 11

SQL> select * from orders;     ORDER_ID ORDER_DATE                               ORDER_MO CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION_ID ---------- ---------------------------------------- -------- ----------- ------------ ----------- ------------ ------------       2458 17-AUG-07 05.34.12.234359 AM             direct           101            0     78279.6          153       2397 20-NOV-07 06.41.54.696211 AM             direct           102            1     42283.2          154       ......   105 rows selected.   SQL>

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SQL> select * from orders where sales_rep_id=159;     ORDER_ID ORDER_DATE                               ORDER_MO CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION_ID ---------- ---------------------------------------- -------- ----------- ------------ ----------- ------------ ------------       2435 03-SEP-07 01.22.53.134567 PM             direct           144            6       62303          159       2405 02-JUL-07 06.49.13.678123 AM             direct           164            5        1233          159       2430 02-OCT-07 08.18.36.663332 PM             direct           101            8     29669.9          159       2439 01-SEP-07 12.19.37.811132 AM             direct           105            1     22150.1          159       2450 12-APR-07 08.18.10.362632 AM             direct           147            3        1636          159       2452 07-OCT-07 10.59.43.462632 AM             direct           149            5       12589          159       2457 01-NOV-07 01.22.16.162632 PM             direct           118            5     21586.2          159   7 rows selected.   SQL>

2.创建VPD策略

以sys用户用sysdba权限登录pdb2
2.1.创建策略函数

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12

create or replace function auth_orders(   schema_var in varchar2,   table_var  in varchar2  )  return varchar2  is   return_val varchar2 (400);  begin   return_val := 'sales_rep_id = 159';   return return_val;  end auth_orders; /

2.2.创建VPD策略

+ View Code?

1 2 3 4 5 6 7 8 9 10 11

begin   dbms_rls.add_policy (     object_schema    => 'oe',     object_name      => 'orders',     policy_name      => 'orders_policy',     function_schema  => 'sys',     policy_function  => 'auth_orders',     statement_types  => 'select, insert, update, delete'    );  end; /

 3.测试
以oe用户登录pdb2

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SQL> select * from oe.orders;     ORDER_ID ORDER_DATE                               ORDER_MO CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION_ID ---------- ---------------------------------------- -------- ----------- ------------ ----------- ------------ ------------       2435 03-SEP-07 01.22.53.134567 PM             direct           144            6       62303          159       2405 02-JUL-07 06.49.13.678123 AM             direct           164            5        1233          159       2430 02-OCT-07 08.18.36.663332 PM             direct           101            8     29669.9          159       2439 01-SEP-07 12.19.37.811132 AM             direct           105            1     22150.1          159       2450 12-APR-07 08.18.10.362632 AM             direct           147            3        1636          159       2452 07-OCT-07 10.59.43.462632 AM             direct           149            5       12589          159       2457 01-NOV-07 01.22.16.162632 PM             direct           118            5     21586.2          159   7 rows selected.   SQL>

 4.删除VPD策略

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

$ sqlplus '/as sysdba' SQL> alter session set container=pdb2;   Session altered.   SQL> drop function auth_orders;   Function dropped.   SQL> exec dbms_rls.drop_policy('oe','orders','orders_policy');   PL/SQL procedure successfully completed.   SQL>