Skip to main content

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.

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

 

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 ;-)

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

 

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


Terms & ConditionsCookie settings