Select to view content in your preferred language

Python script to list Privileges in Geodatabase by Feature Class

1263
5
08-02-2023 06:57 AM
Labels (2)
ShariF
by
Regular Contributor

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. 

5 Replies
Bud
by
Esteemed Contributor

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

Bud_1-1691069793302.png

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

MarceloMarques
Esri Regular Contributor

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.

| Marcelo Marques | Esri Principal Product Engineer | Cloud & Database Administrator | OCP - Oracle Database Certified Professional | "In 1992, I embarked on my journey with Esri Technology, and since 1997, I have been working with ArcSDE Geodatabases, right from its initial release. Over the past 32 years, my passion for GIS has only grown stronger." | “ I do not fear computers. I fear the lack of them." Isaac Isimov |
ShariF
by
Regular Contributor

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

 

0 Kudos
MarceloMarques
Esri Regular Contributor

@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-wi...
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.

| Marcelo Marques | Esri Principal Product Engineer | Cloud & Database Administrator | OCP - Oracle Database Certified Professional | "In 1992, I embarked on my journey with Esri Technology, and since 1997, I have been working with ArcSDE Geodatabases, right from its initial release. Over the past 32 years, my passion for GIS has only grown stronger." | “ I do not fear computers. I fear the lack of them." Isaac Isimov |
ShariF
by
Regular Contributor

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. 

0 Kudos