Solved

Need reporting overview "users > groups > roles"


Badge +2

Our accountant need a report or view of application users and their access groups and roles. Is it possible to generate a "Roles and rights" reports or cube like this (IAM 2021.1)? 

I don’t have time and IAM/TSF table experience to build it from scratch in a timely fashion. If somebody willing to share a SQL query to start with would be awesome.

icon

Best answer by Mark Jongeling 10 May 2021, 09:34

This should do the trick, Active is the checkbox that determines whether or not a Roles is Active for a User group.

declare @project_id     varchar(100) = '' /* Project name */
, @project_vrs_id varchar(100) = '' /* Project version */

select u.name, ug.usr_grp_id, r.project_id, r.project_vrs_id, r.role_id, r.usr_grp_description, iif(active = 1, 'Assigned', 'Not assigned')
from usr u
join usr_grp_usr ugu
on ugu.usr_id = u.usr_id
join usr_grp ug
on ug.usr_grp_id = ugu.usr_grp_id
join usr_grp_gui_appl_role_overview r
on r.usr_grp_id = ug.usr_grp_id

/* Comment out Where clause for all projects and versions*/
where r.project_id = @project_id
and r.project_vrs_id = @project_vrs_id

 

View original

4 replies

Userlevel 6
Badge +18

Hi Jaap,

This query will show you all Users that are in User groups that have one or more Roles inside IAM applications. You can use the variables to limit the query to 1 project (version). (Tested on 2021.1):

declare @project_id     varchar(100) = '' /* Project name */
, @project_vrs_id varchar(100) = '' /* Project version */

select u.name, ug.usr_grp_id, r.project_id, r.project_vrs_id, r.role_id, r.usr_grp_description
from usr u
join usr_grp_usr ugu
on ugu.usr_id = u.usr_id
join usr_grp ug
on ug.usr_grp_id = ugu.usr_grp_id
join usr_grp_gui_appl_role_overview r
on r.usr_grp_id = ug.usr_grp_id

/* Comment out Where clause for all projects and versions*/
where r.project_id = @project_id
and r.project_vrs_id = @project_vrs_id

In the 2021.2 we have a cube inside IAM that can give the exact overview you are requesting for a single IAM application. But the 2021.2 is not available yet (soon™) so the query is the way to go for now:

Current access analysis

 

Badge +2

Thxs @Mark Jongeling I need one column extra for an “access report” within this query.
“Assigned”/”Not assigned” so I can show them a user has access to the role/group or not ;-)

Userlevel 6
Badge +18

This should do the trick, Active is the checkbox that determines whether or not a Roles is Active for a User group.

declare @project_id     varchar(100) = '' /* Project name */
, @project_vrs_id varchar(100) = '' /* Project version */

select u.name, ug.usr_grp_id, r.project_id, r.project_vrs_id, r.role_id, r.usr_grp_description, iif(active = 1, 'Assigned', 'Not assigned')
from usr u
join usr_grp_usr ugu
on ugu.usr_id = u.usr_id
join usr_grp ug
on ug.usr_grp_id = ugu.usr_grp_id
join usr_grp_gui_appl_role_overview r
on r.usr_grp_id = ug.usr_grp_id

/* Comment out Where clause for all projects and versions*/
where r.project_id = @project_id
and r.project_vrs_id = @project_vrs_id

 

Badge +2

Oops, I missed that field :-) 

I added gui_appl to this query (we have a project with multiple applications) and published it to Power BI finance. They are able to create their report and I’m done and all the credits to you @Mark Jongeling THXS!

Reply