Oracle通過expdp的remap_data實現簡單的資料脫敏

Oracle通過expdp的remap_data實現簡單的資料脫敏

Oracle 11g開始,在impdp/expdp中提供remap_data參數,實現對匯入或匯出操作時轉換資料.如果生產庫資料要匯出給測試庫使用,並需要轉換敏感性資料,可以考慮通過remap_data參數去實現.

以下是簡單的單表示例:
oracle version:11.2.0.4

[oracle@ct6605 ~]$ ORACLE_SID=ct66
#建測試表
SQL> create table scott.t_expdp as select * from dba_objects;
#建匯入匯出目錄
SQL> create or replace directory home_dump as '/home/oracle';
#建用於轉換資料的函數包
#轉換的邏輯和複雜度可根據需求確定
SQL> create or replace package scott.pkg_remap
is
#轉換number類型
function f_remap_number(p_number number) return number;
#轉換varchar類型
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 as
  3    4  begin
  5    return floor(dbms_random.value(1, 100000));
  6  end;
function f_remap_varchar(p_varchar varchar2) return varchar2 as
  7    8  begin
  9    return dbms_random.string('A',10);
 10  end;
 11  end;
 12  /

SQL> exit

#使用expdp時remap_data參數匯出時轉換資料
#remap_data格式是要轉換的一個欄位對應一個轉換函式
#reuse_dumpfiles也是11g後的參數,用以確定是否覆蓋同名匯出檔案
[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.pkg_remap.f_remap_number,scott.t_expdp.object_name:scott.pkg_remap.f_remap_varchar

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

Copyright (c) 1982, 2011, 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/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

#使用impdp匯出轉換後的表,這裡由於測試,所以匯入到同庫同使用者下
#remap_table也是11g後的參數,用以重新對應表
[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, 2011, 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, 2011, 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/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
#查看匯入的資料是否經過轉換
SQL> select object_name,object_id from scott.t_impdp where rownum<10;
/*
OBJECT_NAME    OBJECT_ID
swecninjYb    34242
axIpkMKaJw    96259
DpBWmPGhyo    80463
eXcEWFyDvL    46759
uZJIPkYruN    23656
uSRsuPlXNG    78736
CBeviPFlhr    44909
NyQHLHBvKi    16672
vPWTIdMJkN    50456
*/