Masking data when exporting with datapump

We've had to mask some data before sending on a database copy to one of our vendors - this was previously done by copying the hwol system to another database - masking the data and then encrypting it. The new method is as follows:

Stage1: (create masking package)
(package code pinched from here http://psoug.org/reference/datapump.html )

create or replace package endurf_env05.DATAPUMP_ENCRYPT
is
  function mask (inval varchar2) return varchar2 ;
  end DATAPUMP_ENCRYPT;

create or replace package body endurf_env05.DATAPUMP_ENCRYPT
as
  function mask (inval varchar2) return varchar2 is
        begin
      return sys_guid;
      end mask;
  end DATAPUMP_ENCRYPT;

Stage 2 - specify which columns to encrypt on export

expdp / parfile=xxx.par

where xxx.par contains

FLASHBACK_TIME="TO_TIMESTAMP('11-11-2011 13:00:00', 'DD-MM-YYYY HH24:MI:SS')"
schemas=ENDURF_ENV05
directory=DATA_PUMP_DIR
dumpfile=encypt.dmp
remap_data=endurf_env05.account.account_name:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.account.description:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.account_history.account_name:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.account_history.description:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.party.short_name:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.party.long_name:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.party_history.short_name:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.party_history.long_name:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.party_group.short_name:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.party_group.description:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.party_group_history.short_name:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.party_group_history.description:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.business_unit.addr1:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.business_unit.addr2:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.business_unit.city:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.business_unit.mail_code:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.business_unit.phone:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.business_unit.fax:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.business_unit.description:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.business_unit.bic_code:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.business_unit_history.addr1:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.business_unit_history.addr2:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.business_unit_history.city:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.business_unit_history.mail_code:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.business_unit_history.phone:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.business_unit_history.fax:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.business_unit_history.description:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.business_unit_history.bic_code:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.legal_entity.addr1:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.legal_entity.addr2:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.legal_entity.city:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.legal_entity.mail_code:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.legal_entity.phone:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.legal_entity.fax:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.legal_entity.description:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.legal_entity_history.addr1:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.legal_entity_history.addr2:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.legal_entity_history.city:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.legal_entity_history.mail_code:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.legal_entity_history.phone:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.legal_entity_history.fax:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.legal_entity_history.description:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.settle_instructions.settle_name:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.settle_instructions.description:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.settle_instructions_history.settle_name:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.settle_instructions_history.description:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.personnel.first_name:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.personnel.last_name:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.personnel.addr1:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.personnel.addr2:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.personnel.name:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.personnel.email:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.personnel.city:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.personnel.mail_code:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.personnel.phone:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.personnel.fax:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.personnel_history.first_name:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.personnel_history.last_name:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.personnel_history.addr1:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.personnel_history.addr2:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.personnel_history.name:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.personnel_history.email:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.personnel_history.city:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.personnel_history.mail_code:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.personnel_history.phone:endurf_env05.datapump_encrypt.mask
remap_data=endurf_env05.personnel_history.fax:endurf_env05.datapump_encrypt.mask

If you have more than 10 columns from a singlt table to encrypt at 11.1 there is a bug and it failes.... - fixed in 11.2. In most cases though 10 cols is probably enough anyway

Cheers,
Rich

0 comments:

Post a Comment