SQL Query to get security details (such as Security roles, duties and privileges)

 Today, 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;