Script setting insert and select only on featureclasses

[AlexanderGray]

If you right click on an unversioned featureclass in an sde geodatabase in arccatalog, you can get the manage/privilege menu item.  This opens a form where you can see and edit the privileges of the feature class for each user or role.  You can turn on or off Select, Insert, Update and Delete each independently of each other.  I need to script this up for a bunch of featureclasses.  The Change Privileges tool in the Geodatabase Administration only allows to grant, as is or revoke Select and Insert/update/delete all lumped into "Edit".  That is not good enough, I want to grant Select and Insert on a bunch of featureclasses.  I can do it one FC at the time  which is a huge pain as every time we roll out a new DB (every 3 months or so), I need to do all this manual work, is there a python scripting way to do this?

[RussellBrennan · Accepted solution]

Hi Alexander,

There is no way to do this using Arcpy and I would caution doing this in the DBMS using DBMS tools as you need to know exactly what tables need privileges to be applied.

The Change Privileges tool was implemented at a time when ArcMap/ArcCatalog context menus only allowed you to apply View (select) or Edit (insert, update, delete) privileges and unfortunately was not updated at 10.1 when we replaced the privileges dialog to allow you to set these individually for non-versioned data. This is the first time we have had a customer request for this specific functionality to be scripted and have therefore not put effort into developing this functionality.

I would suggest entering this on ideas.arcgis.com to see if you can get other members of the community to support this. With enough demand I think this would be a good idea to move forward on.

Russell

[JoshuaBixby]

As Russell Brennan‌ points out, this isn't currently possible with ArcPy.  ArcGIS 10.3 adds 9 tools to the Geodatabase Administration toolset, but sadly, this still won't be possible natively through ArcPy or any Geoprocessing Tools with the new release.

This is yet another reason retiring the SDE command line tools is premature.  The use cases just keep piling up in the forums (sorry, GeoNet) where SDE command line tools work and nothing else is available, but Esri is doing their best David Farragut impersonation with this one.  Unfortunately for those customers needing to script advanced geodatabase administration tasks, this isn't Mobile Bay and the mines will go off and cause damage.

The sdelayer command should work for what you need, at least for the short while it will be around:

sdelayer -o {grant | revoke} -l <table,column> -U <user>

-A <SELECT,UPDATE,INSERT,DELETE> [-s <server_name>]

[-i {<service> | <port#> | <direct connection>}]

[-D <database_name>] -u <DB_user_name>

[-p <DB_user_password>] [-I] [-q]

Starting with ArcGIS 10.1, Esri decided to stop publishing the reference documentation for the tools online.  If you install the tools, the reference documentation is there.  Since the tools haven't changed much over the years, I usually just look up information in the ArcGIS 10.0 ArcSDE Administration Command Reference.

[AlexanderGray]

Well not what I wanted to hear but technically correct... 

Ya it seems like more and more I moving to Oracle SQL and leaving behind the esri tools.  It is all SQL under the covers so that is more and more the only way to go.  I have always found ArcPy or GP to be a toy compared to AO.  Kind of neat and demos well but not practical for a real application, too slow or too limited.  Now I am finding the same with ArcPy compared to the SDE C API (which is deprecated) or PL/SQL or Java stored procedures.

[AlexanderGray]

I used a call to Oracle to set the insert for the role like this, it works fine.  The tables are registered but not versioned.

sdeConn = arcpy.ArcSDESQLExecute(DESTINATIONDB)

for InsertPriv in SEL_INSERT_LIST:

        print("Grant Insert: " + InsertPriv)

        sql = "GRANT INSERT ON {0} TO USER_ROLE".format(InsertPriv)

        sdeRet = sdeConn.execute(sql)

        print (sdeRet)