从oracle 11.1.0.7开始oracle 支持data pump导出加密,从而实现dmp文件安全.不会因为dmp文件丢失而导致数据泄露.涉及数据泵加密参数主要有：ENCRYPTION,ENCRYPTION_ALGORITHM,ENCRYPTION_MODE,ENCRYPTION_PASSWORD几个参数.这里测试的是使用最简单方式实现datapump加密功能,如果需要更好的数据安全可以考虑实时密码钱包
ENCRYPTION
Encrypt part or all of a dump file.
Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.
ENCRYPTION为加密dmp文件加密部分，其参数值可以有ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE，
如果只有ENCRYPTION_PASSWORD指定值，那么ENCRYPTION默认值为ALL
如果ENCRYPTION_PASSWORD和ENCRYPTION均为指定，那么默认值为NONE
ENCRYPTION_ALGORITHM
Specify how encryption should be done.
Valid keyword values are: [AES128], AES192 and AES256.
ENCRYPTION_ALGORITHM是指加密算法，参数值可以有AES128, AES192 and AES256。默认值为AES128
ENCRYPTION_MODE
Method of generating encryption key.
Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].
ENCRYPTION_MODE指定加密方式，其参数值有DUAL, PASSWORD and TRANSPARENT。默认值为TRANSPARENT
DUAL表示你指定的加密的dmp文件在导入的时候可以通过密码方式或者加密钱包方式导入
PASSWORD表示指定密码方式创建dmp文件，你导入也需要提供密码
TRANSPARENT需要加密钱包方式导出和导入
ENCRYPTION_PASSWORD
Password key for creating encrypted data within a dump file.
ENCRYPTION_PASSWORD指定加密密码
创建测试表
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 11 14:52:32 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
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
SQL> conn chf/xifenfei
Connected.
SQL> create table xifenfei (id number,name varchar2(50));
Table created.
SQL> insert into xifenfei values(&id,'&name');
Enter value for id: 1
Enter value for name: xifenfei
old 1: insert into xifenfei values(&id,'&name')
new 1: insert into xifenfei values(1,'xifenfei')
1 row created.
SQL> /
Enter value for id: 2
Enter value for name: www.xifenfei.com
old 1: insert into xifenfei values(&id,'&name')
new 1: insert into xifenfei values(2,'www.xifenfei.com')
1 row created.
SQL> /
Enter value for id: 3
Enter value for name: www.orasos.com
old 1: insert into xifenfei values(&id,'&name')
new 1: insert into xifenfei values(3,'www.orasos.com')
1 row created.
SQL> commit;
Commit complete.
SQL> col name for a50
SQL> set lines 100
SQL> select * from xifenfei;
ID NAME
---------- --------------------------------------------------
1 xifenfei
2 www.xifenfei.com
3 www.orasos.com
创建目录
SQL> create directory dir_xff as '/tmp'; Directory created.
不加密导出
[oracle@localhost ~]$ expdp chf/xifenfei tables=xifenfei directory=dir_xff dumpfile=none.dmp logfile=none.log Export: Release 11.2.0.4.0 - Production on Wed Feb 11 15:29:13 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 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 "CHF"."SYS_EXPORT_TABLE_01": chf/******** tables=xifenfei directory=dir_xff dumpfile=none.dmp logfile=none.log Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "CHF"."XIFENFEI" 5.460 KB 3 rows Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for CHF.SYS_EXPORT_TABLE_01 is: /tmp/none.dmp Job "CHF"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 11 15:29:37 2015 elapsed 0 00:00:14
数据字典和数据都加密导出
[oracle@localhost ~]$ expdp chf/xifenfei tables=xifenfei directory=dir_xff ENCRYPTION=ALL dumpfile=ALL.dmp logfile=ALL.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=www.xifenfei.com Export: Release 11.2.0.4.0 - Production on Wed Feb 11 15:33:06 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 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 "CHF"."SYS_EXPORT_TABLE_01": chf/******** tables=xifenfei directory=dir_xff ENCRYPTION=ALL dumpfile=ALL.dmp logfile=ALL.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=******** Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "CHF"."XIFENFEI" 5.468 KB 3 rows Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for CHF.SYS_EXPORT_TABLE_01 is: /tmp/ALL.dmp Job "CHF"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 11 15:33:13 2015 elapsed 0 00:00:06
数据加密导出
[oracle@localhost ~]$ expdp chf/xifenfei tables=xifenfei directory=dir_xff ENCRYPTION=DATA_ONLY dumpfile=DATA_ONLY.dmp logfile=DATA_ONLY.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=www.xifenfei.com Export: Release 11.2.0.4.0 - Production on Wed Feb 11 15:33:52 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 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 "CHF"."SYS_EXPORT_TABLE_01": chf/******** tables=xifenfei directory=dir_xff ENCRYPTION=DATA_ONLY dumpfile=DATA_ONLY.dmp logfile=DATA_ONLY.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=******** Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "CHF"."XIFENFEI" 5.468 KB 3 rows Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for CHF.SYS_EXPORT_TABLE_01 is: /tmp/DATA_ONLY.dmp Job "CHF"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 11 15:33:57 2015 elapsed 0 00:00:04
数据字典加密导出
[oracle@localhost tmp]$ expdp chf/xifenfei tables=xifenfei directory=dir_xff ENCRYPTION=METADATA_ONLY dumpfile=METADATA_ONLY.dmp logfile=METADATA_ONLY.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=www.xifenfei.com Export: Release 11.2.0.4.0 - Production on Wed Feb 11 15:50:00 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 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 "CHF"."SYS_EXPORT_TABLE_01": chf/******** tables=xifenfei directory=dir_xff ENCRYPTION=METADATA_ONLY dumpfile=METADATA_ONLY.dmp logfile=METADATA_ONLY.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=******** Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "CHF"."XIFENFEI" 5.460 KB 3 rows Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for CHF.SYS_EXPORT_TABLE_01 is: /tmp/METADATA_ONLY.dmp Job "CHF"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 11 15:50:04 2015 elapsed 0 00:00:03
删除测试表
SQL> drop table xifenfei purge; Table dropped.
导入数据未输入密码
[oracle@localhost tmp]$ impdp chf/xifenfei tables=xifenfei directory=dir_xff dumpfile=METADATA_ONLY.dmp logfile=xifenfei.log Import: Release 11.2.0.4.0 - Production on Wed Feb 11 16:03:13 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 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 ORA-39002: invalid operation ORA-39174: Encryption password must be supplied.
导入数据密码错误
[oracle@localhost tmp]$ impdp chf/xifenfei tables=xifenfei directory=dir_xff dumpfile=METADATA_ONLY.dmp logfile=METADATA_ONLY.log ENCRYPTION_PASSWORD=www.orasos.com Import: Release 11.2.0.4.0 - Production on Wed Feb 11 16:05:46 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 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 ORA-39002: invalid operation ORA-39176: Encryption password is incorrect.
导入数据密码正确
[oracle@localhost tmp]$ impdp chf/xifenfei tables=xifenfei directory=dir_xff dumpfile=METADATA_ONLY.dmp logfile=METADATA_ONLY.log ENCRYPTION_PASSWORD=www.xifenfei.com Import: Release 11.2.0.4.0 - Production on Wed Feb 11 16:06:00 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 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 "CHF"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "CHF"."SYS_IMPORT_TABLE_01": chf/******** tables=xifenfei directory=dir_xff dumpfile=METADATA_ONLY.dmp logfile=METADATA_ONLY.log ENCRYPTION_PASSWORD=******** Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "CHF"."XIFENFEI" 5.460 KB 3 rows Job "CHF"."SYS_IMPORT_TABLE_01" successfully completed at Wed Feb 11 16:06:04 2015 elapsed 0 00:00:02
验证数据
[oracle@localhost tmp]$ sqlplus chf/xifenfei
SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 11 16:06:09 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
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
SQL> set lines 150
SQL> col name for a50
SQL> select * from xifenfei;
ID NAME
---------- --------------------------------------------------
1 xifenfei
2 www.xifenfei.com
3 www.orasos.com
