vpd2.sql

-- -----------------------------------------------------------------------------
-- File Name   : vpd2.sql
-- -----------------------------------------------------------------------------
-- Maintainer  : Pete Finnigan (www.petefinnigan.com)
-- Copyright   : Copyright (C) 2009 PeteFinnigan.com Limited. All rights
--               reserved. All registered trademarks are the property of their
--               respective owners and are hereby acknowledged.
-- -----------------------------------------------------------------------------
--  Usage      : The script provided here is available free. You can do anything
--               you want with it commercial or non commercial as long as the
--               copyright and this notice are not removed or edited in any way.
--               The scripts cannot be posted / published / hosted or whatever
--               anywhere else except at www.petefinnigan.com/vpd2.sql
-- ------------------------------------------------------------------------------

prompt [*] connect as SYS
pause

connect sys/oracle1 as sysdba

prompt [*] Drop the test user
drop user pxf cascade;

prompt [*] Recreate the test user

create user pxf identified by pxf default tablespace users temporary tablespace temp;

grant create session to pxf;

grant create any context to pxf;

grant create table to pxf;

grant unlimited tablespace to pxf;

grant create procedure to pxf;

grant execute on dbms_rls to pxf;

grant execute on dbms_session to pxf;

create table pxf.emp as select * from scott.emp;

prompt [*] Now connect as PXF
pause

connect pxf/pxf

prompt [*] Select from the sample table - should be 15 rows
pause

select * from emp;

Prompt [*] Let everyone see the table and connect as SCOTT and select again
prompt [*] should still be 15 rows

grant select on pxf.emp to public;

connect scott/tiger

select * from pxf.emp;

prompt [*] connect as PXF again and set up a simple VPD
prompt [*] that restricts access to dept 10
pause

connect pxf/pxf

create or replace function predicate (pv_schema in varchar2, pv_object in varchar2)
return varchar2
as
begin
	return 'deptno != ''10''';
end;
/

begin
	dbms_rls.add_policy(
		object_schema => 'PXF',
		object_name => 'EMP',
		policy_name => 'PXFTEST',
		policy_function => 'PREDICATE');
end;
/

prompt [*] Finally connect as SCOTT and see if he is blocked from seeing the data
prompt [*] should now be 12 rows!
pause

connect scott/tiger

select * from pxf.emp;

prompt [*]
pause
sho user

prompt [*] set up trace and dump the predicate
pause
alter session set sql_trace=true;

alter session set events '10730 trace name context forever';

prompt [*] Dump the data from the emp table
select * from pxf.emp;

prompt [*] Turn trace off
alter session set events '10730 trace name context off';
alter session set sql_trace=false;

prompt [*] Lets look at the trace file
pause

prompt [*]
pause

prompt [*] View the predicate
pause

select object_owner,object_name,policy_name,
       pf_owner,pf_owner,function
from all_policies;

set serveroutput on size 1000000

declare
	predic varchar2(1000);
begin
	dbms_output.put_line('The predicate is :'||pxf.predicate('PXF','EMP'));
end;
/

prompt [*] I have seen this type of design:
prompt [*] where the predicate functions are executable by all
pause

connect pxf/pxf
grant execute on predicate to public;
connect scott/tiger

set serveroutput on size 1000000

declare
	predic varchar2(1000);
begin
	dbms_output.put_line('The predicate is :'||pxf.predicate('PXF','EMP'));
end;
/

prompt [*] Access the data directly
pause

-- -------------------------------------------------------
-- change the file number and block number to suit your
-- database not mine.
-- -------------------------------------------------------

select distinct dbms_rowid.rowid_block_number(rowid) blk,
   dbms_rowid.rowid_relative_fno(rowid) fno
from pxf.emp;

select file_name from dba_data_files
where file_id=4;

alter system dump datafile 4 block 444;

prompt [*] Lets try again as monitor
pause

connect monitor/monitor

select distinct dbms_rowid.rowid_block_number(rowid) blk,
   dbms_rowid.rowid_relative_fno(rowid) fno
from pxf.emp;

select file_name from dba_data_files
where file_id=4;

alter system dump datafile 4 block 444;