topic Re: Python script to list Privileges in Geodatabase by Feature Class in Geodatabase Questions

Python script to list Privileges in Geodatabase by Feature Class

ShariF — Wed, 02 Aug 2023 13:58:34 GMT

Hello.

I am having no luck finding a python snippet or example of how to list out privileges for the geodatabase. ChatGPT hasn't provided accurate coding either. If anyone can share how to do this then I would be very appreciative. We use SQL server.

Thank you.

Re: Python script to list Privileges in Geodatabase by Feature Class

Bud — Thu, 03 Aug 2023 13:37:28 GMT

For anyone using Oracle, here's a post that uses a SQL query to list table privileges:
How to audit table permissions as a non-DBA

select table_name, grantee , 
        max(sel) sel, max(ins) ins , max(upd) upd, max(del) del,
        case when max(other_privs) is not null then 'other' end other_privs,
        listagg(other_privs,',') within group (order by other_privs) as list_of_other_privs
 from 
 (select table_name, grantee, 
        case when privilege='SELECT' then 'y' end sel, 
        case when privilege='INSERT' then 'y' end ins, 
        case when privilege='UPDATE' then 'y' end upd, 
        case when privilege='DELETE' then 'y' end del,
        case when privilege not in ('SELECT', 'INSERT','UPDATE','DELETE') then privilege end other_privs 
   from user_tab_privs
 ) 
  group by table_name, grantee 
  order by table_name, grantee

You could make a similar post (but for SQL Server) on Stack Overflow or DBA Stack Exchange, and likely get a good answer.

Re: Python script to list Privileges in Geodatabase by Feature Class

MarceloMarques — Thu, 03 Aug 2023 16:06:21 GMT

You can use SQL Server Management Studio to see the permissions.
Example:

--Verify role permissions
select dp.NAME AS principal_name,
dp.type_desc AS principal_type_desc,
o.NAME AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
left OUTER JOIN sys.all_objects o
on p.major_id = o.OBJECT_ID
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
where dp.NAME = 'gis_sde_owner'
GO

--Verify User permissions
select USER_NAME(p.grantee_principal_id) AS principal_name,
dp.type_desc AS principal_type_desc,
p.class_desc,
OBJECT_NAME(p.major_id) AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
where USER_NAME(p.grantee_principal_id) = 'sde'
GO

I hope this helps.

Re: Python script to list Privileges in Geodatabase by Feature Class

MarceloMarques — Thu, 03 Aug 2023 19:07:19 GMT

@ShariF - see this python script example, it might give you some ideas.
https://gis.stackexchange.com/questions/73991/checking-current-user-privileges-for-arcsde-dataset-with-arcpy
This article is quite old though. But take a look at the arcpy API documentation, it might have some new method available.
https://developers.arcgis.com/python/api-reference/

If you do not find what you are looking for then I encourage you to open a support ticket with Esri Technical Support.

Re: Python script to list Privileges in Geodatabase by Feature Class

ShariF — Mon, 07 Aug 2023 13:39:49 GMT

Thank you. I'm a newbie with SQL server studio and will give this a try.

Re: Python script to list Privileges in Geodatabase by Feature Class

ShariF — Mon, 07 Aug 2023 13:43:34 GMT

Thank you. I found that article too but had no luck getting it to work. Maybe with arcpy.ARCSDESQExecute and the previous answer provided I can get something working.