VPD – Using Function

1.)
 create table cmagno.teste_vpd
 (num number,
 dt date,
 text varchar2(1000))
 tablespace users;
create sequence cmagno.seq_test 
minvalue 1 maxvalue 999999999999 
nocache order increment by 1;
2.)
declare
 i number;
 begin
for i in 1..1000 loop
 insert into cmagno.teste_vpd values
 (cmagno.seq_test.nextval, sysdate,'TEST :' || TO_CHAR(I));
 end loop;
 COMMIT;
 end;
 /
3.)
 SQL> SELECT COUNT(*) FROM cmagno.teste_vpd;
COUNT(*)
 ----------
 1000
1* select * from cmagno.teste_vpd where rownum<10
 SQL> /
NUM DT        TEXT
 ---------- --------- ----------------------------
 546 25-FEB-16 TEST :546
 547 25-FEB-16 TEST :547
 548 25-FEB-16 TEST :548
 549 25-FEB-16 TEST :549
 550 25-FEB-16 TEST :550
 551 25-FEB-16 TEST :551
 552 25-FEB-16 TEST :552
 553 25-FEB-16 TEST :553
 554 25-FEB-16 TEST :554
SQL>
 /
4.) Function
CREATE OR REPLACE FUNCTION CMAGNO.num_gt_500 
( v_schema IN VARCHAR2,  v_objname IN VARCHAR2)
RETURN VARCHAR2 AS
 v_where VARCHAR2 (200);
BEGIN
 v_where := 'num>=500';
 RETURN (v_where);
 END num_gt_500;
 /
5.) Add Policy
 BEGIN
 DBMS_RLS.ADD_POLICY (
 object_schema     => 'CMAGNO',
 object_name       => 'TESTE_VPD',
 policy_name       => 'NUM_GT_500_PLCY',
 policy_function   => 'NUM_GT_500',
 sec_relevant_cols => 'NUM');
 END;
 /
6.) Just rows with NUM >= 500
SQL> SELECT COUNT(*) FROM cmagno.teste_vpd;
COUNT(*)
 ----------
 501
7.) Droping Policy:
 BEGIN
 DBMS_RLS.DROP_POLICY (object_schema     => 'CMAGNO',
 object_name       => 'TESTE_VPD',
 policy_name       => 'NUM_GT_500_PLCY');
 END;
 /
8.)
 SQL> SELECT COUNT(*) FROM cmagno.teste_vpd;
COUNT(*)
 ----------
 1000
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s