Security metadata from Dynamics 365 finance and operations

Security objects in Dynamics 365 finance and operations consists of privileges,duties,roles etc. Dynamics 365 for finance and operations comes with out of the box security objects for most of the business processes. Multiple roles are available out of the box which can be assigned to users for restricting or granting access to different functionalities in Dynamics 365 finance and operations.  

 A power user can create custom security objects and add additional security configuration using the security configuration form in D365 fin ops. The securable types like menuitems, dataentity, code permissions are assigned read access,update access,invoke access etc and associated with privileges.

Standard entities are available in D365 finops to export the security configuration. These standard entities are 

1) Security role metadata customizations entity

2) Security duty metadata customizations entity

3) Security privilege metadata customizations entity

The standard entities are masters and export all the metadata for roles,duties and privileges. The standard entities does not export all the cross references. The cross references are 

1) Privileges,duties assigned to role

2) Permissions assigned to privileges

3) Permissions available on a duty or role

The customized security objects along with standard security objects are stored in mainly few table in D365 fin ops.
The tables are dbo.SECURITYDUTY ,dbo.SECURITYROLE and dbo.SECURITYPRIVILEGE.

For cross references the tables are dbo.SECURITYRESOURCEPRIVILEGEPERMISSIONS,dbo.SECURITYROLEDUTYEXPLODEDGRAPH,

dbo.SECURITYPRIVILEGEPARENTREFERENCES. Some of the content in cross references table are stored in serialized xml format. For example the table SECURITYPRIVILEGEPARENTREFERENCES has the data column stored in xml format. The data column has parent references to duties or roles for the privilege record. Since some of the data is stored as serialized XML there is no direct way to get all the cross references and permission details.

Now its becomes very difficult if we want to audit the security info and see details about permissions assigned to duties or privileges. The security configuration form in D365 finops has a visual representation of the data. The entire data can also be exported as XML but we would need to do the additional plumbing to get the required info from the data.

This can be solved by writing a simple SQL query against the table mentioned above for getting the security metadata and all its cross references and securable types. This query assumes that you have knowledge of SQL and XML.This query can be run on a sandbox environment which has the prod db backup.

Use AxDB

DECLARE @Duties TABLE
(
IDENTIFIER nvarchar(81),
XMLData xml
)

DECLARE @DutiesPrivilege TABLE
(
Privilege nvarchar(100),
Duty nvarchar(200)
)

INSERT INTO @Duties(Identifier, XMLData)
SELECT [IDENTIFIER], (CAST([DATA] as XML))
FROM [AxDB].[dbo].[SECURITYPRIVILEGEPARENTREFERENCES]

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.datacontract.org/2004/07/Microsoft.Dynamics.AX.Security.Management') 
INSERT INTO @DutiesPrivilege(Privilege, Duty)
SELECT T.IDENTIFIER,(Cast(T2.Duties.query('text()') as nvarchar(200))) as Duty
FROM @Duties T
CROSS APPLY T.XMLData.nodes('/PrivilegeParents/Duties/node()') as T2(Duties)

select 
Role1.AOTNAME as RoleAOTName,
Role1.NAME as RoleName,
Duty.IDENTIFIER as DutyIdentifier,
Duty.NAME as DutyName,
DP.Privilege as PrivilegeIdentifier,
P.NAME as PrivilegeName
from dbo.SECURITYROLEDUTYEXPLODEDGRAPH graph
join dbo.SECURITYDUTY Duty on Duty.RECID = graph.SECURITYDUTY
join dbo.SECURITYROLE Role1 on Role1.RECID = graph.SECURITYROLE
join @DutiesPrivilege DP on DP.Duty = Duty.IDENTIFIER
join dbo.SECURITYPRIVILEGE P on P.IDENTIFIER = DP.Privilege

select PP.PRIVILEGEIDENTIFIER,P.Name,
case PP.SECURABLETYPE
when 1 then 'menuitemdisplay'
when 2 then 'menuitemoutput'
when 3 then 'menuitemaction'
when 55 then 'weburlitem'
when 56 then 'webactionitem'
when 57 then 'webdisplaycontentitem'
when 58 then 'weboutputcontentitem'
when 75 then 'webmanagedcontentitem'
when 73 then 'webcontrol'
when 59 then 'webletitem'
when 44 then 'tablefield'
when 45 then 'classmethod'
when 76 then 'serviceOperation'
when 11 then 'FormControl'
when 82 then 'formpart'
when 81 then 'infopart'
when 85 then 'ssrsreport'
when 18 then 'report'
when 115 then 'codepermission'
when 143 then 'formdatasource'
when 67 then 'dataentity'
when 146 then 'dataentitymethod'
End as SecurableType,
PP.AOTName as PermissionAOTname,
PP.AOTCHILDNAME as PermissionAOTChildName,
PP.READACCESS as ReadAccess,
PP.UPDATEACCESS as UpdateAccess,
PP.CREATEACCESS as CreateAccess,
PP.CorrectAccess as CorrectAccess,
PP.DeleteAccess as DeleteAccess,
PP.INVOKEACCESS as InvokeAccess from
dbo.[SECURITYRESOURCEPRIVILEGEPERMISSIONS] PP 
join dbo.SECURITYPRIVILEGE P on P.IDENTIFIER = PP.PRIVILEGEIDENTIFIER