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.
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;