You are here

Use Python to Automate
Esri GDB Permissions

As most of you know, when you make a data model change within your Esri geodatabase, it often invalidates the underlying SQL Server or Oracle permissions on the class or dataset within the geodatabase. Sometimes this manifests itself in unexpected ways.

For example, if I add a new field to the electric SWITCH feature class, sometimes it may invalidate the permissions on a related multiversion view and sometimes on the entire electric dataset.

For these inconsistent reasons, we always recommend resetting the related permissions via ArcCatalog after making any data model changes. This proactive approach ensures there are no issues or downtime in the system.

Set Esri Permissions

After recently making some changes for one our clients, the client mentioned that due to the large number of database roles they have, it was taking around an hour to reset their electric-related permissions in each affected environment. This was a major time-suck and was also prone to user error since it's a very manual process to set the permissions within ArcCatalog.

So I volunteered to write a python script that could be used to automate setting the permissions in each environment. We've written similar scripts before but I had never standardized an approach that would be reusable for SSP. 

I googled around a bit and found plenty of examples on using python to set Esri permissions - but not a comprehensive script. So I created one, and am happy to share the pattern with the community!

My pattern was to create two python files. The first file is called SetPermissions.py and contains reusable methods for setting permissions on a "type" of object. For example, you would create a method that is dedicated to setting permissions on your electric objects. This method might look like this (please copy and paste entire code snippet out of browser to view in full):

import arcpy

# this method sets standard role-based permissions on the passed connection file and table/class/dataset
def SetElectricPermissionsOnObject(connectionFile, arcTable):
try:
print ""
arcpy.ChangePrivileges_management(connectionFile + "\\" + arcTable, "electric_editor", "GRANT", "AS_IS")
print "Set electric_editor privs for " + arcTable
arcpy.ChangePrivileges_management(connectionFile + "\\" + arcTable, "electric_viewer", "GRANT", "GRANT")
print "Set electric_viewer privs for " + arcTable
arcpy.ChangePrivileges_management(connectionFile + "\\" + arcTable, "gis_admins", "GRANT", "GRANT")
print "Set gis_admins privs for " + arcTable
print ""

return
except:
print arcpy.GetMessages(2)

So within the electric permission method above, I am setting permissions for the three example DB roles including electric_editor, electric_viewer, and gis_admins. The electric_editor and gis_admins both get "select + update" whereas the electric_viewer role only gets select permissions.

This reusable python file would then be expanded with reusable methods for all the other data types which might include gas, water, telecom/fiber, and even the MM_* configuration tables.This file essentially becomes a library for setting permissions. The role names would need to be updated to your environment.

Next we will create a second python file that will consume the first reusable python file and will call the methods for each GIS object that needs to have its permissions set. You can think about this in a very similar manner to using ArcCatalog to set permissions.

For example we can call the SetElectricPermissionsOnObject method for the entire ElectricDataset and not for each feature class within the dataset. But we do need to call it individually for any related electric tables that exist outside the dataset. An example of consuming the above method would look like this (please copy and paste entire code snippet out of browser to view in full):

import arcpy
import SetPermissions

# Set variables for .sde file
connectionFile = "C:\Users\sperry\AppData\Roaming\ESRI\Desktop10.0\ArcCatalog\electric@MySdeGdb.sde"

# call the reusable methods to set permissions for each GIS object
try:
print "----STARTING PROCESS-----"

SetPermissions.SetElectricPermissionsOnObject(connectionFile, "Minerville.Electric.ElectricDataset")
SetPermissions.SetElectricPermissionsOnObject(connectionFile, "Minerville.Electric.TransformerUnit")
SetPermissions.SetElectricPermissionsOnObject(connectionFile, "Minerville.Electric.FuseUnit")
SetPermissions.SetElectricPermissionsOnObject(connectionFile, "Minerville.Electric.ConductorInfo")
SetPermissions.SetElectricPermissionsOnObject(connectionFile, "Minerville.Electric.CircuitSource")

print "----PROCESS COMPLETE-----"

except:
print arcpy.GetMessages(2)

First note that I am importing the SetPermissions python file with all of my reusable methods. Its important to note that this python file should be saved in the same directory as the SetPermissions.py file.

Then you can see that I am setting up a variable called connectionFile that has a hard-coded string to an SDE connection file on my machine. That connection file has been created via ArcCatalog and has all the connection properties needed to connect to my SQL Server geodatabase, including a saved username/password for the electric data owner.

Then I am calling the reusable method SetElectricPermissionsOnObject for the ElectricDataset and several electric tables.

When you combine the logging from this method along with the logging in the reusable methods, you get a nicely-output log file documenting all the permissions you've set via the script.

Python Permissions Log

All you have to do is load the second python script into the Python IDLE window and run it. Then sit back and watch your permissions be set! Much better than doing it manually, and it takes all the guess work out of the manual clicks you'd be doing if you set the permissions one by one.

To round out this post, I created example python scripts based on the above pattern using three different connections for electric, gas, and water, to show an example of scripting permissions across datasets/commodities. This should get you going on your geodatabase regardless of the data you manage. You can download the example python here.

I hope this helps you out and saves you some time! If you want to take this to the next level, be sure to beef up your python code using Jeff Buturff's posts, Python Best Practices #1 and Python Best Practices #2 to add better logging, configuration and more!

Author Information

  • Skye Perry

    Skye Perry

    Skye Perry is a CEO for SSP Innovations and has provided technical architecture, development, and management solutions for GIS-centered implementations since 2000. Skye's roots tie back to Convergent Group, Miner & Miner, and Enspiria Solutions where he has focused on implementing Esri GIS customizations and system integrations. Today Skye is still involved on a number of SSP's project implementations and supports technical marketing in addition to leading the company.

    See all items created by this author >

    Connect with me on:

Comments

When I went to run a script to perform routine SDE updates with the 10.3 desktop version of ArcGIS, the script crashed at my "change privileges" section.

Broken code: arcpy.ChangePrivileges_management(GISADM_B_D, "SDESEL, GISAPP, WSDSEL", "GRANT", "")

Replaced w: arcpy.ChangePrivileges_management(GISADM_B_D, "SDESEL", "GRANT", "")
arcpy.ChangePrivileges_management(GISADM_B_D, "GISAPP", "GRANT", "")
arcpy.ChangePrivileges_management(GISADM_B_D, "WSDSEL", "GRANT", "")

Hope this helps someone else . . .

Thanks a ton for the update for 10.3! Much appreciated. 

Having the features in a feature dataset should mean you only need to apply the permissions to the dataset. How would you alter the script to handle feature datasets and more mutliple permission groups?

You are correct. When features exist in a feature dataset you set the permissions at the dataset level. Most of our data is organized that way and the script is already set up to work at the dataset level. You just submit in the name of the dataset when calling the reusable method. And it also already handles multiple permissions... basically it has a reusable method and you are passing in the individual object (dataset, class, or table) and the group to provide permissions to. The script already handles the rest. I hope this clarification helps.

Add new comment