vpd policy (filtering by condition) - liamlamth/blog GitHub Wiki

select * from dba_policies;

• allow multiple policies applied to one object (where policy 1 and policy 2)

begin
  dbms_rls.add_policy('EFFECT_OBJ_OWNER', 'EFFECT_OBJ', 'POLICYNAME', 'FUNCTION_OWNER', 'FUNCTION_NAME', 'select');
end;
/

dbms_rls.drop_policy('EFFECT_OBJ_OWNER', 'EFFECT_OBJ', 'POLICY_NAME'); 

dbms_rls.enable_policy('EFFECT_OBJ_OWNER', 'EFFECT_OBJ', 'POLICY_NAME', true);

• to change filtering logic, only need to amend function

create or replace function FUNCTION_NAME (owner varchar2, objname varchar2)
return varchar2 is
    v_predicate varchar2(4000);
    v_session_user varchar2(100);
begin
    select user into v_session_user from dual;
    if v_session_user like 'LOW_USER%' then
        v_predicate := 'tblcol_RANK = ''LOW''';
    else
        v_predicate := null;
    end if;
    return v_predicate;
end;
/