+2Our 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.
Best answer by Mark Jongeling
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
+23Hi 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_idIn 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:
+2Thxs
“Assigned”/”Not assigned” so I can show them a user has access to the role/group or not ;-)
+23This 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
+2Oops, 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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.