mysql datapump_给你的dmp文件(datapump)加上密码锁

从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