Masking Sensitive data (using datapump in 11G)

One of the several requirements in today’s regulated environments is to mask sensitive data such as credit card numbers, and so on, when moving production data to test systems. This is a hard requirement to satisfy, especially if you have a large database. It’s about time Oracle came up with a facility to support it.

Oracle Data Pump now offers the ability to obfuscate data during an export or import operation. This functionality is supported with the use of the new remap_data parameter. With the remap_data parameter you define the schema table.column object(s) to be remapped, and you also define an associated function that
will be called “remap” (or obfuscate) the column data.

rajesh@ORA11G> create or replace package mypkg is
  2     function foo(p_x in varchar2)
  3     return varchar2;
  4  end;
  5  /

Package created.
Elapsed: 00:00:00.20
rajesh@ORA11G>
rajesh@ORA11G> create or replace package body mypkg is
  2     function foo(p_x in varchar2)
  3     return varchar2
  4     as
  5     begin
  6             return( translate(p_x,
  7             'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
  8             'bcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZa') );
  9     end;
 10  end;
 11  /
Package body created.


rajesh@ORA11G> create table t as
  2  select username x
  3  from all_users
  4  where rownum <= 5;

Table created.
Elapsed: 00:00:00.14
rajesh@ORA11G> select * from t;

X
------------------------------
SYS
SYSTEM
OUTLN
DIP
ORACLE_OCM

5 rows selected.


Now exporting the table T using expdp (Export datapump) utility

D:>expdp rajesh/oracle@ora11g tables=RAJESH.T directory=ETL_DATA_DIR dumpfile=t.dmp logfile=log.txt remap_data=RAJESH.T.X:RAJESH.MYPKG.FOO

Export: Release 11.2.0.1.0 - Production on Wed Jul 3 17:59:09 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "RAJESH"."SYS_EXPORT_TABLE_01":  rajesh/********@ora11g tables=RAJESH.T directory=ETL_DATA_DIR dumpfile=t.dmp logfile=log.txt remap_data=RAJESH.T.X:RAJESH.MYPKG.FOO
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "RAJESH"."T"                                5.054 KB       5 rows
Master table "RAJESH"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for RAJESH.SYS_EXPORT_TABLE_01 is:
  D:\APP\ETL_DATA\T.DMP
Job "RAJESH"."SYS_EXPORT_TABLE_01" successfully completed at 17:59:36


Now importing the data from this dumpfile to database by invoking data masking plsql routine on the column having sensitive data.

D:>impdp scott/tiger@ora11g directory=ETL_DATA_DIR dumpfile=t.dmp logfile=implog.txt remap_schema=rajesh:scott

Import: Release 11.2.0.1.0 - Production on Wed Jul 3 17:59:47 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/********@ora11g directory=ETL_DATA_DIR dumpfile=t.dmp logfile=implog.txt remap_schema=rajesh:scott
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."T"                                 5.054 KB       5 rows
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at 17:59:51


scott@ORA11G> select * from t;

X
------------------------------
TZT
TZTUFN
PVUMO
EJQ
PSBDMF_PDN
5 rows selected.


Now the sensitive data is masked using  this new remap_data parameter available in Oracle 11g Datapump utility.