Oracle implements simple data desensitization through expdp remap_data

Oracle implements simple data desensitization through expdp remap_data

Starting from Oracle 11g, The remap_data parameter is provided in impdp/expdp to convert data during import or export operations. if the production database data needs to be exported to the test database and converted to sensitive data, you can use the remap_data parameter.

The following is a simple single representation example:
Oracle version: 11.2.0.4

[Oracle @ ct6605 ~] $ ORACLE_SID = ct66
# Create a test table
SQL> create table scott. t_expdp as select * from dba_objects;
# Create an import/export directory
SQL> create or replace directory home_dump as '/home/oracle ';
# Create a function package for Data Conversion
# The logic and complexity of the conversion can be determined based on requirements.
SQL> create or replace package scott. pkg_remap
Is
# Converting the number type
Function f_remap_number (p_number number) return number;
# Convert the varchar type
Function f_remap_varchar (p_varchar varchar2) return varchar2;
End;
/

SQL> create or replace package body scott. pkg_remap
2 is
Function f_remap_number (p_number number) return number
3 4 begin
5 return floor (dbms_random.value (1, 100000 ));
6 end;
Function f_remap_varchar (p_varchar varchar2) return varchar2
7 8 begin
9 return dbms_random.string ('A', 10 );
10 end;
11 end;
12/

SQL> exit

# Convert data when the remap_data parameter is used for expdp Export
# The remap_data format is a conversion function corresponding to a field to be converted.
# Reuse_dumpfiles is also a parameter after 11 GB to determine whether to overwrite the exported file with the same name
[Oracle @ ct6605 ~] $ Expdp system dumpfile = home_dump: remap_t_expdp.dmp tables = scott. t_expdp reuse_dumpfiles = y remap_data = scott. t_expdp.object_id: scott. example, scott. t_expdp.object_name: scott. Example

Export: Release 11.2.0.4.0-Production on Fri Mar 25 11:02:51 2016

Copyright (c) 1982,201 1, Oracle and/or its affiliates. All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM ". "SYS_EXPORT_TABLE_01": system/******** dumpfile = home_dump: remap_t_expdp.dmp tables = scott. t_expdp reuse_dumpfiles = y remap_data = scott. t_expdp.object_id: scott. pkg_remap.f_remap_number, scott. t_expdp.object_name: scott. pkg_remap.f_remap_varchar
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 10 MB
Processing object type TABLE_EXPORT/TABLE
.. Exported "SCOTT". "T_EXPDP" 7.257 MB 86526 rows
Master table "SYSTEM". "SYS_EXPORT_TABLE_01" successfully loaded/unloaded
**************************************** **************************************
Dump file set for SYSTEM. SYS_EXPORT_TABLE_01 is:
/Home/oracle/remap_t_expdp.dmp
Job "SYSTEM". "SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 25 11:03:08 2016 elapsed 0 00:00:13

# Use impdp to export the converted table. Due to the test, the table is imported to the same database and user.
# Remap_table is also a parameter after 11 GB, used to remap the table
[Oracle @ ct6605 ~] $ Impdp system dumpfile = home_dump: remap_t_expdp.dmp remap_table = scott. t_expdp: t_impdp

Import: Release 11.2.0.4.0-Production on Fri Mar 25 11:09:20 2016

Copyright (c) 1982,201 1, Oracle and/or its affiliates. All rights reserved.
Password: UDI-00001: user requested cancel of current operation

[Oracle @ ct6605 ~] $ Impdp system dumpfile = home_dump: remap_t_expdp.dmp remap_table = scott. t_expdp: t_impdp

Import: Release 11.2.0.4.0-Production on Fri Mar 25 11:11:23 2016

Copyright (c) 1982,201 1, Oracle and/or its affiliates. All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM". "SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM". "SYS_IMPORT_FULL_01": system/******** dumpfile = home_dump: remap_t_expdp.dmp remap_table = scott. t_expdp: t_impdp
Processing object type TABLE_EXPORT/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.. Imported "SCOTT". "T_IMPDP" 7.257 MB 86526 rows
Job "SYSTEM". "SYS_IMPORT_FULL_01" successfully completed at Fri Mar 25 11:11:28 2016 elapsed 0 00:00:02

[Oracle @ ct6605 ~] $ Sqlplus/as sysdba
# Check whether the imported data has been converted
SQL> select object_name, object_id from scott. t_impdp where rownum <10;
/*
OBJECT_NAME OBJECT_ID
Swecninjyb34242
AxIpkMKaJw 96259
Dpbwmpghyoyo 80463
EXcEWFyDvL 46759
UZJIPkYruN 23656
USR suplxng 78736
Cbevip flhr 44909
Nyqhlhbv Ki 16672
Vpwtidm jkn 50456
*/