Finding Role & Role Approver info in IDM 7.2

[Former Member]

Hi Team,

Can any one kindly provide me the View/Tabe name/SQL Query that provides the list of roles by approver or vice versa?

I could able to find the info for role name & owner but not approver info.

Regards,

Venkata Bavirisetty

[Former Member · Accepted solution]

In 7.2 you should start looking at the link audit, idmv_linkaudit_<> views. Example I gave here yesterday shows the linkaudit info for a specific user:

select linkid,userMSKEYVALUE,AssignmentMSKEYVALUE,OperationText,auditdate from idmv_linkaudit_ext where linkid in

(select mcUniqueId from idmv_link_basic_active where mcThisMskey = (select mcMskey from idmv_entry_simple where mcMSKEYVALUE='ADMINISTRATOR'))

From there it should be quite easy to understand the columns to add additional filters for what you're looking for. Such as, everything approved by 'Administrator':

select linkid,userMSKEYVALUE,AssignmentMSKEYVALUE,OperationText,auditdate,ResponsibleMSKEYVALUE as approver

from idmv_linkaudit_ext

where operationText = 'Approved' and ResponsibleMskey in

(select mcMskey from idmv_entry_simple where mcMSKEYVALUE='ADMINISTRATOR')

This also includes expired and removed assignments, so to only get current assignments join with a link_active view:

select linkid,userMSKEYVALUE,AssignmentMSKEYVALUE,OperationText,auditdate,ResponsibleMSKEYVALUE as approver

from idmv_linkaudit_ext LA inner join idmv_link_basic_active L ON L.mcUniqueID = LA.LinkId

where operationText = 'Approved' and ResponsibleMskey in

(select mcMskey from idmv_entry_simple where mcMSKEYVALUE='ADMINISTRATOR')

See the mxi_link_audit_operations table for a list of operation code texts/descriptions if you use other views without operationText included.

Br,

Chris

[Former Member · Accepted solution]

Hi Chris,

My scenarios is not related to assignments. Thats why I never tried into link tables.

My scenarios is to replace roleapprover1 with roleapprover2 for all the roles approver1 is currently responsible for. This is like master data maintenance for roles before users requests roles.

Regards,

Venkata Bavirisetty

[Former Member · Accepted solution]

Ok, I answered a different question noone had asked then.

I would usually have put the approver(s) in the role's MX_APPROVERS attribute, so thats where I'd look, or better yet, the approvers are in a role that is stored in the MX_APPROVERS attribute so when I remove the person from the approver role it sorts itself out.

This should list roles/privileges where ADMINISTRATOR is an approver:

select mcmskey,mcmskeyvalue,mcDisplayName from idmv_entry_simple where mcEntryType in ('MX_ROLE','MX_PRIVILEGE') and mcMskey in
(select mcMskey from idmv_vallink_basic where mcAttrName='MX_APPROVERS' 
 and mcSearchValue = (select mcmskey from idmv_entry_simple where mcMSKEYVALUE = 'ADMINISTRATOR'))

You might need to extend this to check additional attributes for escalation approvers, additional approval levels etc.

Hope this is more helpful than my first response 🙂

Br,

Chris