0 
0 Virtual Private Database does not let me go. Today I had another burning question to which I could not see the immediate logical answer. I know that when a Policy has been set up for a table and I execute a query against that table the Policy Function will return a predicate that is added to my own where-clause to further restrain my result set. Depending on who the current user is, this predicate may vary.
My question is: when I embed this query inside a PL/SQL package that has been created as Definer Rights, the default setting, and I invoke the procedure that contains the query, will the VPD predicate that gets applied be the one set up for me – the current USER – or the owner of the package? We know that normally the Definer Rights package is executed under the privileges and name resolution of the Definer or Owner of the package. So what will it be?
First I set up a very simple policy on the EMP table:
create or replace
function get_emp_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_emp_predicate;
/
begin
dbms_rls.add_policy
( object_schema => 'SCOTT'
, object_name => 'EMP'
, policy_name => 'EMP_POLICY'
, policy_function => 'GET_EMP_PREDICATE'
, function_schema => 'SCOTT'
, statement_types => 'SELECT, INSERT, UPDATE, DELETE'
, update_check => true
, enable => true
);
end;
/
When executing queries as SCOTT, I only see Employees for departments 20 and 30 and when querying as UP – another user – then only 10 and 20 staff are returned:
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
as UP:
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
Now I create a simple package that will perform a query against EMP and write the results to the output. The package is created under the SCOTT schema:
create or replace
package emp_reporter
is
procedure show_all_emps
;
end emp_reporter;
/
create or replace
package body emp_reporter
is
procedure show_all_emps
is
begin
dbms_output.put_line('All EMPS as seen by user '||USER);
for employee in (select * from emp) loop
dbms_output.put_line(employee.ename||' ('||employee.deptno||')');
end loop;
end show_all_emps;
end emp_reporter;
/
I grant execute on this package to user UP. Now when I invoke the show_all_emps procedure as SCOTT, both the Invoker or Current User and the Definer or Owner are the same: SCOTT. Naturally I will see employees from departments 20 and 30:
SQL> exec emp_reporter.show_all_emps All EMPS as seen by user SCOTT FORD (20) ADAMS (20) SCOTT (20) JONES (20) SMITH (20) JAMES (30) TURNER (30) BLAKE (30) MARTIN (30) WARD (30) ALLEN (30) PL/SQL procedure successfully completed.
Now when I connect as user UP, the situation is different:
So, apparently, the Invoker’s Policy Predicate is what gets applied, not the Definer! Even though in the Definer Right’s scheme a user can select from a table he has no privileges on, thanks to the fact that the query is done under the privileges of the owner of the package, the VPD policy that is applied is still the one set up for the invoker.
In hindsight, I probably should have known what would happen. I make a direct reference to USER in my policy. I could have referred to SYS_CONTEXT ( ‘USERENV’, ‘CURRENT_USER’ ) in my policy function instead. That would have resulted in picking a policy based on the Owner or Definer of the package from which the query is executed. I am not sure what I would prefer – and in which circumstances.
Related posts:
Oracle Quiz on SQL and PL/SQL – See Water Burning – paper for ODTUG 2005 Poor man's VPD – Virtual Private Database before 8i and in Standard Edition Databases Oracle 10gR2 – Conditional PL/SQL Compilation 
Dynamic SQL Pivoting – Stealing Anton’s Thunder Putting Analytical Functions to good use – find tables with multiple foreign keys to the same referenced table
