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
*/
以下是簡單的單表示例:
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
*/
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28539951/viewspace-2063896/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle通過expdp的remap_data實現簡單的資料脫敏OracleREM
- 通過Vue的過濾器實現資料的資料脫敏Vue過濾器
- mysql簡單脫敏MySql
- java怎麼實現資料脫敏Java
- 資料脫敏
- ETL的資料脫敏方式
- 動態脫敏典型應用場景分析——業務脫敏、運維脫敏、資料交換脫敏運維
- 資料脫敏 t-closeness介紹與實現
- MyBatis攔截器優雅實現資料脫敏MyBatis
- SpringBoot實戰:輕鬆實現介面資料脫敏Spring Boot
- 利用Jackson序列化實現資料脫敏
- 工作常備:自定義註解實現資料脫敏
- 透過vmstat的簡單分析資料庫操作資料庫
- 利用otter做mysql資料實時脫敏MySql
- 資料庫動態脫敏資料庫
- 資料庫靜態脫敏資料庫
- 透過Oracle的duplicate database to 【newsid】實現資料庫克隆;OracleDatabase資料庫
- Nginx透過https方式反向代理的簡單實現NginxHTTP
- 聊聊如何自定義資料脫敏
- 透過資料結構實現簡易通訊錄資料結構
- 資料脫敏大資料架構設計大資料架構
- 資料庫的最簡單實現資料庫
- SNP乾貨分享:SAP資料脫敏的具體實施步驟
- 透過API介面實現資料探勘?API
- java 日誌脫敏框架 sensitive,優雅的列印脫敏日誌Java框架
- oracle資料庫的impdp,expdpOracle資料庫
- 構建並實現資料抽取、轉換、清洗、脫敏、載入等功能。
- Laravel 資料庫脫敏工具,僅支援 MySQLLaravel資料庫MySql
- openGauss 動態資料脫敏機制
- 自定義註解進行資料脫敏
- 資料泵datapump(expdp/impdp)的parfile用法,避免跳脫字元字元
- SQL Server資料庫的簡單實現方法SQLServer資料庫
- 《資料安全能力成熟度模型》實踐指南10:資料脫敏模型
- ORACLE PL/SQL 物件、表資料對比功能儲存過程簡單實現OracleSQL物件儲存過程
- 案例解析|“脫敏+加密”保障安徽高速資料安全加密
- 數倉安全:資料脫敏技術深度解析
- openGauss-動態資料脫敏機制
- 實踐 | 大型基金管理公司資料脫敏體系建設