sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA > Oracle expdp impdp >

【学习笔记】Oracle exp 数据库逻辑exp expdp导出dmp加密案例

时间:2016-07-02 23:37   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 给你的dmp文件(datapump)加上密码锁,分享一篇关于Oracle数据库dmp文件(datapump)加上密码锁的方法,含:逻辑exp导出和expdp数据泵导出时加密案例

从oracle 11.1.0.7开始oracle 支持data pump导出加密,从而实现dmp文件安全.不会因为dmp文件丢失而导致数据泄露.涉及数据泵加密参数主要有:ENCRYPTION,ENCRYPTION_ALGORITHM,ENCRYPTION_MODE,ENCRYPTION_PASSWORD几个参数.这里测试的是使用最简单方式实现datapump加密功能,如果需要更好的数据安全可以考虑实时密码钱包

1.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

2.ENCRYPTION_ALGORITHM

Specify how encryption should be done.
Valid keyword values are: [AES128], AES192 and AES256.
ENCRYPTION_ALGORITHM是指加密算法,参数值可以有AES128, AES192 and AES256。默认值为AES128

3.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需要加密钱包方式导出和导入

4.ENCRYPTION_PASSWORD

Password key for creating encrypted data within a dump file.
ENCRYPTION_PASSWORD指定加密密码

5.Oracle数据库exp expdp加密测试

[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/oracleplus
Connected.
SQL> create table oracleplus (id number,name varchar2(50));

Table created.

SQL> insert into oracleplus values(&id,'&name');
Enter value for id: 1
Enter value for name: oracleplus
old   1: insert into oracleplus values(&id,'&name')
new   1: insert into oracleplus values(1,'oracleplus')

1 row created.

SQL> /
Enter value for id: 2
Enter value for name: www.oracleplus.com
old   1: insert into oracleplus values(&id,'&name')
new   1: insert into oracleplus values(2,'www.oracleplus.com')

1 row created.

SQL> /
Enter value for id: 3
Enter value for name: www.orasos.com
old   1: insert into oracleplus values(&id,'&name')
new   1: insert into oracleplus values(3,'www.orasos.com')

1 row created.

SQL> commit;

Commit complete.

SQL> col name for a50
SQL> set lines 100
SQL> select * from oracleplus;

        ID NAME
---------- --------------------------------------------------
         1 oracleplus
         2 www.oracleplus.com
         3 www.orasos.com

创建目录

SQL> create directory dir_xff as '/tmp';

Directory created.

不加密导出

[oracle@localhost ~]$ expdp chf/oracleplus tables=oracleplus 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=oracleplus 
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"."oracleplus"                            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/oracleplus tables=oracleplus directory=dir_xff  ENCRYPTION=ALL dumpfile=ALL.dmp 
logfile=ALL.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=www.oracleplus.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=oracleplus 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"."oracleplus"                            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/oracleplus tables=oracleplus directory=dir_xff  ENCRYPTION=DATA_ONLY 
dumpfile=DATA_ONLY.dmp logfile=DATA_ONLY.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=www.oracleplus.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=oracleplus 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"."oracleplus"                            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/oracleplus tables=oracleplus directory=dir_xff  ENCRYPTION=METADATA_ONLY 
dumpfile=METADATA_ONLY.dmp logfile=METADATA_ONLY.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=www.oracleplus.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=oracleplus 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"."oracleplus"                            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 oracleplus purge;

Table dropped.

导入数据未输入密码

[oracle@localhost tmp]$ impdp chf/oracleplus tables=oracleplus directory=dir_xff 
dumpfile=METADATA_ONLY.dmp logfile=oracleplus.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/oracleplus tables=oracleplus 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/oracleplus tables=oracleplus directory=dir_xff   
dumpfile=METADATA_ONLY.dmp logfile=METADATA_ONLY.log ENCRYPTION_PASSWORD=www.oracleplus.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=oracleplus 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"."oracleplus"                            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/oracleplus

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 oracleplus;

        ID NAME
---------- --------------------------------------------------
         1 oracleplus
         2 www.oracleplus.com
         3 www.orasos.com

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle exp 数据库逻辑exp expdp导出dmp加密案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/699.html

Oracle研究中心

关键词:

Oracle exp加密

Oracle逻辑导出加密方法

Oracle数据泵加密存储

数据库逻辑导出dmp加密案例