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

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?
>--
>http://www.freelists.org/webpage/oracle-l
>

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

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