Data masking with dbms_redact

dbms_redact basicly is a 12c package which was backported to 11.2.0.4. This package allow us create very customizable masking procedures. For simplicity, let say you need to mask credit card numbers, cutting off the middle 6 digits in 16-digital cards for some user i_see only


Create test env

 SQL> create user i_see identified by "***";  
 User created.  
 SQL> grant create session,create table to i_see;  
 Grant succeeded.  
 SQL> grant execute on dbms_random to i_see;  
 Grant succeeded.  
 SQL> conn i_see/***  
 Connected.  
 SQL> set num 20  
 SQL> create table mask_tab (card_num varchar2(20));  
 Table created.  
 SQL> insert into mask_tab  
 select (round(dbms_random.value(1000000000000000, 9999999999999999)))   
 from dual connect by level <= 10;  
 10 rows created.  
 SQL> commit;  
 Commit complete.  
 SQL> select * from mask_tab;  
 CARD_NUM  
 --------------------  
 4315180567707642  
 8343744789583160  
 7219646392845285  
 1281700031661329  
 6939207428729261  
 2520681905985755  
 7344297730838169  
 9104025795743973  
 4902051003122352  
 2026891028511588  
 10 rows selected.  

Lets create redact policy for user i_see, table mask_tab and column card_num

 [testhost:oracle@testdb]$ sqlplus "/as sysdba"  
 SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 13 12:31:44 2015  
 Copyright (c) 1982, 2013, Oracle. All rights reserved.  
 Connected to:  
 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production  
 With the Partitioning, OLAP, Data Mining and Real Application Testing options  
 SQL> begin  
  dbms_redact.add_policy(object_schema     => 'I_SEE',  
              object_name      => 'MASK_TAB',  
              column_name      => 'CARD_NUM',  
              policy_name      => 'CARD_NUM_POLICY',  
              function_type     => DBMS_REDACT.REGEXP,  
              expression      => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'')=''I_SEE''',  
              regexp_pattern    => '(\d{6})\d{6}(\d+)',  
              regexp_replace_string => '\1******\2');  
 end;  
  11 /  
 PL/SQL procedure successfully completed.  
 SQL> select user from dual;  
 USER  
 ------------------------------  
 SYS  
 SQL> select * from i_see.mask_tab;  
 CARD_NUM  
 --------------------------------------------------------------------------------  
 4315180567707642  
 8343744789583160  
 7219646392845285  
 1281700031661329  
 6939207428729261  
 2520681905985755  
 7344297730838169  
 9104025795743973  
 4902051003122352  
 2026891028511588  
 10 rows selected.  
 SQL> conn i_see/***  
 Connected.  
 SQL> select * from mask_tab;  
 CARD_NUM  
 --------------------------------------------------------------------------------  
 431518******7642  
 834374******3160  
 721964******5285  
 128170******1329  
 693920******9261  
 252068******5755  
 734429******8169  
 910402******3973  
 490205******2352  
 202689******1588  
 10 rows selected.  
 SQL> exit  
 Then drop policy   
 [testhost:oracle@testdb]$ sqlplus "/as sysdba"  
 SQL> begin  
     dbms_redact.drop_policy(  
      object_schema => 'I_SEE',  
      object_name  => 'MASK_TAB',  
      policy_name  => 'CARD_NUM_POLICY');  
  end;  
  7 /  
 PL/SQL procedure successfully completed.  
 SQL> conn i_see/***  
 Connected.  
 SQL> select * from mask_tab;  
 CARD_NUM  
 --------------------  
 4315180567707642  
 8343744789583160  
 7219646392845285  
 1281700031661329  
 6939207428729261  
 2520681905985755  
 7344297730838169  
 9104025795743973  
 4902051003122352  
 2026891028511588  
 10 rows selected.  

Also, keep in mind if user has  EXEMPT REDACTION POLICY  privilege, then policies dont applies to user (DBA role has this priv)

Some examples of implementing Data Redaction using DBMS_REDACT (Doc ID 1588270.1)