1.未创建前使用oe用户登录查询:
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>
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.创建策略函数
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策略
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
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策略
$ 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>