Grant all privileges to the SDE user on the spatial index table of a versioned Oracle Spatial table

Summary

When registering a table as versioned, the SDE user is granted privileges on the base table and other side tables to allow the SDE user to compress the geodatabase.
However, when registering an Oracle Spatial table as versioned in ArcSDE 8.3, there are certain privileges on the spatial index table that need to be granted to the SDE user.

Note:
This was corrected in ArcSDE 9.0. It is no longer necessary to grant the SDE user privileges on the spatial index of a versioned Oracle Spatial table in ArcSDE 9.0 or greater.

Procedure

1. Identify the name of the spatial index table of the business table. The following is an example SQL statement for finding the name of the spatial index table on the MEX_STATE_SDO business table:
   

   Code:
   SELECT I.TABLE_NAME, U.SDO_INDEX_NAME, U.SDO_INDEX_TABLE
     FROM USER_SDO_INDEX_METADATA U,
          USER_INDEXES I
     WHERE I.INDEX_NAME = U.SDO_INDEX_NAME
       AND I.TABLE_NAME(+) = 'MEX_STATE_SDO';

   
   
2. Register the feature class as versioned. This can either be done in ArcCatalog by right-clicking the table and selecting Register as Versioned, or at a command prompt with the sdetable –o alter_reg command. See the ArcSDE Administration Command Reference provided with ArcSDE for details on using the sdetable command.
3. Grant the SDE user SELECT, INSERT, UPDATE, and DELETE privileges on the spatial index table as demonstrated in the example below. Be sure to substitute the name of your spatial index table for the spatial index table in the example, MDRT_B18F$.
   

   Code:
   GRANT SELECT, INSERT, UPDATE, DELETE
   ON MDRT_B18F$ TO SDE;

   
   
4. Find the registration ID for the business table by executing a SQL statement similar to the one below. Use the registration ID when checking that the proper privileges were granted on the versioned delta tables.
   
   Be sure to replace the table and owner names to match your table and owner names.
   

   Code:
   SELECT REGISTRATION_ID 
     FROM SDE.TABLE_REGISTRY 
     WHERE TABLE_NAME = 'MEX_STATE_SDO'
       AND OWNER = 'SDETEST';

   
   
5. Display the object privileges granted to the SDE user on the component tables of the feature class to be sure the necessary privileges have been granted. Check the base table, the adds table (A<registration_id>), the deletes table (D<registration_id>), and the spatial index table.
   

   Code:
   SELECT OWNER, TABLE_NAME, PRIVILEGE
   FROM USER_TAB_PRIVS
   WHERE GRANTEE = 'SDE'
     AND TABLE_NAME IN('MEX_STATE_SDO','A172','D172','MDRT_B18F$')
   ORDER BY TABLE_NAME, PRIVILEGE;

   
   Remember to substitute your business table name, adds and deletes table names, and the spatial index table name as determined in Step 2.
   
   The SDE user should now have SELECT, INSERT, DELETE, and UPDATE privileges on all four tables.