Skip to Content
avatar image
Former Member

Finding Role & Role Approver info in IDM 7.2

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Jul 24, 2014 at 11:58 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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