SQL can execute in wrong Schema

AIX53+10.2.0.2 RAC[@more@]

Description

If different schemas have objects (tables / views / synonyms etc..) with identical names and sessions from different schemas execute IDENTICAL SQL statements then it is possible for such SQL sentences to get executed against the wrong schema objects if a cursor gets reloaded in a certain manner.

eg:
Consider USER1 and USER2 both with their own table MYTAB.
If sessions are concurrently executing "SELECT col FROM MYTAB" then it is possible, under certain conditions, for USER1 to execute the statement in USER2's schema regardless of any permissions granted.

This can occur for DML or SELECT SQL .
Once a bad reload has occurred the wrong schema continues to be used for that session until the cursor is aged out or invalidated.

Workaround or Resolution

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.

The best solution to prevent this problem is to get the fix.

Patches

This issue is expected to be fixed in the 10.2.0.3 Patch Set.

Interim patches are available for 10.2.0.2 on some platforms under Patch 5458753 .

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7318139/viewspace-918946/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7318139/viewspace-918946/