Auditing an Oracle database for security issues is very important. PeteFinnigan.com provides all of the information and tools that you will need Click here for details of PeteFinnigan.com Limited's detailed Oracle database security audit service Click here for details of PeteFinnigan.com Limited's Oracle Security Training Courses
There are 51 visitors online    
Cookie Policy:We only use essential cookies on small sections of this website. For details see here.


Back

Can Row Level Access be used on views as well as tables?

This short paper started as a discussion between myself and a colleague after I had written my papers on Row Level Security. This discussion was about whether a statement i made that row level security policy functions could be attached to views as well as tables was true. The following is a simple test case to show that this is indeed true.

	quote
	"Security policies can be associated with both database base tables
	and also database views." - I know that FGA policies may be applied to
	views, are you sure this is also possible with FGAC policies?
	quote
	
	yes definitely, it does work for views as well as tables. I am not sure
	how well it works with complex views. Here is an example to prove:
	
	SQl> -- drop the existing test user
	SQL> drop user vpd cascade;
	
	User dropped.
	
	SQL> -- create a new user with relevant privs
	SQL> create user vpd identified by vpd default tablespace users
	temporary tablespace temp;
	
	User created.
	
	SQL> grant create session,create any context,create table,create view,
	unlimited tablespace,create procedure to vpd;
	
	Grant succeeded.
	
	SQL> connect sys/change_on_install@zulia as sysdba
	Connected.
	SQL> grant execute on dbms_rls to vpd;
	
	Grant succeeded.
	
	SQL> -- create a simple view
	SQL> connect vpd/vpd@zulia
	Connected.
	SQL> create or replace view martin_view
	  2  as
	  3  select * from all_users;
	
	View created.
	
	SQL> -- create a simple predicate function
	SQL> create or replace function martin_vpd(schema_name in
	varchar2,object_name in varchar2) return v
	archar2
	  2  as
	  3  begin
	  4  return '1=2';
	  5  end;
	  6  /
	
	Function created.
	
	SQL> -- add the policy function to our view
	SQL> begin
	  2      dbms_rls.add_policy(
	  3              object_schema => 'VPD',
	  4              object_name => 'MARTIN_VIEW',
	  5              policy_name => 'MARTIN_VIEW_TEST',
	  6              function_schema => 'VPD',
	  7              policy_function => 'MARTIN_VPD',
	  8              statement_types => 'select, insert, update, delete',
	  9              update_check => TRUE,
	 10              enable => TRUE,
	 11              static_policy => FALSE);
	 12  end;
	SQL> /
	
	PL/SQL procedure successfully completed.
	
	SQL> -- this should return no rows as the predicate is '1=2'
	SQL> select * from martin_view;
	
	no rows selected
	
	SQL> -- change the predicate to return something
	SQL> create or replace function martin_vpd(schema_name in varchar2,
	object_name in varchar2)
	  2  return varchar2
	  3  as
	  4  begin
	  5   return 'username=''VPD''';
	  6  end;
	  7  /
	
	Function created.
	
	SQL> select * from martin_view;
	
	USERNAME                          USER_ID CREATED
	------------------------------ ---------- ---------
	VPD                                    85 24-OCT-03
	
	SQL> -- works as planned!!
						



Back