DATA PUMP DATA REMAPPING (OBFUSCATION)

posted Jan 29, 2012, 4:08 PM by Sachchida Ojha   [ updated Jan 29, 2012, 7:02 PM ]
One of the several requirements in today's regulated environments is to mask sensitive data such as credit card numbers, Social Security numbers etc (also called NPI data) when moving data from production to lower environments. This is a hard requirement to satisfy, especially if you have a large database. Oracle 11g support this functionality using  DATA PUMP DATA REMAPPING.
Oracle Data Pump now offers the ability to OBFUSCATE DATA during an export or import operation. This functionality is now supported using new remap_data parameter in expdp and impdp.

With the remap_data parameter you define the schema table.column objects(s) to be remapped, and you also define an associated function that will be called "remap" (or OBFUSCATE) the column data.

Oracle supports multiple remappings.  Each remapping can use the same or a different remapping function.



Example:

SQL> Create or replace package my_package
as
function my_function (p_in_data varchar2)
return varchar2;
end;


Package created.

SQL> Create or replace package body my_package
as
function my_function (p_in_data varchar2)
return varchar2
as
v_return  varchar2(30);
begin
v_return :=translate(p_in_data, '123451234','xxxxxxxxx');
dbms_output.put_line(v_return);
return v_return;
end;
end;

Package body created.

SQL>

Now you lets do the export as

$expdp dbaref/dbaref DIRECTORY=data_pump_dir DUMPFILE=remap.dmp tables=dbaref.emp remap_data=dbaref.emp.ssn:dbaref.my_package.my_function

Lets import the data and see what it looks like

$impdp dbaref/dbaref DIRECTORY=data_pump_dir DUMPFILE=remap.dmp tables=dbaref.emp remap_table=dbaref.emp:emp_test

we can do the remap_data during the impdp also

$impdp dbaref/dbaref DIRECTORY=data_pump_dir DUMPFILE=remap.dmp tables=dbaref.emp remap_table=dbaref.emp:emp_test remap_data=dbaref.emp_test.ssn:dbaref.my_package.my_function
















Buy.com
Comments