Revoking access to table/feature class owned by one SDE user from another SDE user

Question

I am trying to make some static tables "invisible" owned by one SDE user from another SDE user (two GIS editor users) but cannot seem to do this. I use permissions "revoke edit and view" in ArcCatalog on the table to the other SDE user, and while it seems to execute, the table is still visible when I connect as that user.

I try the equvalent operation using the sdetable - o revoke... command - same thing.

The only think I can think is the SELECT ANY TABLE oracle system priviledge that I was told *MUST* be granted to any SDE user (along with the dozens of other privs) to use the ESRI environment (versioning...etc) is overriding any object level access you wish to control. Is this correct? If so, could the ESRI functionality be maintained without this one priv and using only the others (SELECT ANY SEQUENCE,..etc)? Or could this finer grain access and visibility be used for static readers only and all GIS editors would have to see everything in the instance?

-Jim

VinceAngelo · Answer

The notes section on the doc states:
The SELECT ANY TABLE privilege is required if using autoregistration with Oracle Spatial. 
If not using autoregistration, the SELECT ANY TABLE privilege is not needed.

AUTOREG is disabled by default (and shouldn't really be used), so maybe the doc shouldn't list 
this seldom-needed right.
 
When it comes down to it, there are four different sets of permissions involved with ArcSDE:
1) Privs required by SDE user to CREATE or UPGRADE ArcSDE within Oracle
2) Privs required by SDE to run Application Server service
3) Privs required by read/write data owner users
4) Privs required by read-only browse users
 
You will not get a data owner (3) account to operate correctly by assigning SDE application 
service (2) privs. Granting CREATE/UPGRADE privs (1) to a data owner as a workaround is 
bound to cause difficulties. 
 
Roles are an excellent way to manage the "standard" permissions given to different classes
of users, though I admit I don't bother with an SDEUPGRADE_ROLE, and just grant DBA, 
upgrade, revoke DBA, the re-grant the SDESERVICE_ROLE (because revoking DBA removes
overlapping privs like CREATE SESSION).
 
The SDE user should not ever be used to create spatial data.  Instead it (and its tablespace)
should be reserved for ArcSDE instance administration.
 
- V

RobertHu · Answer

Hi Jim,

Are you sure "SELECT ANY TABLE" is necessary for "a" SDE user. I remember it is necessary for "the" SDE user while doing upgrade or so. Other users need "SELECT" (better grant it through role), not the "SELECT ANY TABLE".

See the link,

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/User_permissions_for_geodatabases_in_Oracle/002n0000002v000000/

JamesFox1 · Answer

I have tested this out and found when revoking all of these oracle privs except for CONNECT that ArcGIS editing functions are diminished. Specifically, when I try to import one or more excel spreadsheets into database tables in ArcCatalog I get a ArcDesktop 999999 error (generic error, contact your administrator...) for an SDE user.  However, this import works fine when I add all those Oracle priviledges back to this user. I should try this out with FC moves and ArcMap versioning editing since I am unsure of this behavior with these activities.

Is there a chart somewhere outlining the specific ArcDesktop editing ability and the exact set of Oracle system privaledges needed to perform these actions? The environment is complex since ArcGIS SDE uses sequences and temp tables and the like.

My point is when all these priviledges are in place that SDE user can see all the user schemas      in the entire Oracle database, which is not what we want.

-Jim