Users querying SDO_Geometry data owned by a different user may encounter Oracle bugs 4716096 and 5031836

Description

SDE users querying SDO_Geometry data owned by a different user may encounter Oracle bugs 4716096 and 5031836. This can cause the spatial index on the SDO_Geometry column to not be used.

For more information consult Oracle documentation on the following:

"5031836 - SECURE VIEW MERGING ON 10.2 PREVENTS DOMAIN INDEX USAGE"
"4716096 - DOMAIN INDEX NOT PERFORMED IN QUERY ON VIEW BY A DIFFERENT USER"

Cause

This issue is caused by changes that were made to view security in Oracle 10gR2.

Solution or Workaround

• Upgrade to Oracle 10.2.0.4.
• Alter the database system to set the optimizer_secure_view_merging parameter to false.
  
  

  Code:
  alter system set optimizer_secure_view_merging = FALSE

  
  
• Grant the MERGE ANY VIEW permission to the user who is selecting the data.
  
  

  Code:
  grant merge any view to <the users selecting the data>