Database Reference
In-Depth Information
Remapping Data
Starting with Oracle 11g, when either exporting or importing, you can apply a PL/SQL function to alter a column
value. For example, you may have an auditor who needs to look at the data, and one requirement is that you apply a
simple obfuscation function to sensitive columns. The data don't need to be encrypted; they just need to be changed
enough that the auditor can't readily determine the value of the
LAST_NAME
column in the
CUSTOMERS
table.
This example first creates a simple package that is used to obfuscate the data:
create or replace package obfus is
function obf(clear_string varchar2) return varchar2;
function unobf(obs_string varchar2) return varchar2;
end obfus;
/
--
create or replace package body obfus is
fromstr varchar2(62) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' ||
'abcdefghijklmnopqrstuvwxyz';
tostr varchar2(62) := 'defghijklmnopqrstuvwxyzabc3456789012' ||
'KLMNOPQRSTUVWXYZABCDEFGHIJ';
--
function obf(clear_string varchar2) return varchar2 is
begin
return translate(clear_string, fromstr, tostr);
end obf;
--
function unobf(obs_string varchar2) return varchar2 is
begin
return translate(obs_string, tostr, fromstr);
end unobf;
end obfus;
/
Now, when you import the data into the database, you apply the obfuscation function to the
LAST_NAME
column
of the
CUSTOMERS
table:
$ impdp mv_maint/foo directory=dp_dir dumpfile=cust.dmp tables=customers \
remap_data=customers.last_name:obfus.obf
Selecting
LAST_NAME
from
CUSTOMERS
shows that it has been imported in an obfuscated manner:
SQL> select last_name from customers;
LAST_NAME
------------------
yYZEJ
tOXXSMU
xERX
