Dynamics AX 2009 中有一个 OOTB 安全报告,其中列出了每个group
权限及其相关权限。
对于 Dynamics AX 2012,我们用角色代替了组。在应用程序中可以查看每个角色拥有哪些权限,但似乎没有 OOTB 报告来列出这些信息,以便为审计人员提供他们需要的数据。
我们可以编写自定义代码来提供这些信息,但由于这是 SOX 所要求的,因此似乎可能存在现有的解决方案。
- 有人知道这样的报告吗?
- 除此之外,是否有一些附加组件可以提供此功能(例如可下载的工具,可作为 XPO 使用的现有解决方案或 Dynamics LCS 中的某些内容)?
- 或者是否有关于如何执行 SOX 审计的指导,可以建议一种更好的方法来满足 SOX 要求而不需要这些数据?
答案1
我意识到 AX2012 还有第二个数据库,即模型。经过一番探索,我发现了一些安全表,并尝试找出它们之间的关系。我在谷歌上找不到关于这些表的任何文档,所以如果其他人也使用这个,请注意可能会有很多疏忽和问题。
--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