Oracle 11gR2高危許可權安全漏洞解決
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 Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
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 identified by 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
[root@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 id: 226
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 id: 226
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 ID: 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 ID: 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 ID: 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 Release 11.2.0.4.0
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 Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
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 identified by 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 Release 10.2.0.5.0
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、結論
安全漏洞是我們需要及時關注的一個重要問題。作為運維人員，要時刻注意廠商和技術社群中重點熱點的問題，確保系統的正常和安全。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17203031/viewspace-1673552/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle的物件許可權、角色許可權、系統許可權Oracle物件
- ORACLE許可權Oracle
- oracle 許可權集合Oracle
- oracle 許可權管理Oracle
- Oracle許可權（一）Oracle
- oracle許可權全集Oracle
- vue許可權問題解決方案Vue
- Oracle 使用者、物件許可權、系統許可權Oracle物件
- oracle 通過trigger解決drop許可權問題Oracle
- ubuntu下解決wireshark許可權問題Ubuntu
- Oracle 許可權（grant、revoke）Oracle
- Oracle系統許可權Oracle
- Oracle檢視許可權Oracle
- 【許可權管理】Oracle中檢視、回收使用者許可權Oracle
- django開發之許可權管理(一)——許可權管理詳解(許可權管理原理以及方案)、不使用許可權框架的原始授權方式詳解Django框架
- artisan日誌 root 許可權解決辦法
- Android許可權管理之Android 6.0執行時許可權及解決辦法Android
- 詳解Oracle的unlimited tablespace系統許可權OracleMIT
- Oracle 查詢許可權角色Oracle
- oracle許可權整理檔案Oracle
- oracle使用者許可權Oracle
- oracle列級許可權控制Oracle
- Oracle users / 許可權 / grant privOracle
- Oracle 中的references許可權Oracle
- Oracle ERP許可權管理Oracle
- oracle 使用者許可權Oracle
- 許可權之選單許可權
- 如何用 Vue 實現前端許可權控制（路由許可權 + 檢視許可權 + 請求許可權）Vue前端路由
- linux 檔案許可權 s 許可權和 t 許可權解析Linux
- linux許可權詳解Linux
- android - 解決“應用自定義許可權重名”Android
- Oracle 使用者許可權管理與常用許可權資料字典列表Oracle
- Oracle中定義者許可權和呼叫者許可權案例分析Oracle
- 谷歌VirusTotal開源元件曝高危漏洞，可獲取內網訪問許可權谷歌Rust元件內網訪問許可權
- Oracle軟體許可權修復Oracle
- Oracle許可權相關查詢Oracle
- Oracle使用者與許可權Oracle
- Oracle角色許可權之Default RoleOracle