It does not take long before the NW IDM Administrator starts peeking under the hood to see what sits in the database. This is not a bad thing since there is a lot of good information that one can gain here, especially in the way of analytics and audit data. Understanding the database structure is also important in terms of developing and troubleshooting access rules and other Identity Store SELECT criteria. This posting will talk about a couple of key characteristics of the NW IDM Identity Store database. One of the great things about NW IDM is that there are virtually no differences between the Oracle and MS SQL database schemas so this article works for both environments.
This article will be one of several in a series covering the NW IDM Database structure. We would be very interested in hearing about other areas of the back end that should be discussed.
When looking at these tables one of the first things that people notice is that there is seemingly no direct reference to the MSKEYVALUE attribute and that there is a value for something called an MSKEY. Consider this extract from a sample Identity Store regarding the user Johnny Scooter:
| mskey | attr_id | aValue | SearchValue |
| 187 | 2 | JScooter | JSCOOTER |
| 187 | 1 | MX_USER | MX_USER |
| 187 | 4 | Johnny Scooter | JOHNNY SCOOTER |
| 187 | 36 | Johnny | JOHNNY |
| 187 | 37 | 732 123 4567 | 732 123 4567 |
| 187 | 39 | Scooter | SCOOTER |
| 187 | 40 | Information Systems | INFORMATION SYSTEMS |
| 187 | 41 | 732 765 4321 | 732 765 4321 |
| 187 | 42 | Systems Engineer | SYSTEMS ENGINEER |
| 187 | 55 | CN=JScooter,OU=IdM,DC=nwidm,DC=local | CN=JSCOOTER,OU=IDM,DC=NWIDM,DC=LOCAL |
| 187 | 49 | Johnny.Scooter@nwidm.local | JOHNNY.SCOOTER@NWIDM.LOCAL |
This is a snapshot from the MXI_VALUES table listing four columns, mskey, attr_id, aValue and SearchValue. (For a complete listing of the NW IDM Identity store schema take a look at this document.) The aValue and SearchValue show the attributes’ values in the form that they were entered into the system and a globally consistent format for easy searching respectively. So going back to our question from above, where’s the MSKEYVALUE. If we look in MXI_ATTRIBUTES we’ll find:
| Attr_ID | AttrName |
| 2 | MSKEYVALUE |
| 1 | MX_ENTRYTYPE |
| 4 | DISPLAYNAME |
| 36 | MX_FIRSTNAME |
| 37 | MX_PHONE_ADDITIONAL |
| 39 | MX_LASTNAME |
| 40 | MX_DEPARTMENT |
| 41 | MX_PHONE_PRIMARY |
| 42 | MX_TITLE |
| 55 | MX_MAIL_PRIMARY |
| 49 | AD_DN |
So this answers the first question, how do you match up those Attr_ID’s anyway as there is a direct link between the two tables via the attr_id column. The second and more important question is that a value of “2” for Attr_ID corresponds to MSKEYVALUE. So what is going on here, anyway? To begin; let us look at an excerpt from the view MXIV_SENTRIES about our friend Johnny Scooter, which is a separate, friendlier representation of MXI_VALUES since there’s more name information rather than value references.
| mskey | AttrName | aValue | SearchValue |
| 187 | MSKEYVALUE | JScooter | JSCOOTER |
| 187 | MX_ENTRYTYPE | MX_USER | MX_USER |
| 187 | DISPLAYNAME | Johnny Scooter | JOHNNY SCOOTER |
| 187 | MX_FIRSTNAME | Johnny | JOHNNY |
| 187 | MX_PHONE_ADDITIONAL | 732 123 4567 | 732 123 4567 |
| 187 | MX_LASTNAME | Scooter | SCOOTER |
| 187 | MX_DEPARTMENT | Information Systems | INFORMATION SYSTEMS |
| 187 | MX_PHONE_PRIMARY | 732 765 4321 | 732 765 4321 |
| 187 | MX_TITLE | Systems Engineer | SYSTEMS ENGINEER |
| 187 | AD_DN | cn= JScooter,OU=IdM,DC=nwidm,DC=local | CN= JSCOOTER, JSCOOTER,OU=IDM,DC=NWIDM,DC=LOCAL |
| 187 | MX_MAIL_PRIMARY | Johnny.Scooter@nwidm.local | JOHNNY.SCOOTER@NWIDM.LOCAL |
Basically, there are two identifiers that are in use by NW IDM, the first is the more publicly known MSKEYVALUE. This unique identifier is exposed via the Workflow User Interface and is easily seen in MXIV_SENTRIES. However when we look at MXI_VALUES we see that the references to MSKEYVALUE are harder to root out as we need to go through the Attributes table. We also see a reference to the MSKEY field, which appears to be an identifier in both the MXIV_SENTRIES view and in the MXI_VALUES table. This tells us that NW IDM uses the MSKEY field as an internal unique Identifier. Why is this? I would say that the most basic reason is that the MSKEYVALUE is subject to change and would thus be ineligible for use as a link between tables. Even though we have only spoken about user identities, all entries in the Identity store (users, roles, privileges, entry types) have both MSKEYs and MSKEYVALUES. Therefore, if the MSKEYVALUE were the only identifier it would be a poor choice for linking between tables as a foreign key link as referential integrity issues would soon arise.
So to wrap up, what can we conclude from all of this?
- MSKEYVALUE is the unique identifier used by NW IDM at the workflow layer and is changeable
- MSKEY is the internal unique identifier and is not changeable. It is also used as a foreign key link between various NW IDM tables
- MXI_VALUES is an NW IDM table holding information about all of the objects in NW IDM
- MXIV_SENTRIES is a NW IDM View that holds a “friendlier” representation of MXI_VALUES
Netweaver Identity Management Weblog 