OraMatt: YABAOracle

Virtual Private Database or VPD is one of those cool Oracle Database features that scare some folks because they think it’s SUPER HARD to setup and use.

This little bit of demo code hopefully will dispel that …

ENJOY!

drop table watchlist purge;

create table watchlist (
id number generated by default as identity (start with 1 increment by 1),
name varchar2(100),
status number, -- status of 1 means we are watching you...
indate date,
primary key (id)
)
;


insert into watchlist (name, status, indate) values ('matt',0,sysdate);
insert into watchlist (name, status, indate) values ('tim', 1, sysdate);
insert into watchlist (name, status, indate) values ('burt', 0, sysdate);
insert into watchlist (name, status, indate) values ('dave', 1, sysdate);
insert into watchlist (name, status, indate) values ('chib', 0, sysdate);

commit;

select count(*) from watchlist;


Create a function for VPD as SYS and set up a VPD Policy
-- run as sys
-- don't let folks know they are on the watchlist
create or replace function auth_watchlist( 
schema_var in varchar2,
table_var in varchar2
)
return varchar2
is
return_val varchar2 (400);
begin
return_val := 'status = 0';
return return_val;
end auth_watchlist;
/
begin
dbms_rls.add_policy (
object_schema => 'matt',
object_name => 'watchlist',
policy_name => 'watchlist_policy',
function_schema => 'matt',
policy_function => 'auth_watchlist',
statement_types => 'select, insert, update, delete'
);
end;
/

Test as the VPD policy

-- Test the policy as a regular user
conn matt;
select count(*) from watchlist;

Test the policy as SYS…you should get back ALL the rows
conn / as sysdba
select count(*) from matt.watchlist;

Procedure to generate some random data to keep playing/testing.
conn matt
create or replace procedure insert_watchlist (v_howmany in number)
as
type t_test is table of watchlist%ROWTYPE;

l_howmany	NUMBER;

l_test t_test := t_test();

begin
for i in 1..v_howmany loop
l_test.extend;

l_test(l_test.last).id			:= dbms_random.value(10, 9999999);
l_test(l_test.last).name		:= dbms_random.value(10, 9999999);
l_test(l_test.last).status		:= round( dbms_random.value(0,1));
l_test(l_test.last).indate		:= sysdate;
end loop;

forall x in l_test.first .. l_test.last
insert into watchlist values l_test(x);
commit;

end;
/

Share this:

				
				X
			

				
				Facebook
			
Like Loading...