Pop-quiz: VPD policy that depends on a table with a policy…

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