Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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])

If anybody has any ideas of how to construct the missing "CheckPrivileges", I would love to hear them. Apologies in advance if this isn't enough detail, or my terms aren't quite correct, as I'm new to ArcSDE, and also to this site itself.

Cheers.

share|improve this question
add comment

2 Answers 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)
share|improve this answer
    
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 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 at 9:03
add comment

My eventual solution was to avoid the ArcSDE interface and arcpy and use cx_Oracle, as per answers to a question here.

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.