topic Re: Auditing Tools for SDE Users and Privileges in Data Management Questions

Auditing Tools for SDE Users and Privileges

NoahMiller — Tue, 08 Nov 2022 15:53:33 GMT

Client is asking for a an audit of users and permissions on 2 of their databases. there are 447 items within and I'm looking for a way to access user/privilege information via a script to generate a report.

Re: Auditing Tools for SDE Users and Privileges

MarceloMarques — Tue, 08 Nov 2022 16:36:27 GMT

For Enterprise Geodatabases on Oracle / SQL Server / PostgreSQL use the RDBMS Views to list the permissions granted for each user that needs to be audited, an experienced Database Administrator can create the report you are looking for very easy. For more information search the RDBMS documentation. I hope this helps.

Re: Auditing Tools for SDE Users and Privileges

NoahMiller — Tue, 08 Nov 2022 16:45:24 GMT

Ok, thank you. I'll reach out to our DBA.

Re: Auditing Tools for SDE Users and Privileges

CraigSwadner — Thu, 10 Nov 2022 15:42:47 GMT

This will give you the typical esri permission by user / object (leave out the where clause if you want everything)

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 permission_name in ('select', 'delete', 'update', 'insert')
order by principal_name

Re: Auditing Tools for SDE Users and Privileges

MarceloMarques — Thu, 10 Nov 2022 17:48:01 GMT

--ORACLE-- Permissions Audit Example:

--ROLES--

SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'SDE';

--SYS PRIVILEGES--
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'SDE';

--GEODATABASE USERS--

SELECT DISTINCT(OWNER) FROM SDE.TABLE_REGISTRY ORDER BY OWNER;

--ROLES--

SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE IN (SELECT DISTINCT(OWNER) FROM SDE.TABLE_REGISTRY) ORDER BY 1,2;

--SYS PRIVILEGES--
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE IN (SELECT DISTINCT(OWNER) FROM SDE.TABLE_REGISTRY) ORDER BY 1,2;

--OBJECT PRIVILEGES--

SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE IN (SELECT DISTINCT(OWNER) FROM SDE.TABLE_REGISTRY) ORDER BY 1,2;

--ROLE PRIVILEGES

SELECT * FROM ROLE_TAB_PRIVS WHERE OWNER IN (SELECT DISTINCT(OWNER) FROM SDE.TABLE_REGISTRY) ORDER BY 1,2;

--ROLE SYS PRIVILEGES

SELECT * FROM ROLE_SYS_PRIVS
WHERE ROLE IN
(
SELECT DISTINCT(ROLE) FROM ROLE_TAB_PRIVS WHERE OWNER IN (SELECT DISTINCT(OWNER) FROM SDE.TABLE_REGISTRY)
)
ORDER BY 1,2;

SELECT * FROM ROLE_SYS_PRIVS ORDER BY 1,2;

I hope this helps with the Permissions Audit.