Unified Audit and Virtual Private Database

It seems not a day goes by where I’m not humbled in some way or another. Frequently this happens due to limits of my knowledge or something I’d forgotten. There is a wrinkle when you combine Unified Auditing and Virtual Private Databases (VPD).

Here’s the setup.

create or replace package HR.hr_vpd_policy as
function hr_emp_vpd_policy (p_schema varchar2,
                            p_table  varchar2)
return varchar2;
end;
/
-- keep it real simple. just return 
-- department_id = 10.
create or replace package body HR.hr_vpd_policy as 
function hr_emp_vpd_policy (p_schema varchar2,
                            p_table  varchar2)
return varchar2 is
p_ret_val varchar2(255);
    begin 
      p_ret_val := 'department_id = 10';
      return p_ret_val;
    end;
end;
/
begin 
  sys.dbms_rls.add_policy (
    object_schema   => 'HR',
	object_name     => 'EMPLOYEES_VIEW',
	policy_name     => 'HR_EMP_SEL_POL',
	function_schema => 'HR',
	policy_function => 'hr_vpd_policy.hr_emp_vpd_policy',
	statement_types	=> 'SELECT'
  );
end;
/

Here’s the question. How do I know the where clause VPD placed on a sql statement? Well it’s not in the sql_text column (like I thought is was), it is in the rls_info column. Remember, VPD was called “Row Level Security.” Oracle changed it’s name sometime back, I think 10g, but don’t quote me on the version Oracle changed the name.