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