expdp impdp 加密

一 描述

安全与性能两个都需要考虑的因素.本文未从数据库层演示数据库安全设置.做为DBA,可以先从小处着手,将由数据泵导出的数据进行加密.以防止导出文件盗用而引导起的数据安全.此文档步骤很少,仅以演示expdp的encryption_password参数.

二 操作环境

OS info

$lsb_release -a

LSB Version:    :core-3.1-ia32:core-3.1-noarch:graphics-3.1-ia32:graphics-3.1-noarch

Distributor ID: RedHatEnterpriseServer

Description:    Red Hat Enterprise Linux Server release 5.4 (Tikanga)

Release:        5.4

Codename:       Tikanga

$uname -a

Linux bogon 2.6.18-164.el5 #1 SMP Tue Aug 18 15:51:54 EDT 2009 i686 i686 i386 GNU/Linux

$

DB info

$sqlplus / as sysdba <

> set lines 150

> COL PRODUCT FORMAT A55

> COL VERSION FORMAT A15

> COL STATUS FORMAT A15

> SELECT * FROM PRODUCT_COMPONENT_VERSION;

> col value for a50

> set pages 50

> select * from nls_database_parameters;

> 

> archive log list;

> EOF

 

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 12 07:15:29 2012

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

 

SQL> SQL> SQL> SQL> SQL>

PRODUCT                                                 VERSION         STATUS

------------------------------------------------------- --------------- ---------------

NLSRTL                                                  10.2.0.1.0      Production

Oracle Database 10g Enterprise Edition                  10.2.0.1.0      Prod

PL/SQL                                                  10.2.0.1.0      Production

TNS for Linux:                                          10.2.0.1.0      Production

 

SQL> SQL> SQL>

PARAMETER                      VALUE

------------------------------ --------------------------------------------------

NLS_LANGUAGE                   AMERICAN

NLS_TERRITORY                  AMERICA

NLS_CURRENCY                   $

NLS_ISO_CURRENCY               AMERICA

NLS_NUMERIC_CHARACTERS         .,

NLS_CHARACTERSET               ZHS16GBK

NLS_CALENDAR                   GREGORIAN

NLS_DATE_FORMAT                DD-MON-RR

NLS_DATE_LANGUAGE              AMERICAN

NLS_SORT                       BINARY

NLS_TIME_FORMAT                HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY              $

NLS_COMP                       BINARY

NLS_LENGTH_SEMANTICS           BYTE

NLS_NCHAR_CONV_EXCP            FALSE

NLS_NCHAR_CHARACTERSET         AL16UTF16

NLS_RDBMS_VERSION              10.2.0.1.0

 

20 rows selected.

 

SQL> SQL> Database log mode            No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Current log sequence           3

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

$

other

 

三 过程设计

1.       创建测试环境.

2.       导出scott用户数据,使用encryption_password参数对导出文件进行加密.

3.       导入scott用户加密的导出文件到hr用户.

四 详细步骤操作

1.       创建测试环境

$mkdir -p /home/oracle/backup/encryption_data

$sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 12 07:23:18 2012

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

 

SQL> create directory encryption_data as '/home/oracle/backup/encryption_data';

 

Directory created.

 

SQL>  grant read,write on directory encryption_data to scott;

 

Grant succeeded.

 

SQL> grant read,write on directory encryption_data to hr;

 

Grant succeeded.

 

SQL>

 

2.       导出数据,使用encryption_password参数对导出文件进行加密.

$expdp scott/tiger directory=encryption_data dumpfile=expdp_encryption_120917.dmp logfile=expdp_encryption_120917.log encryption_password=www.163.com

 

Export: Release 10.2.0.1.0 - Production on Wednesday, 12 September, 2012 7:29:35

 

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** directory=encryption_data dumpfile=expdp_encryption_120917.dmp logfile=expdp_encryption_120917.log encryption_password=********

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 192 KB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."DEPT"                              5.656 KB       4 rows

. . exported "SCOTT"."EMP"                               7.820 KB      14 rows

. . exported "SCOTT"."SALGRADE"                          5.585 KB       5 rows

. . exported "SCOTT"."BONUS"                                 0 KB       0 rows

Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:

  /home/oracle/backup/encryption_data/expdp_encryption_120917.dmp

Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 07:29:56

 

$

 

3.       导入加密的导出文件.

$impdp system/oracle directory=encryption_data dumpfile=expdp_encryption_120917.dmp logfile=impdp_encryption_120917.log remap_schema=scott:hr encryption_password=www.163.com

 

Import: Release 10.2.0.1.0 - Production on Wednesday, 12 September, 2012 7:34:39

 

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

ORA-39175: Encryption password is not needed.

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=encryption_data dumpfile=expdp_encryption_120917.dmp logfile=impdp_encryption_120917.log remap_schema=scott:hr encryption_password=*********

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "HR"."DEPT"                                 5.656 KB       4 rows

. . imported "HR"."EMP"                                  7.820 KB      14 rows

. . imported "HR"."SALGRADE"                             5.585 KB       5 rows

. . imported "HR"."BONUS"                                    0 KB       0 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 07:34:45

 

$

五 个人总结

利用数据泵导出的加密参数,可以防止导出导出文件被盗用引起的数据丢失问题.

六 资料参考引用

oracle10g 043课堂练习II（2）->第 15 章：数据库安全性

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11780477/viewspace-743840/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11780477/viewspace-743840/