List of roles assigned to users in IDM 7.1

[former_member298408]

Hi All,

Sometime back, I had requested for some database queries through which I can get a list of roles assigned to users in IDM 7.1.

Below is the query which I got:

select x.mskey, x.avalue, z.mskey, z.avalue from mxiv_sentries x, mxiv_sentries y, mxiv_sentries z

where x.mskey=y.mskey

and y.searchvalue= z.mskey

and y.attrname='MXREF_MX_ROLE'

and x.attrname='MSKEYVALUE'

and x.mskey in (select mskey from mxiv_sentries where attrname='MX_ENTRYTYPE' and searchvalue='MX_PERSON')

and z.attrname='MSKEYVALUE'

order by x.mskey

However, this query only gives the IDM Roles (MXREF_MX_ROLE) assigned to users but I want the privileges (MX_AUTO_PRIVILEGE) coming through IDM Role or privileges (MXREF_MX_PRIVILEGE) that are directly assigned to users, also to be included as well in query.

So basically I want query to return the User and IDM Roles assigned to it, along with Privileges coming through IDM Role and privileges that are directly assigned to user.

I am not an SQL expert, could anyone of you please help me modify above query or come up with new query.

Thanks...

[Former Member · Accepted solution]

So it would be the best to replace this line

and y.attrname='MXREF_MX_ROLE'

with

and y.attrname in ('MXREF_MX_ROLE', 'MXREF_MX_PRIVILEGE', 'MX_AUTOPRIVILEGE')

If you have further attributes you want to add, simply add another in the row

[former_member2987 · Accepted solution]

Dominik,

Nice touch!

At this point, I'd start thinking about something like CRW where grouping and formatting could be done.

Matt

[former_member298408 · Accepted solution]

Thanks Domink...I'll try this one out and will let you know.

[former_member2987]

Aditi,

If you just want privileges replace MX_MXREF_ROLE with MX_MXREF_PRIVILEGE.

[terovirta]

Or MX_AUTOPRIVILEGE if the privileges are not directly assigned..

[former_member298408]

Thanks Matt for your reply, but I want all three i.e. MX_AUTO_PRIVILEGE, MX_MXREF_ROLE and MX_MXREF_PRIVILEGE.