Wednesday, February 20, 2013

How to SCRUB/MASK data using expdp (REMAP_DATA feature)


This is a common requirement for DBA to export data from production for various purposes. In a restricted & compliant environment it’s a must to mask / scrub particular data while exporting from production.
Here’s a simple demonstration. I’ve got the hint from metalink.

  1. Create a package to mask data

Note: The package needs to be created under the schema which would be used to connect to datapump utility. I’ve used SYSTEM user for this case.

as
  function toggle_case(p_value varchar2) return varchar2;
end;
/

create or replace package body datapump_remap_test
as
  function toggle_case(p_value varchar2) return varchar2 is
  begin
    return translate(p_value,    
      'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890!@#$%^&*()_+-=\/ ',
        'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
end;
end;
/

  2. Datapump export with REMAP_DATA parameter:

We’d be using the created package to scrub data for particular table columns. My package would basically replace all mentioned column data into ‘xxxxx’
For this example, I’ve chosen to scrub HR.FIRST_NAME & SALES. The export dump would export the FNADVI schema as well as the the table mentioned with scrubbed column.

expdp system dumpfile=fnadvi.dmp directory=dump logfile=fnadvi.dmp.log schemas=fnadvi remap_data=fnadvi.HR.first_name:system.datapump_remap_test.toggle_case remap_data=fnadvi.SALES.address:system.datapump_remap_test.toggle_case

  3. Import the schema
Now you the import can be done anywhere with scrubbed data.

No comments: