oracle11g掩盖敏感数据(remap_data)

不知道你有没有遇到过开发的需求导一份生产库的数据到测试库，但库表中有敏感信息如手机、身份证号等等等等，如果你全部把真实的数据给他们是不是太不负责人了(当然很多都这么做了),少导列可能会影响测试准确性,最好是用其它掩码覆盖，11g以前做有点麻烦，但刚发现11G提供了这个特性，导出时用一个方法替换指定字段。

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 8月 16 15:38:36 2011
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
sys@ANBOB> conn anbob/anbob
Connected.
anbob@ANBOB> desc testexpdp;
 Name                                             Null?    Type
 --------------------------------------  --------------------------------------------------------
 ID                                              NUMBER(38)
 NAME                                            VARCHAR2(20)
 SEX                                             NUMBER(1)
 IDCARD                                          VARCHAR2(18)
anbob@ANBOB> select * from testexpdp;
        ID NAME                        SEX IDCARD
---------- -------------------- ---------- ------------------
         1 anbob                         1 13053319000101122x
         1 weejar                        1 11053319000101122x
anbob@ANBOB> create package pck_mask
  2  as
  3  function mask_idcard(p_in varchar2) return varchar2;
  4  end;
  5  /
Package created.
anbob@ANBOB> l
  1  create or replace package body pck_mask
  2  as
  3  function mask_idcard(p_in varchar2)
  4  return varchar2
  5  is
  6  begin
  7  return dbms_random.string('l',18);
  8  end;
  9* end;
anbob@ANBOB> /
Package body created.
anbob@ANBOB> col directory_path for a40
anbob@ANBOB> select directory_name,directory_path from all_directories;
DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ----------------------------------------
EXPDIR                         /backup
XMLDIR                         /oracle11g/db_1/rdbms/xml
DATA_PUMP_DIR                  /oracle11g/admin/anbob/dpdump/
ORACLE_OCM_CONFIG_DIR          /oracle11g/db_1/ccr/state
anbob@ANBOB> !
[oracle@orazhang oracle11g]$ expdp anbob/anbob tables=testexpdp dumpfile=test.dump directory=expdir remap_data=TESTEXPDP.IDCARD:pck_mask.mask_idcard      
Export: Release 11.2.0.1.0 - Production on 星期二 8月 16 15:56:23 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
启动 "ANBOB"."SYS_EXPORT_TABLE_01":  anbob/******** tables=testexpdp dumpfile=test.dump directory=expdir remap_data=TESTEXPDP.IDCARD:pck_mask.mask_idcard
正在使用 BLOCKS 方法进行估计...
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 64 KB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
. . 导出了 "ANBOB"."TESTEXPDP"                         6.320 KB       2 行
已成功加载/卸载了主表 "ANBOB"."SYS_EXPORT_TABLE_01"
******************************************************************************
ANBOB.SYS_EXPORT_TABLE_01 的转储文件集为:
  /backup/test.dump
作业 "ANBOB"."SYS_EXPORT_TABLE_01" 已于 15:56:34 成功完成
[oracle@orazhang oracle11g]$ impdp system/oracle directory=expdir dumpfile=test.dump remap_schema=anbob:weejar
Import: Release 11.2.0.1.0 - Production on 星期二 8月 16 15:57:59 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "SYSTEM"."SYS_IMPORT_FULL_01"
启动 "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=expdir dumpfile=test.dump remap_schema=anbob:weejar
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
. . 导入了 "WEEJAR"."TESTEXPDP"                        6.320 KB       2 行
作业 "SYSTEM"."SYS_IMPORT_FULL_01" 已于 15:58:02 成功完成
[oracle@orazhang oracle11g]$ exit
exit
anbob@ANBOB> conn weejar/weejar
Connected.
weejar@ANBOB> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TESTEXPDP                      TABLE
weejar@ANBOB> select * from testexpdp;
        ID NAME                        SEX IDCARD
---------- -------------------- ---------- ------------------
         1 anbob                         1 fcxfinxadnngrurpoo
         1 weejar                        1 xozutyaesyfzphuthh
复制