How to pull list of Users and assigned roles in IDM 7.1

[former_member298408]

Can anyone help me know if there is any way to pull a list of Users and IDM Job Roles assigned to them in IDM 7.1

[Former Member · Accepted solution]

Hi Aditi,

You can do this either in an SQL console (MSSQL Server or SQL Oracle Developer/SQL+) or by writing the SQL as the source of a to ASCII pass in a job.

Assuming your role hierarchy is simple and MX_PERSON objects only have direct relationships with MX_ROLE objects, the following is what I do:

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

Once you have this as a basis you can easily add additional filters such as x.searchvalue to restrict on certain userids or z.searchvalue for role names.

I am sure there are other ways of doing this, too, that other people can contribute.

[terovirta · Accepted solution]

Andy Minshull wrote:
Assuming your role hierarchy is simple and MX_PERSON objects only have direct relationships with MX_ROLE objects, the following is what I do:

That's a good start. And if there is hierarchy but you don't need to display the full hierarchy (ie via what role(s) was the inherited role granted) then use "MX_AUTOROLE" as the role reference attribute.

As the ETL-cababilities of IdM are pretty cool, you could also create a simple IdM DSE job to export the users and their roles.

Souce SQL would be selecting all persons in the system with whatever filtering you would like to do in WHERE-clause like excluding disabled etc (depends on your configuration/requirements).

In destination you would write the MSKEYVALUEs matching the reference attributes MSKEYs (via script) to another table or CSV-file for further processing.

[former_member298408 · Accepted solution]

Hi Andy,

Many thanks for your reply, however, when I asked my DBA to run this query he said that he cannot find any table under name mxiv_sentries

Any thoughts what could be wrong.

Thanks

Aditi

[Former Member · Accepted solution]

Hi Aditi,

Couple of things here:

1. MXIV_SENTRIES is a view, not a table

2. If you are on Oracle the schema must go before the view name, so everywhere I have used MXIV_SENTRIES replace with MXMC_OPER.MXIV_SENTRIES

3. Put the SQL in a Job and you will not need to involve your DBA, or add the schema.

4. If you are on SQL Server you should add with(no lock) after each table name as Per recommends (or just use his query) 😉

Let me know how you get on.

Andy

[former_member2987 · Accepted solution]

Aditi,

mxiv_sentries is a view.

Matt

[terovirta · Accepted solution]

Did the DBA use the IdM database users? Like mxmc_oper or mxmc_rt?

(If your database is created with another prefix the user name would be [prefix]_oper.)

[Former Member · Accepted solution]

If this is Oracle you should make sure he prefixes table and view names with the owner mxmc_oper (replace mxmc with whatever your chosen schema is) if he logs in as system or some other account than the IdM users mxmc_rt or mxmc_oper. It would be mxmc_oper.mxiv_sentries, though a DBA probably knows this already come to think about it.

Br,

Per Christian

[Former Member]

Hi,

Any and all roles assigned to a user will directly or inherited are available in the MX_AUTOROLE attribute. The privileges are a combination of MXREF_MX_PRIVILEGE (directly assigned) and MX_AUTOPRIVILEGE (inherited from roles). So a not probably not very efficient way could be:

select

          E.mskey usermskey, E.avalue usermskeyvalue, REF.searchvalue refmskey,

          CASE REF.attrname

                    WHEN 'MXREF_MX_PRIVILEGE' then 'Direct assigned privilege'

                    WHEN 'MX_AUTOPRIVILEGE' then 'Role inherited privilege'

                    WHEN 'MX_AUTOROLE' then 'Assigned role'

          END assignmenttype,

          (select avalue from mxiv_pentries RM with(nolock) where RM.mskey = REF.searchvalue and RM.ATTRNAME = 'MSKEYVALUE') assignment

from

          mxiv_pentries E with(nolock) , mxiv_pentries REF with(nolock)

where

          REF.mskey = E.mskey and REF.attrname in ('MX_AUTOROLE','MXREF_MX_PRIVILEGE','MX_AUTOPRIVILEGE') AND

          E.attrname = 'MSKEYVALUE' and E.mskey in

(select mskey from mxiv_pentries with(nolock) where attrname = 'MX_ENTRYTYPE' and SEARCHVALUE = 'MX_PERSON')

(SQL Server only since its littered with "with (nolock)" and I dont have a 7.1 Oracle to test it on anyway).

The result is something like:

1063	USER.3003	1007	Direct assigned privilege	PRIV.DIRECT
1063	USER.3003	1009	Assigned role	ROLE.DIRECT
1063	USER.3003	1010	Assigned role	ROLE.INHERIT
1063	USER.3003	1008	Role inherited privilege	PRIV.INHERIT
1064	USER.3004	1007	Direct assigned privilege	PRIV.DIRECT
1064	USER.3004	1009	Assigned role	ROLE.DIRECT
1064	USER.3004	1010	Assigned role	ROLE.INHERIT
1064	USER.3004	1008	Role inherited privilege	PRIV.INHERIT

(my roles and privileges are named ROLE.DIRECT & ROLE.INHERIT, PRIV.DIRECT & PRIV.INHERIT)

Br,

Per Christian

Message was edited by: Per Krabsetsve

[Former Member]

Hi,

Here is a select for IDM 7.2, but I think you can modify it and use it for 7.1

SELECT a.mcmskey PERSON_MSKEY, a.mcmskeyvalue PERSON,

f.mskey ROLE_MSKEY, f.mclinkmskeyvalue BUSINESS_ROLE FROM idmv_entry_simple a

JOIN idmv_vallink_basic f ON f.mskey = a.mcmskey AND f.mcattrname='MXREF_MX_ROLE'

WHERE a.mcidstore=2 AND a.mcentrytype = 'MX_PERSON';

If you have some other questions or the select is not what you expected, don't hesitate.

[ken_halvorsen2]

Hi Simona

This is the first report I've been able to find that works for 7.2.

But, I'm not getting anything returned. Any idea's why I'm not?

Thanks

Ken

[ken_halvorsen2]

I found out why! We haven't created any Business Roles, thats why!

[former_member2987]

This would not work at all in 7.1.  In 7.1 all of the information is held in mxiv_sentries rather than the link structure in 7.2.

BR,

Matt

[Former Member]

Hi Matt,

If the view doesn't exist, there will be an error in the SQL developer for that.

BR

[terovirta]

Matt Pollicove wrote:
This would not work at all in 7.1.  In 7.1 all of the information is held in mxiv_sentries rather than the link structure in 7.2.

That's right. Also in 7.2 the links could be queried from single view "idmv_link_ext" with no joins as entry names (mcthismskeyvalue, mcothermskeyvalue) and entry types (mcthisocname, mcotherocname) are in the same view.

[former_member2987]

Actually neither view is supported in 7.1, which was my only point.  I liked your 7.2 query though!

[Former Member]

You might already be aware of this and joined link and entries manually to help the original issue. But just in case not there's already 7.2 views that does this; idmv_link_<variation> that join entry and link values which allows a slightly simpler version that can be extended with link values such as validfrom/to/context and more very easily:

select mcThisMskey PERSON_MSKEY,mcThisMSKEYVALUE PERSON, mcotherMskey ROLE_MSKEY, mcOtherMskeyValue ROLE

from idmv_link_ext_active

where mcThisOcName='MX_PERSON' and mcOtherOcName='MX_ROLE'

This is completely 7.1 incompatible though and I guess thats why you used the other tables.

Br,

Per Christian

[Former Member]

I tried the same query:

a.mcmskey PERSON_MSKEY, a.mcmskeyvalue PERSON,

.mskey ROLE_MSKEY, f.mclinkmskeyvalue BUSINESS_ROLE FROM idmv_entry_simple a

idmv_vallink_basic f ON f.mskey = a.mcmskey AND f.mcattrname='MXREF_MX_ROLE'

a.mcidstore=2 AND a.mcentrytype = 'MX_PERSON';

And all I get is

Msg 208, Level 16, State 1, Line 1

Invalid object name 'idmv_entry_simple'.

please help.

[terovirta]

Did you run Simona's query or the exact one that you copy/pasted here? The later one has SQL syntax errors.

If you ran Simona's query, did you run it against correct database or are you on 7.1 IdM?

7.1 has different database schema than 7.2.

regards, Tero

[Former Member]

I ran Simona's query, copied and pasted from her post above. Also, we are running 7.2.

SELECT a.mcmskey PERSON_MSKEY, a.mcmskeyvalue PERSON,

f.mskey ROLE_MSKEY, f.mclinkmskeyvalue BUSINESS_ROLE FROM idmv_entry_simple a

JOIN idmv_vallink_basic f ON f.mskey = a.mcmskey AND f.mcattrname='MXREF_MX_ROLE'

WHERE a.mcidstore=2 AND a.mcentrytype = 'MX_PERSON';

It didn't work:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'idmv_entry_simple'

[terovirta]

Hello,

then it has to be either you're not running it against correct database or are not logged in with correct IdM DB-user.

Now that I run the query it didn't return an error. If you get the query run without errors then change the Id Store reference (mcidstore=2) to the actual Id Store you use in your configuration.

If you need to get all users and their roles on 7.2 try this:

select lnk.mcThisMSKEY as UserMskey,

lnk.mcThisMSKEYVALUE as UniqueIdOfUser,

usr.mcDisplayName as DisplayNmaeOfUser,

lnk.mcOtherMSKEY as RoleMskey,

lnk.mcOtherMSKEYVALUE as UniqueIdOfRole,

rle.mcDisplayName as DisplayNameOfRole

from idmv_link_ext lnk, idmv_entry_simple usr, idmv_entry_simple rle

where lnk.mcThisOcName = 'MX_PERSON'

and lnk.mcOtherOcName = 'MX_ROLE'

and lnk.mcAttrName = 'MXREF_MX_ROLE'

and lnk.mcThisMSKEY = usr.mcMSKEY

and lnk.mcOtherMSKEY = rle.mcMSKEY

order by lnk.mcThisMSKEY

I prefer running the queries of linkages between objects agains the active links view (idmv_link_ext)..

If you're not after the display names the drop the two extra joins off the query.

regards, Tero

[Former Member]

HI, thanks, that one worked, it was my credentials, I must not have enough Access on the DB, I logged on with the SA ID and it ran.

[former_member2987]

This should probably be moved to a new thread to keep things organized.

Thanks