SDE Permissions for users

[MartinAshmore]

Hi,

I have been migrating our SDE database to a new server and performing a few upgrades, mainly moving from SQL Server 2005 and SDE 9.2 to SQL Server 2008 and SDE 9.3.1.

I have recreated all the users, moved the data over using arc catalog and now am now getting ready to set permissions for the various users so that they can view the appropriate data.

As there are quite a lot of users and loads of layers I was thinking about doing this at the database level, rather than manually going though them.

Has anyone ever done this? Any suggestions or pointers would be appreciated.

Thanks,
Martin

[LeoDonahue]

Hi,
As there are quite a lot of users and loads of layers I was thinking about doing this at the database level, rather than manually going though them.

What do you mean by manually going through them?  Do you mean assigning permissions via ArcCatalog by layer, by user?

[VinceAngelo]

Best practice is to create roles for the various flavors of table interaction, grant permissions
through ArcGIS to the roles, then use database tools to grant roles to the users.  While it is
possible to use database tools to  grant role accesses, you need to make sure the
associated tables are granted parallel permissions, and that is more easily performed
by ArcGIS.

- V

[MartinAshmore]

Hi,

Yes I mean by setting them in arc catalog.

I've created my users to replicate the original database, now I was hoping to be able to run some SQL to get a list of the users who have read access to certain layers and reengineer this to form SQL to grant access in the new database.

Vince are you saying this is a bad idea, as users need to be granted access to certain system tables (I assume all the tables with weird names in database e.g. bus_edit.i170 etc) as well as the actual tables that contain the layers themselves?

Cheers,

Martin

[LeoDonahue]

As Vince said, if you have that many users, it would be easier to create roles in SQL, add users to those roles in SQL, then use ArcCatalog to grant privileges to those roles.  And if the manual ArcCatalog process is repetitious, then use a model or write some python to do it.

If you choose to do this all in SQL, then when a user loads a new featureclass, you'll have to find a way to figure out what the F, S, I, adds, deletes, etc tables are associated to this featureclass and grant the appropriate user/role access to those objects.  That would get messy.

Some of this depends on how you create roles for users who want to simply read data.  I think it would be easier to create a generic account with read only permissions, and put that .sde file on your network somewhere people would have access to it, as opposed to creating a role in SQL and then needing to add 50+ different users to that role, and then adding permissions to that SQL role via ArcCatalog.

[MartinAshmore]

Hi Leo,

Thanks for the response. I like the idea of doing this with python.

In your response you mention:

figure out what the F, S, I, adds, deletes, etc tables are associated to this featureclass and grant the appropriate user/role access

I'm new to this side of SDE (desktop user before), what do you mean by F, S and I?

I've already created a bit of SQL that generates a list of layers that have certain levels of access for certain users.

I'll have a dabble and see if I can turn the SQL results into a txt file I can loop through using python to grant access as required.

I'm a bit of a noob so by the time I've done this I probably could have done it manually anyway:p

Thanks for your help.

Martin

[LeoDonahue]

I'm new to this side of SDE (desktop user before), what do you mean by F, S and I?

I mean this: http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/Feature_class_storage/002q000000700000...

[MartinAshmore]

Thats interesting reading, I'm glad I asked.

I've got my table lists and users now from our old sde server, I'm just having a nose at the python.

Thanks for the assistance Leo.

[MartinAshmore]

Hi,

This is the solution I ended up using:

1. Used some SQL to gather a list of layers from the previous server which the required user has select rights on.
2. Exported the results to a csv file.
3. Used a python script to run through the csv file layers list to set the required permissions using "ChangePrivileges_management"

Here is the SQL:

use sde
go

select distinct Layer = Object_Name(major_id)
from sys.database_permissions dp
join sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_id
JOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id
where state_desc like 'Grant%' and  permission_name = 'select' and grantee.name = 'ims_read' and not object_Name(major_id) like '[adfis][1-9]%'and not object_Name(major_id) like 'SDE_%'

Run this on the original server, you will need to change the database name and the user. Export the results of the query to a csv file.

Then run the following python script:

#Script to grant privileges on mass from one SDE user to another
#Author - Martin Ashmore
#Date - 19/12/2012


#Import the arcpy module containing Arc tools
import arcpy

#Import the time module to allow access
import time

#Set the user to who rights will be granted to
user ="ims_read"

#Set the workspace to be used by all tools - in this instance the sde connection that is granting access. The path to the file is accessed from properties of the connection in ArcCatalog
arcpy.env.workspace = r"C:\Documents and Settings\pdomja\Application Data\ESRI\Desktop10.0\ArcCatalog\DC gis_admin svgisdb2.sde"

#Create an error indicator, this will be set if an error is encountered processing the layers
err = 0

#Create an object to open the file containing the layers to be shared to the ims_read connection
inF=open(r"C:\temp\imsSelect.csv","r")

#Create an object to open a log file to hold the results of the processing
outF=open(r"C:\temp\grant_log.log", "w")

#Read the contents of the file from the file into a list comprised of strings. The splitlines() function removes end of carriage returns (\n)
layer = inF.read().splitlines()

#Close the file now the data has been read from the file
inF.close()

#Obtain time to be writtern to the log file

curTime = time.asctime(time.localtime(time.time()))

#Write preprocessing information into logfile

outF.write("***************************************************************\n")
outF.write("Processing of files at: "+curTime+".\n\n")



#Loop through the list, for each value grant permissions for ims_read to select the data
for v in layer:
    #error handling - try the following statement
    try:
        #use the arcpy tool ChangePrivileges to grant select permission for each object in the list
        arcpy.ChangePrivileges_management( v, user, "GRANT")
        #Write to screen layer when privileges successfully changed
        print "Processing layer : "+v
        #Write to log file layer when privileges successfully changed
        outF.write("Processing layer : "+v+"\n")

    #If an error occurs processing the current layer thenn echo out the Following statement
    except:
        #Write to screen if layer fails processing
        print "****Layer :"+v+", could not be processed.****"
        #Write to log file layer if layer fails processing
        outF.write("****Layer :"+v+", could not be processed.****""\n")
        #Set the error flag for later
        err = 1

#Loop ended

#Check whether any of the layers have failed processing and inform that the user
#Start If statement
#If error code is 1, report errors otherwise report processing sucessfully
if err == 1:
        print "*********************************************************"
        print "Process completed with errors at : "+curTime+"."
        print r"Consult processing log file: C:\temp\grant_log.log"
        outF.write("\n*********************************************************\n")
        outF.write("Process completed with errors at : "+curTime+".\n")
else:
        print "*********************************************************"
        print "Processing completed sucessfully at : "+curTime+"."
        outF.write("\n*********************************************************\n")
        outF.write("Processing completed sucessfully at : "+curTime+".\n")

#End of If statement

#Close the log file
outF.close()

#End of script

The python script will need to be configured to use your SDE database connection, SDE user that you wish to grant permissions to. The SQL results csv file needs to be placed at c:\temp, or the script repointed.

[MarcoBoeringa]

Good to see you choose to use the "arcpy.ChangePrivileges_management" tool instead of the unwieldy road of attempting to solve the issue straight at the database level using SQL, which would probably have ended up in a mess with wrong privileges set on crucial geodatabase tables. As Vince and Leo already said, having ArcGIS or arcpy manage all the SQL logic involved in geodatabase (privilige) management is best practice.