IDM 7.1: How to pull a report of auto privileges assigned to user and corresponding IDM Role.

[former_member298408]

Hi All,

We have a requirement in our project (IDM 7.1), where we have to provide a list of Users, IDM Roles assigned to them and Auto-Privileges coming through those IDM Roles.

We have this report built in, but the issue is that it doesn't tell that which auto-privilege is coming through which idm role.

It simply gives Name/ID of User, IDM Role assigned to him and Auto-Privileges assigned to him.

Is it possible to build a report which not only gives above information, but also tells that which auto-privilege is coming through which IDM Role.

We can have a list of IDM Roles and auto-privileges assigned to them and a separate list of users and IDM Roles assigned to them and do a V LOOK UP, but the problem is that there are many cases in our project where not all auto-privileges within an IDM Role are approved for a user, so if we go by this option, those auto-privileges which were rejected for the user will also come in final report, which we don't want.

In light of above is it possible to create a report, which provides us User Id/Name, IDM Role Assigned to them and Auto-Privileges Assigned to them because of any particular IDM Role, as I am not able to find any table in IDM DB which stores this information that which auto-privilege was assigned because of which IDM Role.

Key point is that report should be able to tell which auto-privilege was assigned because of which idm role.

Please help!!!

Thanks

Aditi

[Former Member]

Hi Aditi

You can try this:

select * from idmv_link_ext2 where mcthisocname = 'MX_PERSON'  and mcotherocname in ('MX_ROLE', 'MX_PRIVILEGE') ;

For specific user:

select * from idmv_link_ext2 where mcthisocname = 'MX_PERSON'  and mcotherocname in ('MX_ROLE', 'MX_PRIVILEGE') and mcthismskeyvalue = 'MSKEYVALUE';

The values with this are for the person

The values with other are for the references (roles and privileges).

Best Regards,

Kiril

[terovirta]

These link-views are from 7.2 data model and don't exist on 7.1.

Aditi, how is you report executed? Interactive UI task vs job? In a job you could dump all the roles (MXREF_MX_ROLE) of the selected users to temp table in first pass, then in another pass dump all privileges of roles (MXREF_MX_PRIVILEGE) found from the roles from first pass to another temp table.

If the report is generated in UI task then the option would be couple of screens full of scripting. First querying the selected users' roles then finding the privileges under roles..

If I remember correctly anymore as haven't touched 7.1 in three years.. Querying for the users direct role assignments:

select searchvalue from mxiv_sentries where attrname = 'MXREF_MX_ROLE' and mskey = [user_mskey]

Querying for the privileges of each role (searchvalue) in the previous result set:

select mskey from mxiv_sentries where attrname = 'MXREF_MX_PRIVILEGE' and mskey in ([role_mskeys]) order by mskey

Since you don't want to display MSKEYs in the report you need to join the mxiv_sentries to the query twice to get displayname (or another attribute) visible, something like:

select mskey, avalue
from mxiv_sentries a, mxiv_sentries b
where a.attrname = 'MXREF_MX_PRIVILEGE'
and b.attrname = 'DISPLAYNAME'
and a.mskey = b.mskey
and a.mskey in ([role_mskeys])
order by mskey

regards, Tero

[former_member298408]

Hi Tero,

Thank you for your reply, but the issue with this is it will just fetch the auto privileges assigned to IDM Roles.

Our concern is say that an IDM Role has 5 auto-privileges and 3 of them were approved and assigned to user and 2 of them were rejected and not assigned.

This proposed solution will also provide those auto-privileges which were rejected and not assigned to user.

Basically we want the details like: User, IDM Role assigned and the auto-privileges assigned to user because of that IDM Role.

In our report it gives all these details, but it doesn't discriminate that which auto-privilege was assigned because of which IDM Role.

Confusion happens when user has multiple IDM Roles, it just gives a list of users, all the IDM Roles assigned to those users (IDM Role to User Mapping) and all the auto privileges assigned to those users(excluding rejected and not assigned ones), but doesn't give the IDM Role to Auto-privilege mapping.

I was thinking if we can utilize Audit IDs here. Do you have any suggestions.

Thanks

Aditi