data masking - liamlamth/blog GitHub Wiki

• no effect to users who is granted exempt access policy or sys
• require right: grant execute on dbms_rls to policy_owner;
• usage

list policy:           select * from dba_policies where object_owner <> 'XDB'

add policy:            begin 
                            dbms_rls.add_policy   ('object_owner','object_name','policy_name','function_owner', 'function_name','select');
                       end;
                       /
enable/disable policy: begin 
                            dbms_rls.enable_policy('object_owner','object_name','policy_name',false);
                       end;
                       /
drop policy:           begin 
                            dbms_rls.drop_policy  ('object_owner','object_name','policy_name');
                       end;
                       /

• function template

create or replace function f_vpd_filtering_empdata_1(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 = 'JUNIOR_USER' ) then
        v_predicate := 'type in (''INSENSENTIVE'',''MISC'')';           ### same as: select * from tbl_empinfo where type in ('INSENSENTIVE','MISC')
    else
        v_predicate := null;
    end if;
    return v_predicate;
    
    exception
    when others then
        v_predicate := null;
        return v_predicate;
end;
/

• usage

list policy:           select * from redaction_columns;

                                                                                              policy desc     column desc             function parameters
                                                                                              ↓               ↓                       ↓
add policy:            begin 
                            dbms_redact.add_policy('object_owner','object_name','policy_name',null,'col_name',null,dbms_redact.random,null,'sys_context(''userenv'',''session_user'') = ''JUNIOR_USER''');
                       end;
                       /
drop policy:           begin 
                            dbms_redact.drop_policy('object_owner', 'object_name','policy_name');
                       end;
                       /