Report to List Roles and their Permissions in AX2012

Question

In Dynamics AX 2009 there's an OOTB security report which lists each group and its associated permissions.

For Dynamics AX 2012 we have roles taking the place of groups. It's possible in the application to see which permissions each role has, but there doesn't seem to be an OOTB report to list this information, so as to provide auditors with the data they need.

We could write custom code to give us this information, but it seems likely that as this is required for SOX there'd be an existing solution.

Is anyone aware of such a report?
Failing that, is there some add-on to provide this (e.g. a downloadable tool, an existing solution available as an XPO, or something within Dynamics LCS)?
Or is there any guidance on how SOX auditing should be performed which could suggest a better way of meeting the SOX requirements without needing such data?

score 1 · Accepted Answer · answered Nov 09 '15 at 15:05

I realised that there's a second database for AX2012; i.e. the model. After poking about I uncovered some security tables and have taken a stab at finding the relationships between them. I couldn't find any documentation on these tables by Googling, so if anyone else uses this, please note that there may be many oversights and issues.

--use your model database
use [AxDbName_Model]
go

--ensure you don't cause locking when running this script
set transaction isolation level read uncommitted
go

--I got these IDs by comparing the TypeId fields for results with what I saw in the AOT and guessing on relationships.

declare @SubRoleType table (Id int, SubRoleDesc nvarchar(32))
insert @SubRoleType (Id, SubRoleDesc)
values (133, 'Role / SubRole')
, (134, 'Privilege')
, (135, 'Duty')
, (136, 'Process Cycle')

declare @KernelType table (Id int, KernelTypeDesc nvarchar(32))
insert @KernelType (Id, KernelTypeDesc)
values (11, 'Class')
, (44, 'Table')
, (45, 'ServerMethod')

--here's the actual code to fetch the security model/
--it could probably be improved to make it hierarchical, but
--for our company's purposes we don't seem to require that so 
--I didn't put any time into investigating that route.

;with permissionsModelCte (ParentId, ItemId, ItemName, ItemTypeId, ItemType, IsEnabled) as 
(
    --duties & privileges (sub role type describes what type of permission this is; this seems to hold all security related groupings of aot objects)
    select mssr.ROLEHANDLE
    , mssr.RECID
    , mssr.SUBROLENAME
    , mssr.SUBROLETYPE
    , srt.SubRoleDesc
    , mssr.ISENABLED
    from ModelSecuritySubRole mssr 
    left outer join @SubRoleType srt on srt.id = mssr.SUBROLETYPE

    union 

    --permissions (kernel type defines the related object type; this seems to hold everything in the AOT)
    select msp.OWNERHANDLE
    , msp.RECID
    , msp.OBJECTNAME
    , msp.KERNELTYPE 
    , 'Permission\' + kt.KernelTypeDesc
    , msp.ISENABLED
    from ModelSecurityPermission msp 
    left outer join @KernelType kt on kt.Id = msp.KERNELTYPE 
)
select msr.Name
, pmc.ItemType ChildItemType
, pmc.ItemName ChildItemName
, pmc.IsEnabled
--, pmc.ItemId ChildItemId  --interesting for investigating the script, but causes duplicate results
, msr.ROLEHANDLE ItemId
, pmc.ItemTypeId ChildItemTypeId
--, *
from ModelSecurityRole msr
left outer join permissionsModelCte pmc on pmc.ParentId = msr.ROLEHANDLE
--where msr.Name in ('CustInvoiceAccountsReceivableClerk', 'CCIARCollections') 
where msr.UTILTYPE = 133 --Roles only
group by msr.Name
, msr.ROLEHANDLE 
, pmc.ItemName 
--, pmc.ItemId --see select statement's ChildItemId
, pmc.IsEnabled
, pmc.ItemType 
, pmc.ItemTypeId 
order by msr.Name, pmc.ItemType, pmc.ItemName

go

Report to List Roles and their Permissions in AX2012

1 Answers1