Skip to Content

UWL and SAP NetWeaver Identity Management 

In the past I often heard the question if and how the approval items of the Identity Center can be shown in the Universal Worklist (UWL) of the SAP NetWeaaver Portal.
Thanks to Thilo Brandt who already published a lot of information about UWL development and Kare Indroy who is very experienced in Identity Center I was able to come up with a prototype which brings approval tasks into the UWL.
The prototype works already with SAP NetWeaver Identity Management 7.0 SP1 – therefore the version which has already been available end of 2007. My installation has an underlying MS SQL database.
Based on the public UWL API it is quite simple to implement and I will demonstrate in this first blog what you need to do on the IdM side to get it working yourself.

Background with regards to Identity Center (Version 7.0 and 7.1) – Updated Nov 12th, 2010

A lot of functionality of Identity Center is available through stored procedures. In order to retrieve approval items the stored procedure “mxp_get_Approvers” is used

  • This stored procedure requires as input parameter the MSKEY of the approver. The MSKEY is a numerical value which can be derived from the MSKEYVALUE of a user entry.
  • For version 7.0 the creation date of items in the result set is stored in a field with no name
  • For version 7.1 the creation date of items in the result set is stored in a field with the name “PostedDate”

For attribute approvals (approvals of Pending Values) another stored procedure is required: “call mxp_get_Attr_Approvals”. Similar to “mxp_get_Approvers” it requires as input parameter the MSKEY of the approver.

For the transformation of MSKEY to MSKEYVALUE and vice versa following SQL statements are required:

  • SELECT MSKEY FROM MXIV_SENTRIES WHERE AttrName=’MSKEYVALUE’ AND SearchValue=’UserName’ AND IS_ID=2;
    Remark: SearchValue must be the logonId (MSKEYVALUE) and IS_ID must be the id of the identity store to work with
  • SELECT aValue FROM MXIV_SENTRIES WHERE AttrName=’MSKEYVALUE’ AND MSKEY=8888 AND IS_ID=2;
    Remark: MSKEY is the unique ID of the user and IS_ID must be the id of the identity store to work with

I wrapped each of the database operations into a dedicated method:

  private void getApprovalTasks (Connection con, String mskey) throws TaskListException {
  
    try {
      //get all tasks for approval of current user
      CallableStatement cs =
        con.prepareCall(“{call mxp_get_Approvers(?)};”);
        cs.setString(1, mskey);
    
      boolean results = cs.execute();
      int rsCount = 0;
      Task t = null;
 
      //Loop through the available result sets.
      do {
        if (results) {
          ResultSet rs = cs.getResultSet();
          rsCount++;
 
          while (rs.next()) {
            // create a local Task object instance which holds task information in a HashMap

            t = new Task();
            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                t.setAttribute(
                  rs.getMetaData().getColumnName(i + 1),
                  rs.getString(i + 1));
            }
            t.setAttribute(“MSKEYVALUE”, getMSKEYVALUEFromMSKEY(con, (String) t.getAttribute(“userMSKEY”)));
            this.m_list.add(t);
          } 
          rs.close();
        }
        results = cs.getMoreResults();
      }
      while (results);
      cs.close();

      //added Nov 12th, 2010: retrieve approval information about PVO objects
      CallableStatement cs1 = con.prepareCall(“{call mxp_get_Attr_Approvals(?)};”);
      cs1.setString(1, mskey);
      boolean results1 = cs1.execute();
      int rsCount1 = 0;
      Task t1 = null;
      //Loop through the available result sets.
      do {
        if (results1) {
          ResultSet rs = cs1.getResultSet();
          rsCount1++;
          while (rs.next()) {
           
t1 = new Task();
            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
              t1.setAttribute(
                   rs.getMetaData().getColumnName(i + 1),
                   rs.getString(i + 1));
            }
            t1.setAttribute(“MSKEYVALUE”, 
                 getMSKEYVALUEFromMSKEY(con, (String) t1.getAttribute(“ParentMskey”)));
            t1.setAttribute(“userMSKEY”, (String) t1.getAttribute(“PendingMskey”));
            t1.setAttribute(“TaskId”, (String) t1.getAttribute(“ApprovalTaskid”));
            t1.setAttribute(“TaskName”, (String) t1.getAttribute(“ApprovalTaskname”));
            this.m_list.add(t1);
          } 
          rs.close();
        }
        results1 = cs1.getMoreResults();
      } while (results1);
      cs1.close();
      // end addition Nov 12, 2010   

    } catch (SQLException e) {
      //handle exception
    }
  }

  private String getUserMSKEY(Connection con, String user) throws TaskListException {

    String mskey = null;
    try {
      //get MSKEY for user in a dedicated identity store
     
PreparedStatement ps = con.prepareStatement(
        “SELECT MSKEY FROM MXIV_SENTRIES WHERE AttrName = ? AND SearchValue = ? AND IS_ID = ?;”); 
      //retrieve configuration about the IC unique user attribute -> MSKEYVALUE

      ps.setString(1, this.m_config.getProperty(TaskList.CFG_DB_USER_ATTRIBUTE));
      ps.setString(2, user);
      //retrieve configuration about the Identity store Id
      ps.setInt(3, Integer.parseInt(this.m_config.getProperty(this.CFG_IDM_IDS_ID)));
      ResultSet r = ps.executeQuery();
      while (r.next()) {
        mskey = r.getString(1);
      }
    if (mskey == null || mskey.length() == 0)
      new TaskListException(
        “User <“
          + user
          + “> has no MSKEY entry in the SAP IdentityManagement database.”);
    } catch (SQLException e) {
      //handle exception
    }
    return mskey;
  }

  private String getMSKEYVALUEFromMSKEY(Connection con, String mskey) throws TaskListException {
  
    String mskeyvalue = null;
    try {
      //get MSKEYVALUE for specific MSKEY in a dedicated identity store
     
PreparedStatement ps = con.prepareStatement(
        “SELECT aValue FROM MXIV_SENTRIES WHERE AttrName=’MSKEYVALUE’ AND MSKEY = ?  AND IS_ID = ?;”);
     
ps.setString(1,mskey);
      //retrieve configuration about the Identity store Id
      ps.setInt(2, Integer.parseInt(this.m_config.getProperty(this.CFG_IDM_IDS_ID)));
      ResultSet r = ps.executeQuery();

      while (r.next()) {
        mskeyvalue = r.getString(1);
      }
 
      if (mskeyvalue == null || mskeyvalue.length() == 0)
        new TaskListException(
         “no user found for MSKEY <“
           + mskey + “>”);
   
    } catch (SQLException e) {
      //handle exception
    }
    return mskeyvalue;
  }

The code of the TaskListException used above looks as follows:

  public class TaskListException extends Exception {

    public TaskListException() {
      super();
    }

    public TaskListException(String arg0) {
      super(arg0);
    }

    public TaskListException(Throwable arg0) {
      super(arg0);
    }

    public TaskListException(String arg0, Throwable arg1) {
      super(arg0, arg1);
    }
  }

UWL Connector 

Now that you know how to get the required information from the Identity Center database you have to look into creating a custom UWL connector retriving the information.
You find very good ressources on SDN about UWL connector development here: http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/00ff559e-c563-2910-17a7-c902dbef9a16

Thus I will only briefly name the essential steps:

  1. Create a new “UWL Custom Connector Project” in the SAP NetWeaver developer studio.
  2. Implement your connector
  3. Build a par file
  4. Deploy the par file to your portal server
  5. Register and Configure the IdM UWL connector

I provide more details about the UWL connector implementation in a second blog which you find SAP NetWeaver Identity Management: Approval Items in UWL (Part 2).

To report this post you need to login first.

7 Comments

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

  1. Sietze Roorda
    It shouldn’t come as a surprise that the code must be tweaked somewhat differently for Oracle. However, the corresponding stored procedure “mxp_get_approvers” does not work there. It doesn’t return any results whatsoever.
    (0) 
    1. Oliver Nocon Post author
      Hi Jens,

      thanks for this pointer.
      There is different handling in the system for “old” type of approvals and approval of Pending Value Objects (PVO).
      For PVOs an additional stored procedure is required “mxp_get_Attr_Approvals”.
      I updated the blog accordingly. This hopefully brings you further.

      (0) 

Leave a Reply