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.