# Checking current user privileges for ArcSDE dataset with ArcPy

I am managing data distribution via an ArcSDE Oracle gdb to a number of users (schemas?), each of which can only have read only access to datasets they have requested access to, and have then been licensed to see - due to the confidential nature of the data.

Currently I keep these privileges in a spreadsheet as 0, 1, or 2 for each user, and with a Python program, I then update each dataset in the SDE to match the privileges in the spreadsheet using the ArcGIS "Change Privileges" tool.

However, this means I am currently running the change privileges on all SDE datasets, when in reality only a handful may actually need be changed, which is then taking a lot longer than it really needs to. So I am looking for a way to find out what privileges each dataset already has, to know if they do need to be updated or can be skipped, probably with a custom function (nothing Oracle please), as in a simplified example of my existing code below:

import arcpy

#  New function here
def CheckPrivileges(sde_fc, user):
etc..
return privileges_value  #  e.g. 'Select'

#  Input SDE and spreadsheet paths
sde = r'SDEConnectionPath'

#  Dictionary to convert 0, 1, 2 values from spreadsheet to privilege lists
new_privileges = {"0": ["REVOKE",  "REVOKE"], "1": ["GRANT",  "AS_IS"], "2": ["GRANT",  "GRANT"]}

#  A second dictionary to check against the returned value from the CheckPrivileges function,
#  but using the same values from the spreadsheet e.g.
privileges_check = {"0": "", "1": "Select", "2": "All"}  #  (This is almost certainly incorrect)

#  In the spreadsheet, each user has a column with the 0, 1, 2 privilege values e.g.
user = 'USER1'

#  Search cursor with each dataset and the users privileges.
rows = arcpy.da.SearchCursor(in_xl, ['SDE_DATASET', user])
for row in rows:

#  Path to SDE dataset having privileges altered.
sde_fc = sde + '\\' + row[0]

#  Pulls out the correct privilege values from the dictionaries.
user_privs = new_privileges[str(row[1])]

check_privs = privileges_check[str(row[1])]

#  This is where I would call the new function I need e.g.
current_privs = CheckPrivileges(sde_fc, user)

#  And then check against what it should be according to the dictionary.
if current_privs != check_privs:

#  If not equal, then amend privileges, otherwise continue to the next row
arcpy.ChangePrivileges_management(sde_fc, user, user_privs[0], user_privs[1])


How do I construct the missing CheckPrivileges function?

• For what it's worth, I no longer use a spreadsheet for this, instead displaying the current privileges for a user or file with a Python toolbox, and checking on/off the ones I wish to change. The accepted solution below will enhance this. – ssast Feb 23 '16 at 10:20

I have found that ArcSDESQLExecute allows a query against ALL_TAB_PRIVS while using an existing SDE connection.

This sample implementation:

def get_privileges(sde, owner, table):
privileges  = {"edit": set(), "view": set()}
command = """
select PRIVILEGE, GRANTEE
from ALL_TAB_PRIVS
where TABLE_NAME = '{0}'
and TABLE_SCHEMA = '{1}'""".format(table, owner)
executor = arcpy.ArcSDESQLExecute(sde)
result = executor.execute(command)
for row in result:
if row[0] == "SELECT":
elif row[0] in ("UPDATE", "INSERT", "DELETE"):
return privileges


Can be used like this:

sde = r"C:\path\to\connection.sde"
owner = "OWNER_NAME"
table = "TABLE_NAME"

get_privileges(sde, owner, table)

# output:
{
'edit': set([u'USER1']),
'view': set([u'USER1', u'USER2', u'USER3'])
}

• Interesting. I believe this would be a better solution (for one, entering a password isn't required so long as the .sde file is present). However, at "executor.execute(command)" it's returning True as the result. Any idea why that would be? – ssast Feb 22 '16 at 15:56
• @ssast This sample only covers the Happy Path. If the SQL returns zero rows ArcSDESQLExecute.execute() will return True or None to indicate success or failure. Are there any typos in the owner, or table parameters? – Kelly Thomas Feb 23 '16 at 3:48
• Found the problem, the table name needs to be all upper case to be recognised by the query, as this is how they appear in the ALL_TAB_PRIVS table, even if when viewed in ArcCatalog the table name has a difference case. – ssast Feb 23 '16 at 10:12
• A piece of the original question that hasn't been addressed is identifying the current user. There are multiple scenarios in which you might need to extract the user name from the connection itself. You can accomplish this by executing a query through arcpy.ArcSDESQLExecute. For Oracle, the query would be SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL. It returns a string. – Tom Jun 20 '17 at 23:48
import arcpy, os, pyodbc
def GetPrivileges(table):
cursor = cnxn.cursor()
cursor.execute("EXEC sp_helprotect '" + table + "'")
rows =cursor.fetchall()
privileges = []
for row in rows:
privileges.append(row.Grantee)
return privileges

arcpy.env.workspace =sdecon
for fc in arcpy.ListFeatureClasses():
desc = arcpy.Describe(fc)
table = desc.BaseName
print table, GetPrivileges(table)

• Important to note that installing a 3rd party library, pyodbc (code.google.com/p/pyodbc), is required in order to run this code. – Alex Tereshenkov Jul 2 '14 at 12:11
• I couldn't use this code immediately with the Oracle GDB I work with, as I'm not sure what to enter as the "DRIVER". However, by thinking outside of an ESRI/arcpy solution, this did set me on the right track to find a solution for my specific case, thank you. – ssast Jul 8 '14 at 9:03

My eventual solution was to avoid the ArcSDE interface and arcpy and use cx_Oracle, as per answers at Python script: Access data from databases.

The below class returns privileges on an admin/owner connection for either tables (returning a list of users with access) or users (returning a list of tables they can access).

I imagine there are more elegant ways to do this, but with my limited knowledge of this side of GIS and RDBMS in general, this works fine for me. With a few adjustments to my original script, this is sufficient to identify which privileges need to be updated, and avoid the need to update all every time a change is made.

import cx_Oracle

class GetPrivileges(object):
cursor = db.cursor()
cursor.execute("select * from USER_TAB_PRIVS") # Alternatively, ALL_TAB_PRIVS
rows = cursor.fetchall()
self.privileges = []
for row in rows:
self.privileges.append(row)

def check_user(self, user):
user_privs = []
for row in self.privileges:
if user in row:
user_privs.append(row[2])
return user_privs

def check_table(self, table):
tbl_privs = []
for row in self.privileges:
if table in row:
tbl_privs.append(row[0])
return tbl_privs


To call this in my script, it'd be similar to the below:

privs = GetPrivileges("ADMIN", "1234", "ABC.world")


I'd then compare all tables and users against values in the input spreadsheet file. Note that the returned table names don't include Feature Datasets and suchlike, rather the tables within them. Also, a number of tables are returned that are not visible through ArcCatalog, so filter them out to avoid making unintentional changes to those tables.