Re: SQL may execute in wrong schema -- Quite Dangerous

  • From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
  • To: rjamya@xxxxxxxxx, "Oracle Discussion List" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 20 Sep 2006 19:41:36 +0800


Quite Dangerous ?! b Worthy of a Critical CPU
(can be seen as a Vulnerability)

The latest Rediscovery for 5458753 is
"*** 09/19/06 09:54 pm *** Rediscovery Information:
1. if the application design is such that schemas have similar looking objects and cursors
access those objects without full qualification, then due to this bug there is a possibility
under high pressure on the library cache that the cursor might access the incorrect base
object ie another schemas object which has the same name and structure. if the structure
isn't similar then we might get parse/semantic errors ORA-904 , ORA-02291 ( contraint errors) .

Workaround: none which could fully prevent this. decreasing load/pressure could help.
pinning the required cursor will help.

Release Notes: ]] incorrect objects were access
under heavy load if schemas have objects ]] with same name. this has been fixed. "


Also see Bugs
5531458
5466213 which says
"It is likely bug 5458753 but cant say 100% on existing evidence. > 95% likely though.
Likely workaround is to use _kks_use_mutex_pin = false "
and then
5517241 (re Sequences) which includes
"1. Setting _kks_use_mutex_pin=FALSE has been tried, it did not make any difference"

Hemant

At 06:53 PM Wednesday, rjamya wrote: 
This morning I found this bug which " can occur much more easily " in
10.2.0.2 onwards and the workaround is so funny.

Essentially this bug happens when you have more than one schema with
identical table/view names and two users are executing same SQL but in
different schema. Sometimes the SQL will execute in the _wrong_
schema.

"Workaround:
 There is no simple workaround which can avoid this fully.
 The issue can be avoided by prefixing object names with the schema name.
 eg: In the above example change the SQL to use
   "select mycol from A.mytable" for user A and
   "select mycol from B.mytable" for user B.

 If SQL cannot be changed then it can help to reduce shared pool
 load (as the problem occurs when cursors are reloaded having
 been aged out / invalidated). Using DBMS_SHARED_POOL.KEEP for
 affected cursors can also help.:

BUG# 5458753

Raj
----------------------------------------------
Got RAC?
--
//www.freelists.org/webpage/oracle-l



Hemant K Chitale
http://web.singnet.com.sg/~hkchital


--
//www.freelists.org/webpage/oracle-l


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 09-2006