In this blog post I would like to provide useful queries for retrieving roles, duties and privileges information.
DECLARE @Resource varchar(250)
DECLARE @NetworkAlias varchar(250)
SET @Resource = '<<RESOURCE NAME>>' -- sample: BATCHABORT, SALESLINE
SET @NetworkAlias = '<<NETWORK ALIAS>>' <name>@d365operationslicensing.ccsctp.net
DECLARE @UserID varchar(100)
SELECT @UserID = ID FROM USERINFO
WHERE NETWORKALIAS = @NetworkAlias
1. Roles for given User
SELECT DISTINCT SECURITYROLE, SR.AOTNAME, SR.NAME
FROM SECURITYUSERROLE SUR
JOIN SECURITYROLE SR
ON SR.RECID = SUR.SECURITYROLE
WHERE SUR.USER_ = @UserID
AND SUR.ASSIGNMENTSTATUS = 1
1a. Roles specific to a company/organization
SELECT DISTINCT
USER_ AS [USER ID],
SUR.SECURITYROLE AS [ROLE ID],
SR.NAME AS [ROLE NAME],
ISNULL(CSR.DATAAREA, '4ALL') AS [COMPANY]
FROM SECURITYUSERROLE SUR
LEFT JOIN SECURITYUSERROLECONDITION CSR
ON SUR.RECID = CSR.SECURITYUSERROLE
JOIN SECURITYROLE SR
ON SR.RECID = SUR.SECURITYROLE
WHERE SUR.USER_ = @UserID
AND ASSIGNMENTSTATUS = 1
2. Roles and sub-roles for given User
SELECT RECID AS [SECURITYROLE], AOTNAME, NAME, 'Yes' AS [IS SUB ROLE]
FROM SECURITYROLE SR
WHERE RECID IN
(SELECT SECURITYSUBROLE
FROM SECURITYSUBROLE
WHERE SECURITYROLE IN
(SELECT SECURITYROLE
FROM SECURITYUSERROLE
WHERE USER_ = @UserID
AND ASSIGNMENTSTATUS = 1
))
UNION
SELECT RECID AS [SECURITYROLE], AOTNAME, NAME, 'No' AS [IS SUB ROLE]
FROM SECURITYROLE SR
WHERE RECID IN
(SELECT SECURITYROLE
FROM SECURITYSUBROLE
WHERE SECURITYROLE IN
(SELECT SECURITYROLE
FROM SECURITYUSERROLE
WHERE USER_ = @UserID
AND ASSIGNMENTSTATUS = 1
))
3. Duties assigned to given User
SELECT *
FROM SECURITYDUTY
WHERE RECID IN
(SELECT SECURITYDUTY
FROM SECURITYROLEDUTYEXPLODEDGRAPH SDE
JOIN SECURITYUSERROLE SUR
ON SUR.SECURITYROLE = SDE.SECURITYROLE AND SUR.ASSIGNMENTSTATUS = 1
WHERE SUR.USER_ = @UserID
)
4. Privileges for given User (this includes privileges assigned via duties and subrole)
SELECT DISTINCT
SP.IDENTIFIER,
SP.NAME AS [PRIVILEGE]
FROM SECURITYPRIVILEGE SP
JOIN SECURITYROLEPRIVILEGEEXPLODEDGRAPH MAP
ON MAP.SECURITYPRIVILEGE = SP.RECID
WHERE MAP.SECURITYROLE
IN (SELECT SECURITYROLE FROM SECURITYUSERROLE
WHERE USER_ = @UserID
AND ASSIGNMENTSTATUS = 1)
5. Permissions and grants for given User (this includes privileges assigned via duties)
SELECT DISTINCT SECURABLETYPE,
MAP.SECURITYROLE AS [ROLEID],
RES.AOTNAME AS [RESOURCE],
RES.AOTCHILDNAME AS [CHILD RESOURCE],
SP.NAME AS [PRIVILEGE],
RES.READACCESS,
RES.UPDATEACCESS,
RES.CREATEACCESS,
RES.CORRECTACCESS,
RES.DELETEACCESS
FROM SECURITYRESOURCEPRIVILEGEPERMISSIONS RES
JOIN SECURITYPRIVILEGE SP
ON SP.IDENTIFIER = RES.PRIVILEGEIDENTIFIER
JOIN SECURITYROLEPRIVILEGEEXPLODEDGRAPH MAP
ON MAP.SECURITYPRIVILEGE = SP.RECID
WHERE MAP.SECURITYROLE
IN (SELECT SECURITYROLE FROM SECURITYUSERROLE
WHERE USER_ = @UserID
AND ASSIGNMENTSTATUS = 1)
6. Permission for a given User and Resource (this includes privileges assigned via duties)
SELECT DISTINCT SECURABLETYPE,
MAP.SECURITYROLE AS [ROLEID],
RES.AOTNAME AS [RESOURCE],
RES.AOTCHILDNAME AS [CHILD RESOURCE],
SP.NAME AS [PRIVILEGE],
RES.READACCESS,
RES.UPDATEACCESS,
RES.CREATEACCESS,
RES.CORRECTACCESS,
RES.DELETEACCESS
FROM SECURITYRESOURCEPRIVILEGEPERMISSIONS RES
JOIN SECURITYPRIVILEGE SP
ON SP.IDENTIFIER = RES.PRIVILEGEIDENTIFIER
JOIN SECURITYROLEPRIVILEGEEXPLODEDGRAPH MAP
ON MAP.SECURITYPRIVILEGE = SP.RECID
WHERE MAP.SECURITYROLE
IN (SELECT SECURITYROLE FROM SECURITYUSERROLE
WHERE USER_ = @UserID
AND ASSIGNMENTSTATUS = 1)
AND RES.AOTNAME = @Resource
7. Permissions required for given Resource
SELECT DISTINCT SECURABLETYPE,
MAP.SECURITYROLE AS [ROLEID],
SR.NAME AS [ROLE NAME],
RES.AOTNAME AS [RESOURCE],
RES.AOTCHILDNAME AS [CHILD RESOURCE],
SP.NAME AS [PRIVILEGE],
RES.READACCESS,
RES.UPDATEACCESS,
RES.CREATEACCESS,
RES.CORRECTACCESS,
RES.DELETEACCESS
FROM SECURITYRESOURCEPRIVILEGEPERMISSIONS RES
JOIN SECURITYPRIVILEGE SP
ON SP.IDENTIFIER = RES.PRIVILEGEIDENTIFIER
JOIN SECURITYROLEPRIVILEGEEXPLODEDGRAPH MAP
ON MAP.SECURITYPRIVILEGE = SP.RECID
JOIN SECURITYROLE SR
ON SR.RECID = MAP.SECURITYROLE
WHERE RES.AOTNAME = @Resource
ORDER BY MAP.SECURITYROLE , SECURABLETYPE
8. Permissions required for given Resource (using SecurityroleRuntime table. This is used during runtime and should have same data as above)
SELECT DISTINCT TYPE, SECURITYROLE AS [ROLEID], NAME AS [RESOURCE], CHILDNAME AS [CHILD RESOURCE], CREATEACCESS, READACCESS, UPDATEACCESS, DELETEACCESS FROM SECURITYROLERUNTIME
WHERE NAME = @Resource
ORDER BY SECURITYROLE, TYPE
That's it!
No comments:
Post a Comment