VPD

--1、在APPS中创建表，赋权给PO, ONT用户

createtable hand_vpd_test_tb1

(column1  varchar2(30),

 db_user  varchar2(30)

 )

grantselect ,insert, update on hand_vpd_test_tb1 to po,ont;

 

 

--2、创建策略函数package

--使用hand_vpd_tst_security.pck 创建策略函数包

--赋权

grant execute on  apps.hand_vpd_tst_securityto public;

CREATEPUBLIC SYNONYM hand_vpd_tst_security FOR apps.hand_vpd_tst_security;

 

--3、添加VPD策略 

begin

  

 DBMS_Rls.Add_Policy('APPS','HAND_VPD_TEST_TB1', 'INSERT_POLICY','APPS','HAND_VPD_TST_SECURITY.INSERT_SECURITY','INSERT', TRUE);

 DBMS_Rls.Add_Policy('APPS','HAND_VPD_TEST_TB1', 'SELECT_POLICY','APPS','HAND_VPD_TST_SECURITY.SELECT_SECURITY','SELECT');

end;

select *from DBA_POLICIES a where a.object_name = 'HAND_VPD_TEST_TB1';

--删除VPD策略（备用）

begin

 dbms_rls.drop_policy('APPS','HAND_VPD_TEST_TB1','USER_DATA_INSERT_POLICY');

 dbms_rls.drop_policy('APPS','HAND_VPD_TEST_TB1','USER_DATA_SELECT_POLICY');

end;  

  

--4、Select, Insert  测试

begin

 insert into hand_vpd_test_tb1 values('test1-po','PO');

 insert into hand_vpd_test_tb1 values('test1-ont','ONT');

end;

--5、切换到 PO用户登录

select *from apps.hand_vpd_test_tb1

--结果：

--策略函数执行出错： 

--trace发现是ora-06550错误，

selectvalue from v$parameter where name = 'user_dump_dest';

altersession set tracefile_identifier = 'Hand_vpd_test2';

altersession set sql_trace=true;

select *from apps.hand_vpd_test_tb1

altersession set sql_trace=false;

--表面是SELECT_SECURITY未声明，实际是XX用户执行策略函数时需要访问被施加策略的对象：hand_vpd_test_tb1，因为对该对象无权限，而导致报此错误；

--解决方案

grantselect ,insert, update on hand_vpd_test_tb1 to public;

--6 再次测试：

-- 切换到 PO用户登录

select *from apps.hand_vpd_test_tb1

--结果只出现  DB_USER=PO的记录；

--7 做insert测试

 insert into apps.hand_vpd_test_tb1values ('test1-po','PO');

 --结果：顺利插入

 insert into apps.hand_vpd_test_tb1values ('test1-ont','ONT');

 --结果：报 ORA-28115: policy with check option violation 错误