after converting my Geo-Database from Long Raw (SDEBINARY) to st_geometry, i found out that i can only see the feautre classes (layers) through schema accounts ONLY in ArcCatalog !!!
when i try to view the data with “application-user” accounts, i face the below error:
Underlying DBMS error[ORA-29902: error in executing ODCIIndexStart() routine
ORA-01031: insufficient privileges
ORA-06512: at “SDE.SPX_UTIL”,line 847
ORA-06512: at “SDE.SPX_UTIL”,line 891
ORA-06512: at “SDE.ST_DOMAIN_METHODS”, line 105
**** Solution:
since we follow strict security implimentation on the Oracle Databases, we have revoked many ‘PUBLIC’ role permissions in ArcSDE Geodatabase Systems, and it seems these permissions are required.
so you need to make sure that the following permissions listed are “granted“:
grant UPDATE on sde.ALL_ST_GEOMETRY_COLUMNS_V to app_user;
grant SELECT on sde.ALL_ST_GEOMETRY_COLUMNS_V to app_user;
grant INSERT on sde.ALL_ST_GEOMETRY_COLUMNS_V to app_user;
grant DELETE on sde.ALL_ST_GEOMETRY_COLUMNS_V to app_user;
grant EXECUTE on sde.ARCHIVE_UTIL to app_user;
grant EXECUTE on sde.BLOB_ARRAY_TAB to app_user;
grant EXECUTE on sde.BND_ROWID_TAB to app_user;
grant SELECT on sde.COLUMN_REGISTRY to app_user;
grant SELECT on sde.CONNECTION_ID_GENERATOR to app_user;
grant SELECT on sde.DBTUNE to app_user;
grant EXECUTE on sde.DBTUNE_UTIL to app_user;
grant EXECUTE on sde.FLT_ARRAY_TAB to app_user;
grant INSERT on sde.GDB_ITEMRELATIONSHIPS to app_user;
grant SELECT on sde.GDB_ITEMRELATIONSHIPS to app_user;
grant UPDATE on sde.GDB_ITEMRELATIONSHIPS to app_user;
grant DELETE on sde.GDB_ITEMRELATIONSHIPS to app_user;
grant INSERT on sde.GDB_ITEMRELATIONSHIPS_VW to app_user;
grant SELECT on sde.GDB_ITEMRELATIONSHIPS_VW to app_user;
grant UPDATE on sde.GDB_ITEMRELATIONSHIPS_VW to app_user;
grant DELETE on sde.GDB_ITEMRELATIONSHIPS_VW to app_user;
grant UPDATE on sde.GDB_ITEMRELATIONSHIPTYPES to app_user;
grant SELECT on sde.GDB_ITEMRELATIONSHIPTYPES to app_user;
grant INSERT on sde.GDB_ITEMRELATIONSHIPTYPES to app_user;
grant DELETE on sde.GDB_ITEMRELATIONSHIPTYPES to app_user;
grant UPDATE on sde.GDB_ITEMS to app_user;
grant SELECT on sde.GDB_ITEMS to app_user;
grant INSERT on sde.GDB_ITEMS to app_user;
grant DELETE on sde.GDB_ITEMS to app_user;
grant UPDATE on sde.GDB_ITEMS_VW to app_user;
grant DELETE on sde.GDB_ITEMS_VW to app_user;
grant INSERT on sde.GDB_ITEMS_VW to app_user;
grant SELECT on sde.GDB_ITEMS_VW to app_user;
grant SELECT on sde.GDB_ITEMTYPES to app_user;
grant UPDATE on sde.GDB_ITEMTYPES to app_user;
grant INSERT on sde.GDB_ITEMTYPES to app_user;
grant DELETE on sde.GDB_ITEMTYPES to app_user;
grant SELECT on sde.GDB_REPLICALOG to app_user;
grant UPDATE on sde.GDB_REPLICALOG to app_user;
grant INSERT on sde.GDB_REPLICALOG to app_user;
grant DELETE on sde.GDB_REPLICALOG to app_user;
grant UPDATE on sde.GDB_TABLES_LAST_MODIFIED to app_user;
grant SELECT on sde.GDB_TABLES_LAST_MODIFIED to app_user;
grant DELETE on sde.GDB_TABLES_LAST_MODIFIED to app_user;
grant INSERT on sde.GDB_TABLES_LAST_MODIFIED to app_user;
grant EXECUTE on sde.GDB_UTIL to app_user;
grant SELECT on sde.GEOMETRY_COLUMNS to app_user;
grant UPDATE on sde.INSTANCES to app_user;
grant SELECT on sde.INSTANCES to app_user;
grant INSERT on sde.INSTANCES to app_user;
grant DELETE on sde.INSTANCES to app_user;
grant EXECUTE on sde.INSTANCES_UTIL to app_user;
grant SELECT on sde.INSTANCE_ID_GENERATOR to app_user;
grant EXECUTE on sde.INT_ARRAY_TAB to app_user;
grant EXECUTE on sde.KEYSET_UTIL to app_user;
grant SELECT on sde.LAYERS to app_user;
grant EXECUTE on sde.LAYERS_UTIL to app_user;
grant SELECT on sde.LAYER_ID_GENERATOR to app_user;
grant SELECT on sde.LAYER_LOCKS to app_user;
grant EXECUTE on sde.LAYER_STATS_UTIL to app_user;
grant SELECT on sde.LINEAGES_MODIFIED to app_user;
grant SELECT on sde.LOCATORS to app_user;
grant SELECT on sde.LOCATOR_ID_GENERATOR to app_user;
grant EXECUTE on sde.LOCATOR_UTIL to app_user;
grant EXECUTE on sde.LOCK_UTIL to app_user;
grant EXECUTE on sde.LOGFILE_UTIL to app_user;
grant SELECT on sde.METADATA to app_user;
grant SELECT on sde.METADATA_ID_GENERATOR to app_user;
grant EXECUTE on sde.METADATA_UTIL to app_user;
grant SELECT on sde.MVTABLES_MODIFIED to app_user;
grant SELECT on sde.OBJECT_LOCKS to app_user;
grant EXECUTE on sde.PINFO_UTIL to app_user;
grant SELECT on sde.PROCESS_INFORMATION to app_user;
grant SELECT on sde.R2 to app_user;
grant SELECT on sde.R3 to app_user;
grant SELECT on sde.R4 to app_user;
grant SELECT on sde.R5 to app_user;
grant SELECT on sde.R6 to app_user;
grant EXECUTE on sde.RASTERCOLUMNS_UTIL to app_user;
grant SELECT on sde.RASTERCOLUMN_ID_GENERATOR to app_user;
grant SELECT on sde.RASTER_COLUMNS to app_user;
grant EXECUTE on sde.REGISTRY_UTIL to app_user;
grant EXECUTE on sde.SDENUMTAB to app_user;
grant EXECUTE on sde.SDEXMLTOTEXT to app_user;
grant SELECT on sde.SDE_ARCHIVES to app_user;
grant SELECT on sde.SDE_LAYER_STATS to app_user;
grant SELECT on sde.SDE_LOGFILE_POOL to app_user;
grant UPDATE on sde.SDE_TABLES_MODIFIED to app_user;
grant SELECT on sde.SDE_TABLES_MODIFIED to app_user;
grant EXECUTE on sde.SDE_UTIL to app_user;
grant SELECT on sde.SDE_XML_COLUMNS to app_user;
grant UPDATE on sde.SDE_XML_DOC1 to app_user;
grant SELECT on sde.SDE_XML_DOC1 to app_user;
grant DELETE on sde.SDE_XML_DOC1 to app_user;
grant INSERT on sde.SDE_XML_DOC1 to app_user;
grant UPDATE on sde.SDE_XML_DOC2 to app_user;
grant SELECT on sde.SDE_XML_DOC2 to app_user;
grant INSERT on sde.SDE_XML_DOC2 to app_user;
grant DELETE on sde.SDE_XML_DOC2 to app_user;
grant UPDATE on sde.SDE_XML_DOC3 to app_user;
grant INSERT on sde.SDE_XML_DOC3 to app_user;
grant SELECT on sde.SDE_XML_DOC3 to app_user;
grant DELETE on sde.SDE_XML_DOC3 to app_user;
grant SELECT on sde.SDE_XML_DOC4 to app_user;
grant UPDATE on sde.SDE_XML_DOC4 to app_user;
grant INSERT on sde.SDE_XML_DOC4 to app_user;
grant DELETE on sde.SDE_XML_DOC4 to app_user;
grant SELECT on sde.SDE_XML_INDEXES to app_user;
grant SELECT on sde.SDE_XML_INDEX_TAGS to app_user;
grant EXECUTE on sde.SDO_UTIL to app_user;
grant SELECT on sde.SERVER_CONFIG to app_user;
grant SELECT on sde.SPATIAL_REFERENCES to app_user;
grant EXECUTE on sde.SPX_UTIL to app_user;
grant EXECUTE on sde.SP_GRID_INFO to app_user;
grant EXECUTE on sde.SREF_UTIL to app_user;
grant SELECT on sde.STATES to app_user;
grant SELECT on sde.STATE_LINEAGES to app_user;
grant SELECT on sde.STATE_LOCKS to app_user;
grant EXECUTE on sde.ST_AGGR_CONVEXHULL to app_user;
grant EXECUTE on sde.ST_AGGR_INTERSECTION to app_user;
grant EXECUTE on sde.ST_AGGR_UNION to app_user;
grant EXECUTE on sde.ST_AREA to app_user;
grant EXECUTE on sde.ST_ASBINARY to app_user;
grant EXECUTE on sde.ST_ASTEXT to app_user;
grant EXECUTE on sde.ST_BOUNDARY to app_user;
grant EXECUTE on sde.ST_BUFFER to app_user;
grant EXECUTE on sde.ST_BUFFER_INTERSECTS to app_user;
grant EXECUTE on sde.ST_CENTROID to app_user;
grant EXECUTE on sde.ST_CONTAINS to app_user;
grant EXECUTE on sde.ST_CONVEXHULL to app_user;
grant EXECUTE on sde.ST_COORDDIM to app_user;
grant SELECT on sde.ST_COORDINATE_SYSTEMS to app_user;
grant EXECUTE on sde.ST_CREF_UTIL to app_user;
grant EXECUTE on sde.ST_CROSSES to app_user;
grant EXECUTE on sde.ST_CURVE to app_user;
grant EXECUTE on sde.ST_DIFFERENCE to app_user;
grant EXECUTE on sde.ST_DIMENSION to app_user;
grant EXECUTE on sde.ST_DISJOINT to app_user;
grant EXECUTE on sde.ST_DISTANCE to app_user;
grant EXECUTE on sde.ST_DOMAIN_METHODS to app_user;
grant EXECUTE on sde.ST_DOMAIN_OPERATORS to app_user;
grant EXECUTE on sde.ST_DOMAIN_STATS to app_user;
grant EXECUTE on sde.ST_ENDPOINT to app_user;
grant EXECUTE on sde.ST_ENTITY to app_user;
grant EXECUTE on sde.ST_ENVELOPE to app_user;
grant EXECUTE on sde.ST_ENVINTERSECTS to app_user;
grant EXECUTE on sde.ST_EQUALS to app_user;
grant EXECUTE on sde.ST_EXTERIORRING to app_user;
grant DEBUG on sde.ST_FUNCS_ARRAY to app_user;
grant EXECUTE on sde.ST_FUNCS_ARRAY to app_user;
grant EXECUTE on sde.ST_GEOMCOLLECTION to app_user;
grant EXECUTE on sde.ST_GEOMETRY to app_user;
grant EXECUTE on sde.ST_GEOMETRYN to app_user;
grant EXECUTE on sde.ST_GEOMETRYTYPE to app_user;
grant SELECT on sde.ST_GEOMETRY_COLUMNS to app_user;
grant UPDATE on sde.ST_GEOMETRY_INDEX to app_user;
grant SELECT on sde.ST_GEOMETRY_INDEX to app_user;
grant INSERT on sde.ST_GEOMETRY_INDEX to app_user;
grant DELETE on sde.ST_GEOMETRY_INDEX to app_user;
grant EXECUTE on sde.ST_GEOMETRY_OPERATORS to app_user;
grant EXECUTE on sde.ST_GEOMETRY_SHAPELIB_PKG to app_user;
grant EXECUTE on sde.ST_GEOM fromTEXT to app_user;
grant EXECUTE on sde.ST_GEOM fromWKB to app_user;
grant EXECUTE on sde.ST_GEOM_COLS_UTIL to app_user;
grant EXECUTE on sde.ST_GEOM_UTIL to app_user;
grant EXECUTE on sde.ST_INTERIORRINGN to app_user;
grant EXECUTE on sde.ST_INTERSECTION to app_user;
grant EXECUTE on sde.ST_INTERSECTS to app_user;
grant EXECUTE on sde.ST_IS3D to app_user;
grant EXECUTE on sde.ST_ISCLOSED to app_user;
grant EXECUTE on sde.ST_ISEMPTY to app_user;
grant EXECUTE on sde.ST_ISMEASURED to app_user;
grant EXECUTE on sde.ST_ISRING to app_user;
grant EXECUTE on sde.ST_ISSIMPLE to app_user;
grant EXECUTE on sde.ST_LENGTH to app_user;
grant EXECUTE on sde.ST_LINE fromTEXT to app_user;
grant EXECUTE on sde.ST_LINE fromWKB to app_user;
grant EXECUTE on sde.ST_LINESTRING to app_user;
grant EXECUTE on sde.ST_M to app_user;
grant EXECUTE on sde.ST_MAXM to app_user;
grant EXECUTE on sde.ST_MAXX to app_user;
grant EXECUTE on sde.ST_MAXY to app_user;
grant EXECUTE on sde.ST_MAXZ to app_user;
grant EXECUTE on sde.ST_MINM to app_user;
grant EXECUTE on sde.ST_MINX to app_user;
grant EXECUTE on sde.ST_MINY to app_user;
grant EXECUTE on sde.ST_MINZ to app_user;
grant EXECUTE on sde.ST_MLINE fromTEXT to app_user;
grant EXECUTE on sde.ST_MLINE fromWKB to app_user;
grant EXECUTE on sde.ST_MPOINT fromTEXT to app_user;
grant EXECUTE on sde.ST_MPOINT fromWKB to app_user;
grant EXECUTE on sde.ST_MPOLY fromTEXT to app_user;
grant EXECUTE on sde.ST_MPOLY fromWKB to app_user;
grant EXECUTE on sde.ST_MULTICURVE to app_user;
grant EXECUTE on sde.ST_MULTILINESTRING to app_user;
grant EXECUTE on sde.ST_MULTIPOINT to app_user;
grant EXECUTE on sde.ST_MULTIPOLYGON to app_user;
grant EXECUTE on sde.ST_MULTISURFACE to app_user;
grant EXECUTE on sde.ST_NUMGEOMETRIES to app_user;
grant EXECUTE on sde.ST_NUMINTERIORRING to app_user;
grant EXECUTE on sde.ST_NUMPOINTS to app_user;
grant EXECUTE on sde.ST_ORDERINGEQUALS to app_user;
grant EXECUTE on sde.ST_OVERLAPS to app_user;
grant UPDATE on sde.ST_PARTITION_INDEX to app_user;
grant SELECT on sde.ST_PARTITION_INDEX to app_user;
grant DELETE on sde.ST_PARTITION_INDEX to app_user;
grant INSERT on sde.ST_PARTITION_INDEX to app_user;
grant EXECUTE on sde.ST_POINT to app_user;
grant EXECUTE on sde.ST_POINT fromTEXT to app_user;
grant EXECUTE on sde.ST_POINT fromWKB to app_user;
grant EXECUTE on sde.ST_POINTN to app_user;
grant EXECUTE on sde.ST_POINTONSURFACE to app_user;
grant EXECUTE on sde.ST_POLY fromTEXT to app_user;
grant EXECUTE on sde.ST_POLY fromWKB to app_user;
grant EXECUTE on sde.ST_POLYGON to app_user;
grant EXECUTE on sde.ST_RELATE to app_user;
grant EXECUTE on sde.ST_RELATION_OPERATORS to app_user;
grant EXECUTE on sde.ST_SPATIAL_INDEX to app_user;
grant SELECT on sde.ST_SPATIAL_REFERENCES to app_user;
grant EXECUTE on sde.ST_SPREF_UTIL to app_user;
grant EXECUTE on sde.ST_SRID to app_user;
grant EXECUTE on sde.ST_STARTPOINT to app_user;
grant EXECUTE on sde.ST_SURFACE to app_user;
grant EXECUTE on sde.ST_SYMMETRICDIFF to app_user;
grant EXECUTE on sde.ST_TOUCHES to app_user;
grant EXECUTE on sde.ST_TRANSFORM to app_user;
grant EXECUTE on sde.ST_TYPE_EXPORT to app_user;
grant EXECUTE on sde.ST_TYPE_USER to app_user;
grant EXECUTE on sde.ST_TYPE_UTIL to app_user;
grant EXECUTE on sde.ST_UNION to app_user;
grant EXECUTE on sde.ST_VERIFY to app_user;
grant EXECUTE on sde.ST_WITHIN to app_user;
grant EXECUTE on sde.ST_X to app_user;
grant EXECUTE on sde.ST_Y to app_user;
grant EXECUTE on sde.ST_Z to app_user;
grant EXECUTE on sde.SVR_CONFIG_UTIL to app_user;
grant SELECT on sde.TABLE_ID_GENERATOR to app_user;
grant SELECT on sde.TABLE_LOCKS to app_user;
grant SELECT on sde.TABLE_REGISTRY to app_user;
grant UPDATE on sde.USER_ST_GEOMETRY_COLUMNS_V to app_user;
grant SELECT on sde.USER_ST_GEOMETRY_COLUMNS_V to app_user;
grant INSERT on sde.USER_ST_GEOMETRY_COLUMNS_V to app_user;
grant DELETE on sde.USER_ST_GEOMETRY_COLUMNS_V to app_user;
grant UPDATE on sde.USER_ST_GEOM_INDEX_V to app_user;
grant SELECT on sde.USER_ST_GEOM_INDEX_V to app_user;
grant INSERT on sde.USER_ST_GEOM_INDEX_V to app_user;
grant DELETE on sde.USER_ST_GEOM_INDEX_V to app_user;
grant SELECT on sde.VERSION to app_user;
grant SELECT on sde.VERSIONS to app_user;
grant SELECT on sde.VERSION_ID_GENERATOR to app_user;
grant EXECUTE on sde.VERSION_USER_DDL to app_user;
grant EXECUTE on sde.VERSION_UTIL to app_user;
grant SELECT on sde.XML_COLUMN_ID_GENERATOR to app_user;
grant SELECT on sde.XML_DOC1_ID_GENERATOR to app_user;
grant SELECT on sde.XML_DOC2_ID_GENERATOR to app_user;
grant SELECT on sde.XML_DOC3_ID_GENERATOR to app_user;
grant SELECT on sde.XML_DOC4_ID_GENERATOR to app_user;
grant SELECT on sde.XML_INDEX_ID_GENERATOR to app_user;
grant SELECT on sde.XML_TAG_ID_GENERATOR to app_user;
grant EXECUTE on sde.XML_UTIL to app_user;