|
|
IT运行维护中，系统升级补丁是不可缺少的工作。无论是何种系统或者软件，都存在出现Bug和故障的可能。同时，系统不断升级、推出新特性功能，都会给系统现有平衡状态带来新漏洞出现的机会。此外，在各种因素的驱动作用下，外部攻击者也会不断挖掘系统的安全漏洞，给系统带来潜在攻击风险。
作为专业的IT运行机构，比较成熟的做法是设立专门的安全管理团队负责系统升级和补丁工作。安全管理员会在各基础软件官方网站、安全论坛和社区中进行监视，及时发现整理出被业界发现的漏洞和问题，交付运维部门进行处理。
作为最大商业数据库的Oracle，安全是最重要的。无论功能多么强大，运行速度多么高效，有大的安全隐患一个硬伤，就可以将所有商业声誉毁坏殆尽。针对安全漏洞和Bug，Oracle内部和外部都有很严格的处理流程和步骤。在官方MOS网站上，付费用户是可以下载到各种安全补丁和漏洞补丁。
CPU（Critical Patch Update）是Oracle的一种安全补丁集合。通常而言，Bug Fix是一系列相互依赖或者独立的程序包构成。除非紧急救命场景，单独找一个Bug Fix装上的机会并不多。而且，一旦Bug Fix相互依赖，也是比较麻烦的事情。但长时间不进行升级，让系统带病运行显然也不是Oracle可以袖手不管的情况。于是，Oracle按照固定时间（通常一个季度）为间隔，以累计的方式将期间内的安全补丁集合发布。这种方式对管理员来说，更好的控制补丁操作时间和减少升级风险。
2014年Oracle主流版本（11.2.0.x-12.1.0.x）爆发出严重安全漏洞。漏洞借助with语句特性，让用户绕过权限体系对只拥有select权限的数据表可以进行insert、update和delete操作。行业普遍认为该补丁一定要尽早修复，减少数据安全风险。本篇主要演示通过补丁升级方式，解决安全漏洞。注意：由于篇幅原因，具体补丁环节笔者不会进行演示，请参考其他资料。
1、环境介绍
笔者选择一个全新安装的Oracle 11gR2数据库，具体版本为11.2.0.4。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition>
PL/SQL>
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 – Production
环境架构上，数据库为单实例+ASM Storage组织结构。ASM、Database通过Grid Infrastructure进行组织管理。
[grid@NCR-Standby-Asm ~]$ crsctl stat res -t -init
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE ncr-standby-asm
ora.LISTENER.lsnr
ONLINE ONLINE ncr-standby-asm
ora.RECO.dg
ONLINE ONLINE ncr-standby-asm
ora.asm
ONLINE ONLINE ncr-standby-asm Started
ora.ons
OFFLINE OFFLINE ncr-standby-asm
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE ONLINE ncr-standby-asm
ora.diskmon
1 OFFLINE OFFLINE
ora.evmd
1 ONLINE ONLINE ncr-standby-asm
ora.sicsstb.db
1 ONLINE ONLINE ncr-standby-asm Open
2、安全检索漏洞
下面演示未打补丁过程中的问题。创建用户test，进行简单授权。
SQL> create user test>
User created
SQL> grant connect to test;
Grant succeeded
SQL> grant select on scott.dept to test;
Grant succeeded
此时test用户只有一个select权限，针对scott.dept数据表。
SQL> select * from scott.dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
切换到test用户，进行测试。
SQL> insert into scott.dept values(50,'TEST', 'TEST');
insert into scott.dept values(50,'TEST', 'TEST')
ORA-01031: 权限不足
SQL> update scott.dept set dname='TEST' where deptno=40;
update scott.dept set dname='TEST' where deptno=40
ORA-01031: 权限不足
SQL> delete scott.dept;
delete scott.dept
ORA-01031: 权限不足
直接对数据表的增加、修改和删除是没有问题的，从权限角度被拒绝。但是，如果通过with语句进行转换，权限却可以被绕过。
SQL> insert into (with tmp as (select * from scott.dept) select * from tmp) values (50,'TEST', 'TEST');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from scott.dept;
DEPTNO DNAME LOC
------ -------------- -------------
50 TEST TEST
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> update (with tmp as (select * from scott.dept) select * from tmp) set dname='Bug-TEST' where deptno=50;
1 row updated
SQL> commit;
Commit complete
SQL> select * from scott.dept;
DEPTNO DNAME LOC
------ -------------- -------------
50 Bug-TEST TEST
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> delete (with tmp as (select * from scott.dept) select * from tmp) where deptno=50;
1 row deleted
SQL> commit;
Commit complete
SQL> select * from scott.dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
这显然是不能容忍的安全漏洞问题。
3、OPatch更新和更新列表
根据Oracle推荐的方法论，每次进行有计划升级的时候，都需要使用OPatch最新版本。随Oracle程序安装的版本通常有各种Bug和问题，需要从MOS上下载最新的OPatch使用。
[grid@NCR-Standby-Asm ~]$ cd /upload/
[grid@NCR-Standby-Asm upload]$ ls -l
total 51416
-rw-r--r-- 1 root root 52648436 May 25 08:52 p6880880_112000_Linux-x86-64.zip
[iyunv@NCR-Standby-Asm upload]# chown oracle:oinstall p6880880_112000_Linux-x86-64.zip
[oracle@NCR-Standby-Asm ~]$ cd /upload/
[oracle@NCR-Standby-Asm upload]$ cp p6880880_112000_Linux-x86-64.zip $ORACLE_HOME
[oracle@NCR-Standby-Asm upload]$ cd $ORACLE_HOME
[oracle@NCR-Standby-Asm dbhome_1]$ ls -l | grep OP
drwxr-xr-x 8 oracle oinstall 4096 May 5 10:05 OPatch
原有OPatch以目录方式存在，就在$ORACLE_HOME目录下。注意：oracle和grid分别有各自的OPatch工具包，要分别进行更新。
--更名备份策略
[oracle@NCR-Standby-Asm dbhome_1]$ mv OPatch OPatch_150525
[oracle@NCR-Standby-Asm dbhome_1]$ ls -l | grep p6880880_112000_Linux-x86-64.zip
-rw-r--r-- 1 oracle oinstall 52648436 May 25 08:54 p6880880_112000_Linux-x86-64.zip
[oracle@NCR-Standby-Asm dbhome_1]$ unzip p6880880_112000_Linux-x86-64.zip
Archive: p6880880_112000_Linux-x86-64.zip
creating: OPatch/
（篇幅原因，有省略……）
inflating: OPatch/opatchdiag
inflating: OPatch/opatch.pl
[oracle@NCR-Standby-Asm dbhome_1]$
可以通过./opatch version命令来判断OPatch版本。
[oracle@NCR-Standby-Asm dbhome_1]$ ls -l | grep OP
drwxr-x--- 10 oracle oinstall 4096 Mar 31 17:10 OPatch
drwxr-xr-x 8 oracle oinstall 4096 May 5 10:05 OPatch_150525
[oracle@NCR-Standby-Asm OPatch]$ ./opatch version
OPatch Version: 11.2.0.3.10
OPatch succeeded.
为了方便使用，可以将$ORACLE_HOME/OPatch目录加入到环境变量$PATH中去，这样会很方便。
[oracle@NCR-Standby-Asm OPatch]$ cd ~
[oracle@NCR-Standby-Asm ~]$ vi .bash_profile
（篇幅原因，有省略……）
ORACLE_BASE=/u02/app/oracle
ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1
PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
export ORACLE_SID
export ORACLE_BASE
export ORACLE_HOME
".bash_profile" 22L, 382C written
OPatch工具的lsinventory命令，可以查看所有升级历史。
oracle@NCR-Standby-Asm ~]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.10
Copyright (c) 2015, Oracle Corporation. All rights reserved.
Oracle Home : /u02/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u02/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.10
OUI version : 11.2.0.4.0
Log file location : /u02/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-05-25_09-04-33AM_1.log
Lsinventory Output file location : /u02/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2015-05-25_09-04-33AM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: localhost
ARU platform>
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.
下面，本次进行更新的PSU和CPU列表。
GI
19852360
ORACLE JAVA TECHNOLOGY Patch for Bug# 19852360 for Generic Platforms
p19852360_112040_Generic
20485808
Oracle Grid Infrastructure Patch Set Update 11.2.0.4.6 (Apr2015) (Includes Database PSU 11.2.0.4.6)
p20485808_112040_Linux-x86-64
20834621
Combo of OJVM Component 11.2.0.4.3 DB PSU + GI PSU 11.2.0.4.6 (Apr2015)
p20834621_112040_Linux-x86-64
Database
19852360
ORACLE JAVA TECHNOLOGY Patch for Bug# 19852360 for Generic Platforms
p19852360_112040_Generic
20299013
Database Patch Set Update 11.2.0.4.6 (Includes CPUApr2015)
p20299013_112040_Linux-x86-64
20299015
Database Security Patch Update 11.2.0.4.0 (CPUApr2015)
p20299015_112040_Linux-x86-64
20406239
Oracle JavaVM Component 11.2.0.4.3 Database PSU (Apr2015)
p20406239_112040_Linux-x86-64
20834611
Combo of OJVM Component 11.2.0.4.3 DB PSU + DB PSU 11.2.0.4.6 (Apr2015)
p20834611_112040_Linux-x86-64
注意：补丁包之间可能会有重叠的情况，特别是一些GI更新之后，联动的Database也打上补丁了。
4、升级之后测试
补丁之后，我们可以通过视图dba_registry_history或者利用OPatch查看系统补丁情况。
SQL> select action_time, action, version, comments from dba_registry_history;
ACTION_TIME ACTION VERSION COMMENTS
---------------------------------------- ---------- ----------------- ------------------------------
24-8?? -13 12.03.45.119862 ???? APPLY 11.2.0.4 Patchset 11.2.0.2.0
05-5?? -15 10.20.34.429195 ???? APPLY 11.2.0.4 Patchset 11.2.0.2.0
25-5?? -15 04.16.33.326118 ???? APPLY 11.2.0.4 PSU 11.2.0.4.6
25-5?? -15 05.12.32.715043 ???? jvmpsu.sql 11.2.0.4.3OJVMBP RAN jvmpsu.sql
25-5?? -15 05.12.32.790741 ???? APPLY 11.2.0.4.3OJVMBP OJVM PSU post-install
25-5?? -15 05.12.32.000000 ???? APPLY Patch 20406239 applied
25-5?? -15 05.42.15.728778 ???? APPLY 11.2.0.4 PSU 11.2.0.4.6
7 rows selected
[oracle@NCR-Standby-Asm ~]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.10
Copyright (c) 2015, Oracle Corporation. All rights reserved.
Oracle Home : /u02/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u02/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.10
OUI version : 11.2.0.4.0
Log file location : /u02/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-05-26_09-20-36AM_1.log
Lsinventory Output file location : /u02/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2015-05-26_09-20-36AM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: localhost
ARU platform>
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.
Interim patches (3) :
Patch 20406239 : applied on Mon May 25 17:06:22 CST 2015
Unique Patch> 18763312
Patch description: "ORACLE JAVAVM COMPONENT 11.2.0.4.3 DATABASE PSU (APR2015)"
Created on 31 Mar 2015, 07:39:44 hrs PST8PDT
Bugs fixed:
19007266, 19909862, 19153980, 19554117, 17201047, 19058059, 19852360
20408829, 18933818, 19006757, 19895326, 19231857, 18458318, 17285560
17056813, 18166577, 14774730, 19374518, 19223010
Patch 20420937 : applied on Mon May 25 15:06:20 CST 2015
Unique Patch> 18573450
Patch description: "OCW Patch set update : 11.2.0.4.6 (20420937)"
Created on 27 Mar 2015, 15:19:23 hrs PST8PDT
Bugs fixed:
18328800, 19270660, 18691572, 20365005, 17750548, 17387214, 17617807
（篇幅原因，有省略……）
16867761, 20235486, 15869775, 19642566, 17447588, 15920201
Patch 20299013 : applied on Mon May 25 15:05:19 CST 2015
Unique Patch> 18573940
Patch description: "Database Patch Set Update : 11.2.0.4.6 (20299013)"
Created on 4 Mar 2015, 02:27:44 hrs PST8PDT
Sub-patch 19769489; "Database Patch Set Update : 11.2.0.4.5 (19769489)"
Sub-patch 19121551; "Database Patch Set Update : 11.2.0.4.4 (19121551)"
Sub-patch 18522509; "Database Patch Set Update : 11.2.0.4.3 (18522509)"
Sub-patch 18031668; "Database Patch Set Update : 11.2.0.4.2 (18031668)"
Sub-patch 17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"
Bugs fixed:
17288409, 17798953, 18273830, 18607546, 17811429, 17205719, 20506699
17816865, 19972566, 17922254, 17754782, 16384983, 17726838, 13364795
（篇幅原因，有省略……）
--------------------------------------------------------------------------------
OPatch succeeded.
通过实验测试安全补丁是否生效。
SQL> conn test/test@sicsstb
Connected to Oracle Database 11g Enterprise Edition>
Connected as test
SQL> select * from scott.dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> insert into (with tmp as (select * from scott.dept) select * from tmp) values (50,'TEST', 'TEST');
insert into (with tmp as (select * from scott.dept) select * from tmp) values (50,'TEST', 'TEST')
ORA-01031: 权限不足
SQL> update (with tmp as (select * from scott.dept) select * from tmp) set dname='Bug-TEST' where deptno=50;
update (with tmp as (select * from scott.dept) select * from tmp) set dname='Bug-TEST' where deptno=50
ORA-01031: 权限不足
SQL> delete (with tmp as (select * from scott.dept) select * from tmp) where deptno=50;
delete (with tmp as (select * from scott.dept) select * from tmp) where deptno=50
ORA-01031: 权限不足
实验成功！
5、额外低版本测试
如果我们选择Oracle 10gR2版本，那么会有类似问题吗？
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition>
PL/SQL>
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 – Production
创建类似实验环境。
SQL> create user test>
User created
SQL> grant connect to test;
Grant succeeded
SQL> grant select on scott.dept to test;
Grant succeeded
实验用户test下。
SQL> conn test/test@chinareweb_pub
Connected to Oracle Database 10g Enterprise Edition>
Connected as test
SQL> select * from scott.dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> insert into scott.dept values (50,'TEST','TEST');
insert into scott.dept values (50,'TEST','TEST')
ORA-01031: insufficient privileges
SQL> insert into (with tmp as (select * from scott.dept) select * from tmp) values (50,'TEST', 'TEST');
insert into (with tmp as (select * from scott.dept) select * from tmp) values (50,'TEST', 'TEST')
ORA-01732: data manipulation operation not legal on this view
6、结论
安全漏洞是我们需要及时关注的一个重要问题。作为运维人员，要时刻注意厂商和技术社区中重点热点的问题，确保系统的正常和安全。
|
|
QQ群⑧：
窥视卡
雷达卡
发表于 2015-12-16 14:56:18
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡