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.
