一 描述
安全与性能两个都需要考虑的因素.本文未从数据库层演示数据库安全设置.做为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/