When revoking select privileges on a versioned feature class, the select privileges are not revoked from the versioned table's adds and deletes tables

Description

When revoking privileges on a versioned feature class, the select privileges are not revoked from the versioned table's adds and deletes tables.

Even though the privileges are not revoked from the adds and deletes tables, users will not be able to select or edit the versioned table with ArcGIS. However, the users will continue to have access to the tables directly with SQL. This may be considered a security risk.

Cause

The cause of the problem resides in the ArcSDE function, which revokes privileges from objects. The adds and deletes tables are mistakenly skipped when privileges are revoked.

Workaround

1. Identify the versioned table's registration_id value in SQL*Plus. In the following example, the versioned table is named gas_mains.
   
   

   Code:
   SQL> SELECT registration_id FROM sde.table_registry WHERE table_name = 'GAS_MAINS';
   
   REGISTRATION_ID
   ---------------
                45

   
   
2. Explicitly revoke the SELECT privilege from the adds and deletes tables for the given user.
   
   

   Code:
   SQL> REVOKE SELECT ON a45 FROM brent;
   
   Revoke succeeded.
   
   SQL> REVOKE SELECT ON d45 FROM brent;
   
   Revoke succeeded.