SQL Query to get security details (such as Security roles, duties and privileges)
Views (191)
Maha Jamali
487
Like
Share
ReportToday, I will sharing out the list of queries which can used to get the security details.
It will cover following combinations of data.
- Security roles
- Security roles to duties
- Security roles with privileges
- Security role and duty combination along with privileges
Following list of tables are used in it.
- SECURITYROLES
- SECURITYOBJECTCHILDREREFERENCES
- SECURITYDUTY
- SECURITYPRIVILEGE
-- Get the list of all security roles
Select Name as SecurityRoleName FROM SecurityRole;
-- Get the list of all security roles to duties
SELECT T2.Name as SecurityRole, T3.NAME as Duty
FROM SECURITYOBJECTCHILDREREFERENCES T1
JOIN SECURITYROLE T2 ON T1.IDENTIFIER = T2.AOTNAME
JOIN SECURITYDUTY T3 ON T1.CHILDIDENTIFIER = T3.IDENTIFIER
WHERE T1.OBJECTTYPE = 0 AND T1.CHILDOBJECTTYPE = 1 ;
-- Get the ;ist of all security roles with privileges
SELECT T2.Name as SecurityRole, T3.NAME as Privileges
FROM SECURITYOBJECTCHILDREREFERENCES T1
JOIN SECURITYROLE T2 ON T1.IDENTIFIER = T2.AOTNAME
JOIN SECURITYPRIVILEGE T3 ON T1.CHILDIDENTIFIER = T3.IDENTIFIER
WHERE T1.OBJECTTYPE = 0 AND T1.CHILDOBJECTTYPE = 2 ;
-- Get the list of all role-duty combination with privilege
SELECT T2.Name as SecurityRole, T2.AOTNAME as RoleSystemName, T3.NAME AS Duty, T3.IDENTIFIER as DutySystemName, T5.NAME as Privilege, T5.IDENTIFIER as PrivilegeSystemName
FROM SECURITYOBJECTCHILDREREFERENCES T1
JOIN SECURITYROLE T2 ON T1.IDENTIFIER = T2.AOTNAME
JOIN SECURITYDUTY T3 ON T1.CHILDIDENTIFIER = T3.IDENTIFIER
JOIN SECURITYOBJECTCHILDREREFERENCES T4 on T4.IDENTIFIER = T3.IDENTIFIER
JOIN SECURITYPRIVILEGE T5 on T4.CHILDIDENTIFIER = T5.IDENTIFIER
WHERE T1.OBJECTTYPE = 0 AND T1.CHILDOBJECTTYPE = 1
AND T4.OBJECTTYPE = 1 AND T4.CHILDOBJECTTYPE = 2;
This was originally posted here.
*This post is locked for comments