Home > DBA > Data Masking with Oracle Data Pump

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

About these ads
Categories: DBA
  1. September 3, 2011 at 1:11 pm | #1

    It is indeed greate feature. Easy way to avoid Data Masking pack license fees or having developers create and update scripts. I got an interesting question from a client 2 weeks ago: how to additionally ensure that histograms on the column(s) are preserved on the masked data (so that performance, execution plans will not be affected)?

    • September 23, 2011 at 7:31 pm | #2

      I’ve come across that question (preserving statistics under data masking) but not any putative solution. Is there one?

  2. September 24, 2011 at 11:24 am | #3

    How about these 3 options:

    1. Lock the statistics on the table and then mask the data.
    2. Export the statistics, mask, import them.
    3. Use SET_COLUMN_STATS to manually set thecolumn stats to the original values (get them with GET_COLUMN_STATS)

    1 and 2 are in general same..

  3. sshdba
    September 25, 2012 at 9:43 pm | #4

    Reblogged this on Easy Oracle DBA.

  4. WilhelmZ
    December 18, 2012 at 11:58 am | #5

    Great! Just what I needed . . .

  1. March 16, 2012 at 2:30 am | #1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: