Auditing Tools for SDE Users and Privileges

[NoahMiller]

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.

[MarceloMarques · Accepted solution]

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.

[NoahMiller]

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

[MarceloMarques]

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

[CraigSwadner]

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