Another Pop-Quiz: Whose VPD policy is used when executing SQL in a (definer rights) package?

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.