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.
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