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-c902dbef9...
Thus I will only briefly name the essential steps:
- Create a new "UWL Custom Connector Project" in the SAP NetWeaver developer studio.
- Implement your connector
- Build a par file
- Deploy the par file to your portal server
- 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).
