rls.sql

--
-- rls.sql
-- Pete Finnigan 
-- 07-Nov-2003
--
-- Example code for an article for www.securityfocus.com called "Oracle
-- Row level Security" by Pete Finnigan (pete@petefinnigan.com)
-- website http://www.petefinnigan.com - see http://www.petefinnigan.com/orasec.htm
-- for other oracle security papers by Pete Finnigan.
--
set serveroutput on size 1000000

--
-- capture the output.
--
spool rls.lis

--
-- check if the user vpd exists already, if it does abort and print a 
-- message. The reader needs to check if this is a genuine user for 
-- their database and if so rename the use used for this test else
-- (if it does exist) then it must be dropped first
--

whenever sqlerror exit rollback
connect system/manager@zulia

declare
	--
	cursor c_1 is
	select username
	from	dba_users
	where username='VPD';
	--
	lv_1 c_1%rowtype;
	--
begin
	--
	open c_1;
	fetch c_1 into lv_1;
	if c_1%found then
		close c_1;
		dbms_output.put_line('ser VPD exists, either drop this user '||
			'or rename the user in this script');
		raise_application_error(-20010,'VPD user exists - take relevant action');
	end if;
	close c_1;
	--
end;
/

whenever sqlerror continue

--
-- connect as system or a DBA - substitute your own system password and alias
-- then create the sample user "vpd" used for the tests. Also grant the
-- the relevant privileges. 
--

connect system/manager@zulia

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

grant create session to vpd;

grant create any context to vpd;

grant create table to vpd;

grant unlimited tablespace to vpd;

grant create procedure to vpd;

--
-- Connect as sys and grant access to the row level security API and session API
-- again substitute your own SYS password and alias.
--

connect sys/change_on_install@zulia as sysdba


grant execute on dbms_rls to vpd;

grant execute on dbms_session to vpd;

--
-- create the sample table in our test schema and insert some sample records 
-- to play around with
--

connect vpd/vpd@zulia

create table transactions (
	trndate date,
	credit_val number(12,2),
	debit_val number(12,2),
	trn_type varchar2(10),
	cost_center varchar2(10)) tablespace users;

insert into transactions (trndate,credit_val,debit_val,trn_type,cost_center)
values (to_date('15-OCT-2003','DD-MON-YYYY'),100.10,0.0,'PAY','CASH');

insert into transactions (trndate,credit_val,debit_val,trn_type,cost_center)
values (to_date('15-OCT-2003','DD-MON-YYYY'),50.23,0.0,'PAY','CASH');

insert into transactions (trndate,credit_val,debit_val,trn_type,cost_center)
values (to_date('15-OCT-2003','DD-MON-YYYY'),0.0,230.20,'INV','ACCOUNTS');

insert into transactions (trndate,credit_val,debit_val,trn_type,cost_center)
values (to_date('15-OCT-2003','DD-MON-YYYY'),15.24,0.0,'INT','ACCOUNTS');

commit;

select * from transactions;

--
-- Next create the application context. The package used to set the context
-- does not have to exist when the context is created.
--

show user

create or replace context vpd_test using set_vpd_context;

--
-- check the context was created OK.
--

select sys_context('userenv','ip_address') from dual;

--
-- Now create the package header and procedures that are use to set
-- the application contexts.
--

create or replace package set_vpd_context
is
	procedure set_manager;
	procedure set_accountant;
	procedure set_clerk;
end;
/

show errors package set_vpd_context

--
-- create the package body that will implement the procedures to 
-- set the contexts.
--

create or replace package body set_vpd_context
as
	--
	procedure set_manager
	is
	begin
		dbms_session.set_context('vpd_test','app_role','manager');
	end;
	--
	procedure set_accountant
	is
	begin
		dbms_session.set_context('vpd_test','app_role','accountant');
	end;
	--
	procedure set_clerk
	is
	begin
		dbms_session.set_context('vpd_test','app_role','clerk');
	end;
	--
end;
/

show errors package body set_vpd_context

--
-- create the package declaration for the actual policy function
--

create or replace package vpd_policy
as
	function vpd_predicate(schema_name in varchar2, object_name in varchar2)
	return varchar2;
end;
/

--
-- implement the row level security policy package and function. It is this 
-- function that will return the predicate to be used in each SQL execution.
--

create or replace package body vpd_policy
as
   function vpd_predicate(schema_name in varchar2,object_name in varchar2)
   return varchar2
   is
           lv_predicate varchar2(1000):='';
   begin
           if sys_context('vpd_test','app_role') = 'manager' then
                   lv_predicate:=''; -- allow all access
           elsif sys_context('vpd_test','app_role') = 'accountant' then
                   lv_predicate:='cost_center=''ACCOUNTS''';
           elsif sys_context('vpd_test','app_role') = 'clerk' then
                   lv_predicate:='cost_center=''CASH''';
           else
                   lv_predicate:='1=2'; -- block access
           end if;
           return lv_predicate;
   end;
end;
/

show errors package body vpd_policy

--
-- Finally add the policy function to the table via the row level security API
--

begin
    dbms_rls.add_policy(
            object_schema => 'VPD',
            object_name => 'TRANSACTIONS',
            policy_name => 'VPD_TEST_POLICY',
            function_schema => 'VPD',
            policy_function => 'VPD_POLICY.VPD_PREDICATE',
            statement_types => 'select, insert, update, delete',
            update_check => TRUE,
            enable => TRUE,
            static_policy => FALSE);
end;
/

--
-- This is an example of a logon trigger used to set a context for a 
-- user when they login. A real trigger would of course use a bit more
-- complexity. This example is commented out from the main script as
-- it needs GRANT ADMINISTER DATABASE TRIGGER for the user who creates
-- the trigger - I have not granted this in this example.
--

-- create or replace trigger vpd_logon_trigger
-- after logon on database
-- begin
-- 	set_vpd_context.set_accountant;
-- end;
-- /

--
-- Next, the first test case. connect as VPD but do not set the application
-- context. This should result in no rows being returned as no context is
-- set. We defined in the business rules that anyone not in any of the business
-- groups should not see any rows.
--

connect vpd/vpd@zulia

select * from transactions;

--
-- next set the vpd user's role so that he is a CLERK.
--

exec set_vpd_context.set_clerk;

--
-- use the session_context view to check the correct application role
-- has been set.
--

col namespace for a15
col attribute for a15
col value for a15
select * from session_context;

--
-- Now check what transactions can be seen. We should only be able to view
-- CASH transactions.
--

select * from transactions;

--
-- Next test what happens when an attempt is made to change the application
-- context without using the correct functions!
--

exec dbms_session.set_context('vpd_test','app_role','manager');

--
-- Next set the application role to accountant and check that only ACCOUNTS
-- transactions are visible.
--

exec set_vpd_context.set_accountant;

select * from session_context;

select * from transactions;

--
-- Finally test the last application context and set it to manager. This 
-- should allow us to view all transactions.
--

exec set_vpd_context.set_manager;

select * from session_context;

select * from transactions;

--
-- set the application context to accountant and attempt to insert
-- a record for a CASH transaction. This should fail.
--

exec set_vpd_context.set_accountant;

select *  from session_context;

insert into transactions(trndate,credit_val,debit_val,trn_type,cost_center)
values (to_date('15-OCT-2003','DD-MON-YYYY'),120.0,0.0,'PAY','CASH');

--
-- Now try and insert an ACCOUNTS transaction, this should succeed.
--

insert into transactions(trndate,credit_val,debit_val,trn_type,cost_center)
values (to_date('15-OCT-2003','DD-MON-YYYY'),120.0,0.0,'INV','ACCOUNTS');

--
-- now drop the policy function and re-create it with the update_check
-- parameter set to FALSE. This should allow any transaction to be created
-- in deference to the business rules we have defined. This allows a slight
-- circumvention of the policy rules.
--

begin
  dbms_rls.drop_policy(
   object_schema => 'VPD',
   object_name => 'TRANSACTIONS',
   policy_name => 'VPD_TEST_POLICY');
end;
/

begin
     dbms_rls.add_policy(
             object_schema => 'VPD',
             object_name => 'TRANSACTIONS',
             policy_name => 'VPD_TEST_POLICY',
             function_schema => 'VPD',
             policy_function => 'VPD_POLICY.VPD_PREDICATE',
             statement_types => 'select, insert, update, delete',
             update_check => FALSE, -- set back to FALSE
             enable => TRUE,
             static_policy => FALSE);
end;
/

insert into transactions (trndate,credit_val,debit_val,trn_type,cost_center)
values (to_date('15-OCT-2003','DD-MON-YYYY'),120.0,0.0,'PAY','CASH');

--
-- Now connect "AS SYSDBA" and check what can been seen. SYS, INTERNAL or
-- any user connected "AS SYSDBA" is exempt from any row level security
-- policies. 
--

connect system/manager@zulia as sysdba

select * from vpd.transactions;

--
-- check out what policies and assosiated cursors are in the library cache.
--

col sql_text for a25
col predicate for a20
col policy for a15
col object_name for a15
select substr(sql_text,1,25) sql_text,
   predicate,
   policy,
   object_name
from v$sqlarea ,v$vpd_policy
where hash_value = sql_hash;

--
-- check the user policies view for what policies have been defined
-- for our user VPD.
--

col object_name for a15
col policy_name for a15
col function for a15
col sel for a3
col ins for a3
col upd for a3
col del for a3
col chk for a3
col enb for a3
select     object_name object_name,
   policy_name policy_name,
   function function,
   sel sel,
   ins ins,
   upd upd,
   del del,
   chk_option chk,
   enable enb
from user_policies;

--
-- demonstration to show that PL/SQL source for policies can be retreived
-- from the SGA.
--

col name for a15
col text for a64
set pages 0
break on name
select name,text
from user_source
order by name,type,line;

--
-- use dbms_session to list out application context values that
-- have been set for the logged in user. In this case VPD.
--

connect vpd/vpd@zulia

exec set_vpd_context.set_manager;

set serveroutput on size 1000000
declare
	lv_context dbms_session.appctxtabtyp;
	lv_size number;
begin
	dbms_session.list_context(lv_context,lv_size);
	dbms_output.put_line(lv_context(1).namespace||'.'
		||lv_context(1).attribute||' = '
		||lv_context(1).value);
end;
/

--
-- re-create the policy function with the extra SQL in it to access the base 
-- table. This should not be possible and should cause the example to hang!
--

create or replace package body vpd_policy
as
   function vpd_predicate(schema_name in varchar2,object_name in varchar2)
   return varchar2
   is
           lv_predicate varchar2(1000):='';
           lv_trndate   date:=null;
   begin
	   select trndate into lv_trndate
	   from transactions
	   where rownum=1;
           if sys_context('vpd_test','app_role') = 'manager' then
                   lv_predicate:='';
           elsif sys_context('vpd_test','app_role') = 'accountant' then
                   lv_predicate:='cost_center=''ACCOUNTS''';
           elsif sys_context('vpd_test','app_role') = 'clerk' then
                   lv_predicate:='cost_center=''CASH''';
           else
                   lv_predicate:='1=2';
           end if;
           return lv_predicate;
   end;
end;
/

--
-- Set the context to accountant and test the new policy function
--

exec set_vpd_context.set_accountant;

select * from transactions;
-- This should give a hanging session

--
-- example to show that granting the privilege "exempt access policy"
-- will bypass the row level security set up.
--

sho user

select * from transactions;

connect system/manager@zulia

grant exempt access policy to vpd;

connect vpd/vpd@zulia

select * from transactions;

--
-- Example to show how to trace the policy function with normal SQL trace
--

sho user

exec set_vpd_context.set_accountant;

alter session set sql_trace=true;

select * from transactions;

--
-- A better example to show the use of the 10730 event to trace the optimizer
-- and get details of the row leve security internals!
--

sho user

exec set_vpd_context.set_accountant;

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

set autotrace on explain
select * from transactions;

--
-- A final example to trace the policy function and row level security is
-- to use the event 10060.
--

sho user

analyze table transactions estimate statistics;

create table kkoipt_table(c1 int, c2 varchar2(80));

connect system/manager@zulia

alter system flush shared_pool;

connect vpd/vpd@zulia

exec set_vpd_context.set_accountant;

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

set autotrace on explain;
select * from transactions;

set autotrace off
select c2 from (select distinct c1,c2 from kkoipt_table) order by c1;

--
-- examples completed.
--

spool off