Customer experience | Sales, Customer Insights,...

Is it possible to build a Power BI report on use security roles and their access

(0) Share

Report

Posted on by MikeC282

2,095

Hi all,

Quick question. I know there's a couple of tools in the XRM toolbox that allows you check which security roles have access to which entity, who has these security roles and allows you to export a csv file. It's all a bit of a manual process and just wondering whether it's possible to pull security role and table access and user security access into a Power BI report so that when I audit access for users it doesn't have to be a manual process.

All responses (3)

Answers (0)

Gustavo Longo on at

Like (0)

Report

RE: Is it possible to build a Power BI report on use security roles and their access

I have this query for Onprem environment, but eventually could be an inspiration for you.

Retrieve privileges for an user based on user's security roles and user's team security roles as well:

select pb.name,

SecurityLevel =

CASE rp.PrivilegeDepthMask

WHEN 1 THEN 'User'

WHEN 2 THEN 'Business Unit'

WHEN 4 THEN 'Parent: Child Business Unit'

WHEN 8 THEN 'Organization'

END,

AccessLevel =

CASE pb.AccessRight

WHEN 1 THEN 'READ'

WHEN 2 THEN 'WRITE'

WHEN 4 THEN 'APPEND'

WHEN 16 THEN 'APPENDTO'

WHEN 32 THEN 'CREATE'

WHEN 65536 THEN 'DELETE'

WHEN 262144 THEN 'SHARE'

WHEN 524288 THEN 'ASSIGN'

END

from roleprivileges as rp

join privilegebase as pb on rp.privilegeid = pb.privilegeid

where rp.RoleId in (

select distinct sur.RoleId

from SystemUserRoles sur

join SystemUserBase sub

on sub.SystemUserId = sur.SystemUserId

where sub.SystemUserId = '<SystemUserID>'

union

select distinct tr.RoleId

from TeamRoles tr

join TeamMembership tms

on tms.TeamId = tr.TeamId

join SystemUserBase sub

on sub.SystemUserId = tms.SystemUserId

where sub.SystemUserId = '<SystemUserID>')

order by pb.name
MikeC282 2,095 on at

Like (0)

Report

RE: Is it possible to build a Power BI report on use security roles and their access

Thanks bud. I know I can pull out static csv files but is there a way to directly access the roles and privilege tables/entities in Dynamics? I'm thinking using the Power Query (M) Builder in the XRM toolbox to pull out those tables. I've already created a BI report that contains the name and email of several AD security groups that need to be matched to certain entity access. BI seems to connect with AD fine.

I've had a quick looking using Fetch XML builder and have found the roles entity which seems to contain the security role values. There also seems to be a privilege entity, privilegeobjecttypecodes. Just unsure how this all joins together. Basically if I pull them all into the BI report what's the cardinality/data modelling is it even possible? Hoping someone out there already tried doing this via power BI.

Does it actually need code to pull? I'm assuming all the access data is stored in tables within Dynamics. If I'm just after the ability to read it can I access it using Fetch XML?
Suggested answer

Gustavo Longo on at

Like (0)

Report

RE: Is it possible to build a Power BI report on use security roles and their access

Hello MikeC282,

Hope you are well.

You need something pulling the security role info from Dynamics 365.

Eventually you can create a SDK code and run it periodically or even use these XRM toolbox plug-ins as well.

Later you can consume the output in Power BI to prepare an user friendly report.

Regards,