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.

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\[email protected]"

# 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.

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!