Skip to Content
Hi there, IdM 7.2 is generally available for some time now and I thought it might be useful for some people in the IdM community to have some information available about the new tables/views in this version of SAP ID Mgmt. One of the new features in SAP ID Mgmt 7.2 is that links between objects aren’t stored like other attributes like firstname or email. They have their own table now.To recap: In order to print out the displayname of all directly assigned privileges of the user with mskey 12345 in SAP ID Mgmt 7.1 I needed the statement   select searchvalue from mxiv_sentries where attrname=’DISPLAYNAME’ and convert(varchar, mskey) in (  select searchvalue from mxiv_sentries where attrname=’MXREF_MX_PRIVILEGE’ and mskey=12345 )   The problem with this statement is that the convert (or to_char on an Oracle DB) kills performance because the indexes can’t deal with it. This was a conceptual problem. The column mskey is an integer column in the table mxi_values. The column searchvalue is a varchar column, however, in case the attribute is a reference attribute this varchar column contains an integer value.This has been significantly improved with version 7.2. Here, all reference attributes are stored in a separate table. Let’s have a look at it: select top 5 mcthismskey, mcothermskey, mcattrname from idmv_link_ext   imageThe difference here is that you don’t need to mix varchar and integer values. The values in this view are always mskeys of objects in Identity Center (in other words: integers). The query from above translated into 7.2 looks like this:   select mcvalue from idmv_vallink_basic where attrname=’DISPLAYNAME’ and mcmskey in (  select mcothermskey from idmv_link_ext where mcattrname=’MXREF_MX_PRIVILEGE’ and mcthismskey=12345 )   Notice something? No convert or to_char anymore. But it gets even better. idmv_link_ext has columns mcthismskeyvalue and mcothermskeyvalue. So imagine we weren’t selecting DISPLAYNAME for the privs in the example above but MSKEYVALUE. Then the query gets even more simple:   select mcothermskeyvalue from idmv_link_ext where mcattrname=’MXREF_MX_PRIVILEGE’ and  mcthismskey=12345   image
To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

    1. T RAJENDRA PRASAD

      Hey Kai… I was wondering how to find the IDM Database table names.. like the ones you mentioned above idmv_vallink_basic and mxiv_sentries.

      Its urgent for me.. I was asked to write a query to retrive list of business roles, owners of those roles and the functional area of the role from IDM. I’m not sure about the tables which store these information.

      Can you please help?

      (0) 

Leave a Reply