Skip to Main Content
  • Questions
  • Row level security of multiple views of different predicate in same package

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, san.

Asked: January 16, 2018 - 7:03 pm UTC

Last updated: January 17, 2018 - 5:54 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi Tom,
I have a requirement as follows.
We have multiple views where we want to implement row level security.

We have designed the following
1. A table ( test_pred_tab) which contains the username , view name and corresponding predicate.

 username     view_name         sql_predicate   
        emp1          view1               id=10
        emp2          view2               id=20 
        emp2          view3               id=5
        emp3          view1               id=30          


2. A log on trigger which calls the package (containing the procedure to fetch the predicate from the above table)


In side the procedure, we have query which aggregates the predicates with username like below.


   SELECT listagg(test_pred_tab.sql_predicate,' OR ') WITHIN GROUP ( ORDER BY NULL) into l_pred FROM test_pred_tab  
     WHERE username = l_user;




Then using the dbms_session.set_context , the predicate is set for the query rewrite using dbms_rls policy.



The issue here is when we try to query for one view say view3,

 select * from view2; 


I am getting the output even with predicates of others as below.

 select * (select * from view3 where id=20 OR id=5) ;  


But the expected o/p shoule be

 select * from view3 where id=5;  



Now, I require a suggestion, whether is it possible to have multiple contexts and throw the predicate based on the view name and username only inside a single procedure.

Else, do we need to have separate context, separate procedure, separate policy functions for each table/view . Because, we have nearly 20 views where the security has to be implemented and if we go with this option, we have may 20 contexts, 20 procedures ( 1 each for view).. Can you let me know the approach for this.

and Connor said...

Policies are intended to be *defined* once, not dynamically assigned as people login.

You can have a single policy per table and a *single* policy function, which can use your table to return the correct predicate

eg some pseudo-code might be

function rls_examp( p_own in varchar2, p_obj in varchar2 ) return varchar2 as
begin
  select predicate
  into   l_pred
  from   my_table
  where  username = sys_context('userenv','session_user')
  and    view_name = p_obj;
  return l_pred;
exception
  when no_data_found then  
  return '1=0';
end;



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library