nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Encuentra más publicaciones etiquetadas con

Database Access

Databases

Python

Accepted answers

MMBR

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.

All comments

MMBR

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

NoahMiller

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

MMBR

Únete a la Conversación

Los miembros registrados pueden publicar, seguir actualizaciones y más. ¿Nuevo aquí? Regístrate gratis.

Enlaces Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.