Auditing Tools for SDE Users and Privileges

130
4
Jump to solution
11-08-2022 07:53 AM
Labels (3)
NoahMiller
New Contributor II

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.

1 Solution

Accepted Solutions
MarceloMarques
Esri Regular Contributor

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.

| Marcelo Marques | Principal Product Engineer | Esri |
| OCP - Oracle Certified Professional |

View solution in original post

0 Kudos
4 Replies
MarceloMarques
Esri Regular Contributor

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.

| Marcelo Marques | Principal Product Engineer | Esri |
| OCP - Oracle Certified Professional |
0 Kudos
NoahMiller
New Contributor II

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

0 Kudos
MarceloMarques
Esri Regular Contributor

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

 

| Marcelo Marques | Principal Product Engineer | Esri |
| OCP - Oracle Certified Professional |
0 Kudos
CraigSwadner
Occasional Contributor

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

0 Kudos