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?
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).
- Virtual Private Database, securing your data
- Courtesy of Tom Kyte: Generating rows in SQL with the CUBE statement – no dummy table or table function required
- Making up records in SQL Queries – Table Functions and 10g Model clause
- SQL*Plus or Report style Break Groups in SQL Query
- VPD part 2; The added functionality in 10g