How To: Grant all privileges to the SDE user on the spatial index table of a versioned Oracle Spatial table
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.
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.
Instructions provided describe how to grant all privileges to the SDE user on the spatial index table of a versioned Oracle Spatial table.
It is necessary to determine the name of the spatial index table, register the feature class as versioned, grant the necessary privileges on the spatial index table to the SDE user, and verify the privileges were granted.
The table must already be registered with the geodatabase as a feature class before it can be registered as versioned. In the steps below, the business table of the feature class is MEX_STATE_SDO.
- 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:
SELECT I.TABLE_NAME, U.SDO_INDEX_NAME, U.SDO_INDEX_TABLE
FROM USER_SDO_INDEX_METADATA U,
WHERE I.INDEX_NAME = U.SDO_INDEX_NAME
AND I.TABLE_NAME(+) = 'MEX_STATE_SDO';
- 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.
- 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$.
GRANT SELECT, INSERT, UPDATE, DELETE
ON MDRT_B18F$ TO SDE;
- 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.
WHERE TABLE_NAME = 'MEX_STATE_SDO'
AND OWNER = 'SDETEST';
- 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.
SELECT OWNER, TABLE_NAME, PRIVILEGE
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.