——记一次ORACLE数据库使用opatch升级PSU的过程
目的:使用oracle自带工具opatch对oracle数据库软件应用高危漏洞补丁集PSU,修复oracle数据库高危漏洞,规避由于普通用户具有业务用户数据表的查询权限,
绕过数据库安全机制对业务用户数据表进行增、删、改操作的隐患。
风险:
1、本次整改中需要启停数据库
2、对ORACLE数据库应用PSU补丁集可能会引入新的BUG
前期准备:
操作系统版本:
[oracle@orcl11204 20299013]$ uname -a
Linux orcl11204 2.6.18-164.el5xen #1 SMP Thu Sep 3 04:41:04 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
OPatch软件客户端版本:
[oracle@orcl11204 20299013]$ export PATH=$ORACLE_HOME/OPatch:$PATH
[oracle@orcl11204 20299013]$opatch version
OPatch Version: 11.2.0.3.4
OPatch succeeded.
数据库版本:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE11.2.0.4.0Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> select ACTION_TIME, ACTION, COMMENTS from sys.DBA_REGISTRY_HISTORY;
ACTION_TIME ACTION COMMENTS
------------------------------ -------------------- ------------------------------
24-AUG-13 12.03.45.119862 PM APPLY Patchset 11.2.0.2.0
29-JUN-15 05.15.49.338988 PM APPLY Patchset 11.2.0.2.0
SQL> select count(*) from dba_objects where status<>'VALID';
COUNT(*)
----------
0
SQL>select object_name,object_type,owner,status
from dba_objects where status<>'VALID';
no rows selected
准备高危漏洞补丁包
根据漏洞补丁集安装要求opatch的版本最低位11.2.0.3.6,因此需要准备
高危漏洞的模拟测试
[oracle@orcl11204]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 10 13:43:22 2015
Copyright (c) 1982, 2011, 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>create user test identified by test;
user created.
SQL>grant dba to test;
Grant succeeded.
SQL>conn test/test
Connected.
SQL>create table t(id number);
Table created.
SQL>insert into t values(1); --插入5条
1 row created.
SQL>commit;
Commit complete.
[oracle@orcl11g database]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 10 13:43:22 2015
Copyright (c) 1982, 2011, 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> create user test_update identified by test_update;
User created.
SQL> grant select on test.t to test_update;
Grant succeeded.
SQL> grant create session to test_update;
Grant succeeded.
SQL> conn test_update/test_update
Connected.
SQL> select count(*) from test.t;
COUNT(*)
----------
5
SQL> update (with tmp as (select id from test.t) select id from tmp) set id=10 where id = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> delete (with temp as (select * from test.t) select id from temp) where id = 2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> insert into (with temp as (select * from test.t) select * from temp) select *
from test.t where id =1;
0 rows created.
SQL> commit;
Commit complete.
由上DML执行及结果部分可知,普通拥有对业务用户下表的查询权限可以通过构造临时表对业务数据表进
行DML操作,该漏洞很危险。
以下是漏洞补丁安装及补丁安装完毕后的验证:
关闭数据库实例及监听程序,如果EM开启,也要关闭EM
[oracle@orcl11204 20299013]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 29 17:48:47 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> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@orcl11204 20299013]$ lsnrctl statu
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-JUN-2015 17:50:59
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl11204)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 29-JUN-2015 16:51:52
Uptime 0 days 0 hr. 59 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/product/11.2.0.4/db/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/orcl11204/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl11204)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
根据readme的提示更新OPatch即将上传解压并替换$ORACLE_HOME下的OPatch,注意先备份原有的OPatch以方便回退还原
[oracle@orcl11204 ~]$ opatch version
OPatch Version: 11.2.0.3.11
OPatch succeeded.
根据readme的提示先做安装前的预检,注意如果没有更新OPatch,预检能够通过,但是应用补丁集的时候会报错退出
[oracle@orcl11204 20299013]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /opt/oracle/product/11.2.0.4/db
Central Inventory : /opt/oraInventory
from : /opt/oracle/product/11.2.0.4/db/oraInst.loc