What happens if the policy function on table A returns a predicate that refers to table B and table B has its own policy. For example: in a Master-Detail situation, we have a policy function on the Master table. We also want to enforce that a user can only see child-records from the masters he or she is allowed to see. Does the policy function on table A also kick in when table A is referenced from the policy predicate on table B? And as which user is that policy function executed – the current User or the owner of the Policy Function?
This may look like a merely theoretical exercise to you, but it is something I am actually running into at my current project. So I need an answer.
We can create a simple example. We define a simple Policy Function on table DEPT in the sample SCOTT schema:
create or replace function get_dept_predicate ( p_schema_name in varchar2 , p_table_name in varchar2 ) return varchar2 is begin if user = 'UP' then return 'DEPTNO in (10, 20) '; else if user = 'SCOTT' then return 'DEPTNO in (20,30) '; else return 'DEPTNO = 30 '; end if; end if; end get_dept_predicate; / begin dbms_rls.add_policy ( object_schema => 'SCOTT' , object_name => 'DEPT' , policy_name => 'DEPT_POLICY' , policy_function => 'GET_DEPT_PREDICATE' , function_schema => 'SCOTT' , statement_types => 'SELECT, INSERT, UPDATE, DELETE' , update_check => true , enable => true ); end; /
Querying table DEPT returns appropriate results:
SQL> select * from dept 2 / DEPTNO DNAME LOC ---------- -------------- ------------- 20 RESEARCH DALLAS 30 SALES CHICAGO SQL> grant select on dept to public 2 / Grant succeeded. SQL> connect up/up Connected. SQL> select * from dept 2 / DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS
We have clearly established a policy that specifies which departments can be seen by a user in our database. It should be an obvious thing to ensure that our users can only see employees from those departments that they are allowed to see. For that we also need a policy function on EMP, as a simple select * from emp right now will return all (14) employees. So here we go:
create or replace function get_emp_predicate ( p_schema_name in varchar2 , p_table_name in varchar2 ) return varchar2 is begin return 'exists ( select 1 from dept d where d.loc !=''BOSTON'' and d.deptno = emp.deptno'; end; / begin dbms_rls.add_policy ( object_schema => 'SCOTT' , object_name => 'EMP' , policy_name => 'HR_POLICY' , policy_function => 'GET_EMP_PREDICATE' , function_schema => 'SCOTT' , statement_types => 'SELECT, INSERT, UPDATE, DELETE' , update_check => true , enable => true ); end get_emp_predicate; /
The predicate returned for each user requires the employees to be from an existing department, as long as it is not located in BOSTON. Now my question is: will this return employees from departments that are excluded to the current user? Will the policy function on DEPT kick in – and if so: for whom?
Let’s try:
connect scott/tiger SQL> select ename 2 , deptno 3 from emp 4 / ENAME DEPTNO ---------- ---------- FORD 20 ADAMS 20 SCOTT 20 JONES 20 SMITH 20 JAMES 30 TURNER 30 BLAKE 30 MARTIN 30 WARD 30 ALLEN 30 11 rows selected. SQL> connect up/up Connected. SQL> select ename 2 , deptno 3 from emp 4 / ENAME DEPTNO ---------- ---------- MILLER 10 KING 10 CLARK 10 FORD 20 ADAMS 20 SCOTT 20 JONES 20 SMITH 20 8 rows selected.
So, clearly the Policy Function provides its predicate for the table nested in another Policy Function’s predicate. And the predicate is evaluated for the person running the query. UP has access to departments 10 and 20, therefore the query on EMP only returns employees from these two departments. SCOTT in contract can see departments 20 and 30, so SCOTTs query on EMP returns all employees from those two departments.
In a nutshell, this is good news. It means that I can implement the requirement that details should only be visible when the master is visible by simply checking the existince of the master in the policy predicate for the detail: where exists( select 1 from master where master.id = detail.master_id)
.
En wat als de policy function refereeert naar de tabel zelf? Bijvoorbeeld: User mag alleen maar medewerkers zien die onder een Manager vallen die de User mag zien? Zoiets als:
Which predicate is applied in the subquery? Is this a recursive nightmare?
We have the exact same policies implemented with a EXISTS clause. The only downside to it is the performance hit. Do you have any idea what happens if you have a 10+ table query in a report and all these tables get an EXISTS clause added in the predicate that refer to some central table.
We’re still struggling with it. So yes, it does work but do consider the performance hit!
Rob, You are most welcome. Good to see that this post actually serves a purpose! All the best.
What a timely blog entry. I just spent a half hour this afternoon trying to explain to a client how VPD policies could work to restrict access to their reporting database content (a DW-like info melange). This gives me a great example for them to see how detail data access (i.e. Emp) and corresponding reference data restrictions (i.e. Dept) can combine, without building intensely ugly VPD policies on the detail. Slick!! Thanks!