Most Portal Administrators at some point in time are asked by IBM Support to run a procedure known as "CleanupUsers", documented in the following Technote:
In most cases, this Technote will fix one or more of the following issues:
- Blank lines in Resource Permissions portlet
- Permissions not working for some users, but working for other users
- Recent LDAP migration occurred and need to permissions remain the same in the Portal.
However, the Technote is light on details as to why the CleanupUsers procedure is needed and exactly what data it modifies in the Portal database. This blog entry will provide those details and discuss which use case scenario for CleanupUsers you may wish to run for your Portal environment.
Overview of Database Tables
- Portal stores data about users and groups in its databases. The main item of interest for discussion in this article is the USER_DESC table in the release database.
- Two pieces of information are needed to identify any user or group to Portal
- The full distinguished name (DN) of the user or group
- The externalID attribute (extID) of the user or group
- By default, my USER_DESC table on a clustered system looked like the following:
- There are several built in userIDs that are internal to the Portal server, these should be left alone / never modified.
- The other items in there are the default Portal administrator, and, the default Portal administrator group. In this case, we see each entry listed TWICE. The UNIQUE_ID field, corresponding to the external ID attribute is outdated for 2 of these 4 entries.
- Looking at this table alone, we cannot determine which entry is outdated. We would need to go directly to the data source which contains these user(s) and group(s) to determine the most current value of these two items. Given these items are file repository users and groups, we will check the following file on my lab system:
- We see the two correct entries in here
…indicating the “86” wpsadmin user and “e4” wpsadmins group in the database is outdated. The cleanup users process should be run to cleanup this data.
- Indeed the Cleanupusers procedure returned two results to cleanup:
- With the contents of the invalidusergroups.xml file as follows:
<?xml version="1.0" encoding="UTF-8"?>
<!-- 1 [user Z9eAeOHDI3OKCJ9DAMM474RDIJMG63RCIJM8CJ1EAMMG6NPD46Q8C2BD43R86N1] -->
<!-- 2 [group Z8eAe53DI3R8CL9CIJMK6O1CAJMG61JCAJM8C63PCJMOCP1P86HT6IHPAMIDCG1] -->
<request build="wp7002CF23_001_08" cleanup-users="invalid" type="update" version="220.127.116.11" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="PortalConfig_7.0.0_2.xsd">
<user action="delete" domain="rel" name="uid=wpsadmin,o=defaultwimfilebasedrealm" objectid="Z9eAeOHDI3OKCJ9DAMM474RDIJMG63RCIJM8CJ1EAMMG6NPD46Q8C2BD43R86N1"/>
<group action="delete" domain="rel" name="cn=wpsadmins,o=defaultwimfilebasedrealm" objectid="Z8eAe53DI3R8CL9CIJMK6O1CAJMG61JCAJM8C63PCJMOCP1P86HT6IHPAMIDCG1"/>
<status element="all" result="ok"/>
- Our next steps will be to clean up the stale database for these two items from the Portal database. Note, the changes will ONLY impact the Portal database. LDAP user repositories, file repositories, etc. are NOT touched by this process.
Test Case #1 - Cleanup, No Migrate
Inspecting the invalidusergroups.xml file, we can see that cleanup-users="invalid" is set by default. This indicates that the duplicate users will be removed from the database completely. In my particular use case with outdated users, this is the correct test case to implement. We already know duplicates exist through visual inspection. We just want to get rid of the duplicate.
Which produced a correct end result in the database:
…indicating the “86” wpsadmin user and “e4” wpsadmins group which were stale in the database were moved.
So what is cleanupusers actually doing during this time? For each entry in the database, CleanUpUsers will attempt to query the live user repositories (LDAP, File Repository, etc.) for that user data. If a match is found, CleanupUsers will compare the data in this database table against the match that was found against the data in the live repository. If the live repository contains updated data, CleanupUsers will mark the data to be deleted.
For this specific use case, why did I have a duplicate wpsadmin user and duplicate wpsadmins group? Answer: Because I was in a cluster.
When you first install Portal in a standalone server configuration, it installs an admin username of choice (I chose wpsadmin), and a hardcoded admin group name of wpsadmins. That created two entries in the database. Later, when I installed the DMGR, I had to specify an administrative user (I also chose wpsadmin here). This wpsdamin user in the DMGR file repository had the same DN, but a DIFFERENT extID than the wpsadmin user in the Portal file repository. What about the wpsadmins group? DMGRs do not require groups to function. This is correct, however, when you augment the Portal profile to the DMGR, it creates a wpsadmins group there as well. Once again, the wpsdamins group in the DMGR file repository had the same DN, but a DIFFERENT extID than the wpsadmins group in the Portal file repository. Once I federated Portal to the DMGR, the DMGR file repository became the master copy and overwrote Portal’s file repository. Portal saw the change in the extID for the wpsadmin user and wpsadmins group, (no change in DN, just the extID) and ended up creating duplicates in the database. Hence, if you keep the same user and group name in the file repository for both your DMGR and Portal, you should see at least two entries reported by cleanupusers.
Before we move to the next test cases, we’ll need to setup a live LDAP data to have real data to work with. File repository data can still be used, but this test case makes more sense in the context of an LDAP being used. In this case, the LDAP in use is Tivoli Directory Server, and our ibm-entryuuid attribute will become the extID attribute in the database. Looking at the LDAP data itself:
Note, my database is NOT yet populated with this data. Portal will populate the database on the first lookup of a user or group. Let’s do this via the Manage Users & Groups portlet now.
And the resulting entries in the database
Note that it wasn’t just the user that was populated in there, but also the user’s groups!
As a quick test case, I moved my user to a different location in the LDAP. Do we see duplicates in the database? Answer is NO! Portal code automatically updated the database with the new location correctly. Why? The extID is the primary key! Portal code is able to handle such an update in runtime.
OK, that covers the use case of the DN changing. What about the extID changing? Directly changing the extID in the LDAP isn’t easy and most LDAPs forbid it. In most cases extIDs get changed when an entry in LDAP is deleted and then recreated. I’m going to do that now with my “travis” user.
My end result is the same DN, different extID:
In this particular test case, I have an old userID - ending in “46b”, that may have permissions associated with it in the Portal. Once again I have duplicates, so I’d want to get rid of the old ID while preserving the new ID. After running the script, the stale user is successfully removed.
Test Case #2 - No Cleanup, Migrate Only
This is the recommend use case for most scenarios, and, what the primary Technote for CleanpUsers recommends: https://www-304.ibm.com/support/docview.wss?uid=swg21377025. This is most often associated with the “blank lines” issue. We will swap out the existing bad IDs for new IDs. Existing data will be updated, no data will be removed. Once this is completed, it is recommended you test and ensure end users can use your Portal successfully.
Note: The reason we need to migrate data is because the data is spread out across MANY tables in the release database. USER_DESC is a linkage between these tables. However, cleaning USER_DESC by itself is not sufficient. For example, the LNK_USER_ROLE table ALSO contains the extID of the userID!!!
For this test case, we will perform a similar delete and recreate of a test user. However, rather than performing a lookup in the Manage Users & Groups portlet resulting in a duplicate in the database, we will INSTEAD run the CleanupUsers.xml process. This would simulate a real scenario where LDAPs were recently changed and we need to update items in the database to match the new LDAP.
Here’s our original user in the Portal database:
Here’s our recreated user in the LDAP server:
And with our run of CleanupUsers we get a report this user needs to be cleaned up. Rather than deleting the user, I will instead migrate this user. We should see this also occur in the LNK_USER_ROLE table shown previously:
<?xml version="1.0" encoding="UTF-8"?>
<request build="wp7002CF23_001_08" cleanup-users="false" migrate-users="true" type="update" version="18.104.22.168" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="PortalConfig_7.0.0_2.xsd">
<user action="delete" domain="rel" name="uid=travis,cn=realmp,ou=wplc,o=ibm,c=us" objectid="Z9eAeI1DE3O4CK9CIJMGCJ1DEJMG66RCCMM076JCAMMS6N1EIJG966JD4JG56G1"/>
Test Case #3 - Cleanup and Migrate Only
This is recommend only if #2 fails, and if #2 fails, we recommend you contact IBM Support via a PMR to determine why. This is a combination of #1 and #2 such that we will swap out the existing old IDs for new IDs, existing data will be updated, and stale data will be removed. While it may appear advantageous to run this approach as it requires fewer steps, it also does not permit testing of functional correctness. i.e. If for some reason the user data is not migrated, poof, its no longer available as the invalidation of the stale data will have occurred all in a single operation.
*Note: This test case will not be covered with an example given this is a combination of the first two test cases, and, the first two test cases are covered in detail above.
A Note on MemberFixer
This blog entry is focused specially on CleanupUsers being run against the release database. The MemberFixer procedure operates in a similar manner as the CleanupUsers procedure only against content in the WCM database. Data in the jcr database is dependent on data in the release database being correct. Therefore, if you use WCM content content in your Portal environment, be SURE to run the CleanupUsers procedure in full before beginning the MemberFixer procedure. This blog entry will not cover memberfixer in detail. Look for a future blog entry on details of memberfixer.
A Note on ExtID and DN changes
If you have a use case where both the extID and DN will be changing, please open a PMR with IBM Support. There are options available to handle this use case scenario and a case-by-case evaluation is needed to determine which option is best suited for your Portal envrionment.
Portal as a product offers a means to guard against LDAP data changing. The CleanupUsers process allows for a manual cleanup of data in the Portal database at a time of the Portal administrator's choosing. Its two step operation - i.e. report the bad data and then clean it up - also allows a manual intervention to prevent certain data from being cleaned up if so determined by the Portal administrator. This procedure may seem a bit odd at times, however, it does allow for data integrity of user data.