Query to find Repositories what are the repositories an user is active.

[former_member373435]

Hi All,

Actually I am working on a password reset form (not self service).

So i have created a multi select attribute which would show the repositories where an user is active and has access.

Can anyone help with the query ?

As a start - I have tried "select Repository as rep_id, VarValue as displayName from mc_repository_vars" this gives me all the Priv:<repo>::Only privileges in IDM.

[Steffi_Warnecke · Accepted solution]

Hello Ani,

we use the "ACCOUNT<repname>" attributes for this, because if a user has an account in that repository, this attribute is filled with the username.

And then it's just using some SQL magic (substring) to get the repname part and use this in the dropdown list.

.

Regards,

Steffi.

[former_member201064]

You can try this:

select replace(mcothermskeyvalue, 'PRIV:SYSTEM:', '') rep from idmv_link_ext with (nolock) where mcthismskey = %usermskey% and mcothermskeyvalue like 'PRIV:SYSTEM:%' order by rep

If your repository description is meaningful you also could use:

select r.rep_Description from idmv_link_ext with (nolock) join MC_REPOSITORY r with (nolock) on r.rep_name = replace(mcothermskeyvalue, 'PRIV:SYSTEM:', '') where mcthismskey = %usermskey% and mcothermskeyvalue like 'PRIV:SYSTEM:%' order by rep_description

For Oracle, remove the with (nolock). Replacing could be different also, don't remember it anymore.

[Steffi_Warnecke]

Maybe it's just me, but I try to stay away from idmv_link_ext (and other link-views) if I don't specificly need some information that can only be found there, because of the sheer mass of records in there.

.

Regards,

Steffi.

[former_member201064]

Lucky me then. 🙂

My idmv_value_ext(9.6 million) is five times the size of the idmv_link_ext (1.8 million). SAP roles are done using an unconnected GRC and our AD group handling isn't that worse either.

[Steffi_Warnecke]

Oh boy. 😄

[former_member373435]

Thanks everyone. Wonderful world !