说明:本篇参考eygle老师的作品《Oracle DBA手记4:数据安全警示录》,特此表示感谢。
3、Oracle Wallet加密策略
Oracle Wallet是一种加密安全策略,过去我们在TDE(Oracle透明加密)部分研究过这个组件。简单的说,Oracle Wallet就是在本机上配置一个加密配置文件,通过SQL命令控制Oracle Wallet的开启关闭状态,如果Wallet关闭或者不存在,那么一些加密的信息(包括TDE和RMAN备份集合)数据就不能正常打开。
使用Oracle Wallet应用在RMAN备份中,可以实现类似的透明策略。而且,备份集合只能在相同的服务器(借助Wallet文件)才能正确打开。
首先,我们需要创建Oracle Wallet。注意:笔者使用的GI单实例ASM策略,监听器是从Grid里面执行的。
[oracle@NCR-Standby-Asm ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-JUN- 13:49:47
Copyright (c) 1991, , Oracle.All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
AliasLISTENER
VersionTNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date25-MAY- 17:39:56
Uptime13 days 20 hr. 9 min. 51 sec
Trace Leveloff
SecurityON: Local OS Authentication
SNMPOFF
Listener Parameter File/u01/app/grid/product/11.2.0/grid/network/admin/listener.ora
Listener Log File/u01/app/grid/diag/tnslsnr/NCR-Standby-Asm/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "sicsstb" has 1 instance(s).
Instance "sicsstb", status READY, has 1 handler(s) for this service...
Service "sicsstbXDB" has 1 instance(s).
Instance "sicsstb", status READY, has 1 handler(s) for this service...
The command completed successfully
设置wallet目录位置,需要修改sqlnet.ora文件,加入特定的参数路径信息。注意:虽然监听器指向的是Grid目录位置。但是修改的sqlnet.ora文件,一定是Oracle Instance目录下的sqlnet.ora文件。否则自动创建秘钥文件过程会失败。
[oracle@NCR-Standby-Asm ~]$ cd $ORACLE_HOME/network/admin
[oracle@NCR-Standby-Asm admin]$ ls -l
total 24
drwxrwxr-x 2 oracle oinstall 4096 May5 10:03 samples
-rwxrwxr-x 1 oracle oinstall381 Dec 17 shrept.lst
-rwxrwxr-x 1 oracle oinstall327 Jun 8 15:32 sqlnet1506083PM3230.bak
-rwxrwxr-x 1 oracle oinstall327 Jun8 14:29 sqlnet.ora
-rwxrwxr-x 1 oracle oinstall340 Jun8 15:32 tnsnames1506083PM3230.bak
-rwxrwxr-x 1 oracle oinstall340 Jun8 16:19 tnsnames.ora
[oracle@NCR-Standby-Asm admin]$ cat sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u02/app/oracle/admin/sicsstb/WALLET)))
进入sqlplus创建wallet文件。
[oracle@NCR-Standby-Asm admin]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 8 14:29:28
Copyright (c) 1982, , Oracle.All rights reserved.
SQL> conn / as sysdba
Connected.
SQL>alter system set encryption key authenticated by "test";
System altered.
确定wallet文件生成。
[oracle@NCR-Standby-Asm admin]$ pwd
/u02/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@NCR-Standby-Asm admin]$ cd /u02/app/oracle/admin/sicsstb/WALLET
[oracle@NCR-Standby-Asm WALLET]$ ls -l
total 4
-rw-r--r-- 1 oracle asmadmin 2845 Jun8 14:29 ewallet.p12
尝试关闭开启钱包操作。
SQL> alter system set encryption wallet close identified by "test";
System altered
SQL>alter system set encryption wallet open identified by "test";--把wallet打开了
System altered
配置加密备份过程。
RMAN> configure encryption for database on;
new RMAN configuration parameters:
CONFIGURE ENCRYPTION FOR DATABASE ON;
new RMAN configuration parameters are successfully stored
RMAN>set encryption on;
executing command: SET encryption
备份数据库,注意此时wallet开启。
RMAN> backup database;
Starting backup at 08-JUN-15
using channel ORA_DISK_1
(篇幅原因,有省略……)
Starting Control File and SPFILE Autobackup at 08-JUN-15
piece handle=+RECO/sicsstb/autobackup/_06_08/s_881858264.262.881858265 comment=NONE
Finished Control File and SPFILE Autobackup at 08-JUN-15
备份操作是成功的,但是如果我们关闭了钱包,备份操作如何呢?
SQL> alter system set encryption wallet close identified by "test";
System altered
备份过程:
RMAN> backup database;
Starting backup at 08-JUN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
(篇幅原因,有省略……)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/08/ 16:39:38
ORA-19914: unable to encrypt backup
ORA-28365: wallet is not open
恢复过程测试。
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area1603411968 bytes
Fixed Size2253664 bytes
Variable Size1006636192 bytes
Database Buffers587202560 bytes
Redo Buffers7319552 bytes
RMAN> restore database;
Starting restore at 08-JUN-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=131 device type=DISK
(篇幅原因,有省略……)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/08/ 16:41:19
ORA-19870: error while restoring backup piece +RECO/sicsstb/backupset/_06_08/nnndf0_tag0608t163709_0.261.881858229
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open
不开钱包,就不能进行还原。
SQL> alter system set encryption wallet open identified by "test";
System altered
RMAN> restore database;
Starting restore at 08-JUN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
(篇幅原因,有省略…….)
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 08-JUN-15
RMAN> recover database;
Starting recover at 08-JUN-15
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 08-JUN-15
这种方式比较适合本地数据恢复,如果本地异地恢复结合的方式,建议使用第三种混合策略。
4、混合加密策略
混合加密策略其实就是前面两种策略的集合。如果本地备份恢复,就可以使用wallet进行透明操作。如果是异地恢复,可以使用密码策略。
首先设置encryption参数。
RMAN> set encryption off;
executing command: SET encryption
RMAN> set encryption on identified by "test"; --注意:此处没有only了。
executing command: SET encryption
重启还原。
RMAN> shutdown immediate;
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area1603411968 bytes
Fixed Size2253664 bytes
Variable Size1006636192 bytes
Database Buffers587202560 bytes
Redo Buffers7319552 bytes
RMAN> restore database;
Starting restore at 08-JUN-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=131 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/sicsstb/datafile/system.267.881856977
channel ORA_DISK_1: restoring datafile 00002 to +DATA/sicsstb/datafile/sysaux.268.881856977
channel ORA_DISK_1: restoring datafile 00003 to +DATA/sicsstb/datafile/undotbs1.269.881856977
channel ORA_DISK_1: restoring datafile 00004 to +DATA/sicsstb/datafile/users.270.881856977
channel ORA_DISK_1: reading from backup piece +RECO/sicsstb/backupset/_06_08/nnndf0_tag0608t163709_0.261.881858229
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/08/ 16:47:42
ORA-19870: error while restoring backup piece +RECO/sicsstb/backupset/_06_08/nnndf0_tag0608t163709_0.261.881858229
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open
启动钱包。
SQL> alter system set encryption wallet open identified by "test";
System altered
之后恢复正常。
5、结论
安全是当今信息技术的一个大课题,需要从技术、管理、制度和人员多层面进行配置规划,设置标准的流程规范。