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