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

4

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

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

4 Comments

  1. 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:

    create or replace
    function get_emp_predicate
    ( p_schema_name  in   varchar2
    , p_table_name   in   varchar2
    ) return varchar2
    is
    begin
       if user = 'SCOTT'
       then
        return 'ENAME = ''BLAKE'' or exists ( select 1 from emp e where e.empno = emp.mgr)';
       else if user = 'UP'
            then
              return 'DEPTNO=10 or exists ( select 1 from emp e where e.empno = emp.mgr)';
            end if;
       else
              return 'JOB=''SALESMAN'' or exists ( select 1 from emp e where e.empno = emp.mgr)';
       end if;
    end;
    

    Which predicate is applied in the subquery? Is this a recursive nightmare?

  2. 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!

  3. 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!