Data Masking with Oracle Data Pump

If you expose production data to test, QA or UAT environment, most probably you’ll need to hide sensitive data. You can do this in different ways. One of them is to
use Oracle Data Pump to mask the data. You may choose to do that on the very export step and then on the import step, or just mask the sensitive data when you do the
import in the target schema(s).

Masking should protect your data but not stop the testing process. Ensure you do not lose realistic lookup data for the testing. Mask only whatt you need to mask.
Hide only sensitive data. If you can expose primary and foreign key columns then don’t mask them. If not ensure you keep the integrity of the relations. For example
when you mask the primary key column, you should use the same mask for the foreign key column.

Oracle Data Pump provides a way to achieve this. Oracle Data Pump’s REMAP_DATA parameter is introduced in Oracle Database 11g. It uses an user defined remapping
function to rewrite data. If you want to mask multiple columns in the same process, REMAP_DATA parameter is used several times. I choose to mask only ADDRESS column
in my table. If you have a dump file you can choose to mask the data only when doing the import. I will show this below:

Syntax

REMAP_DATA=[(schema.tablename.column_name:schema.pkg.function)]

impdp user/pass
TABLES=SCHEMA_NAME.TABLE_NAME
TABLE_EXISTS_ACTION=replace
DUMPFILE=EXP.DPUMP
DIRECTORY=IMPORT REMAP_TABLESPACE=(SOURCE_TABLESPACE:TARGET_TABLESPACE)  REMAP_DATA=SCHEMA_NAME.TABLE_NAME.ADDRESS:SCHEMA_NAME.REMAP_UTILS.MASKVARCHAR

This is my remapping function :

CREATE OR REPLACE PACKAGE REMAP_UTILS AS

FUNCTION maskvarchar(ADDRESS VARCHAR2) return VARCHAR2;

END;
/

CREATE OR REPLACE PACKAGE BODY REMAP_UTILS AS

FUNCTION maskvarchar(ADDRESS VARCHAR2) return VARCHAR2
IS
v_string VARCHAR2(120 BYTE) := ”;
BEGIN

v_string := dbms_random.string(‘A’, 10);
RETURN v_string;
END;

END  REMAP_UTILS ;
/

Let’s see the result after the import.

SQL> conn / as sysdba
Connected.
SQL> Spool on
SQL> Spool c:\spooltext.txt
SQL> select ADDRESS from TABLE_NAME
2  where rownum <=10;

ADDRESS
————————————

htkphShybP
bNBNwCIsdY
PhfDFAnZbO
wrTyPtxPjC
spfuDBPhRJ
JUIrqmuXPJ
hequOqHydf
EAiITTjvkX
JwujGneNVe
CeuZwGgmsh

10 rows selected.

SQL> Spool off
SQL>

The address column is masked successfully. As a conclusion, I would say it is a great feature.

Cheers,
Maria