标签归档：pumpdata 加密

从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