Checking current user privileges for ArcSDE dataset with ArcPy

Question

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'
in_xl = r'SpreadsheetSheetPath'

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

Answer 1

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":
            privileges["view"].add(row[1])
        elif row[0] in ("UPDATE", "INSERT", "DELETE"):
            privileges["edit"].add(row[1])
    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'])
}

Answer 2

import arcpy, os, pyodbc
def GetPrivileges(table):
   cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=servername;DATABASE=dbname;UID=user;PWD=password')
   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)

Answer 3

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):
    def __init__(self, admin, passw, tns):
        db = cx_Oracle.connect(admin, passw, tns) 
        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.