Summary

In Crowd, there are certain membership searches which appear to rely on case-sensitive username matching between the cwd_user and cwd_membership tables (more specifically between cwd_membership.child_name and cwd_user.user_name columns). This leads to inconsistent results between views if for whatever reason the two tables do not have the same casing for a given user.

Crowd's cwd_membership table has a lower_child_name column, and it should either use that instead to match cwd_user.lower_user_name, OR, preferably, use the child_id value instead since that references the primary key of cwd_user. A number of columns in cwd_membership appear to be holding redundant info that are already stored in cwd_user, rather than simply referencing cwd_user using the user's ID.

Steps to reproduce

The below example attempts to show one of the inconsistencies caused by differing username casing between the cwd_user and cwd_membership tables. This is a based on a similar issue observed in a support request on a customer instance:

1. In Crowd, create local user "myuser" and local group "mygroup"
2. Place "myuser" into "mygroup"
3. In the database, run the following query to purposely change the casing of the child_name column in the cwd_membership table:

   UPDATE cwd_membership SET child_name = 'MYUSER' WHERE lower_child_name = 'myuser';
   

4. In the Crowd UI, go to Groups > mygroup > Direct Members tab. Note that the user "myuser" is still listed here as a member
5. Click the "Remove users" button, leave the search field blank and click "Search"

Expected behavior

The user "myuser" should be returned, as this user is a member of "mygroup"

Actual behavior

No users are returned, because the value in cwd_membership.child_name ("MYGROUP") does not match that of cwd_user.user_name ("mygroup")

Workaround

The workaround below uses SQL to locate inconsistencies in usernames, and updates them in bulk. It is very important that you backup your database before any modifications, test these queries on a lower environment, have a rollback plan. These queries were written with Postgresql, and if you're using a different DBMS you may need to modify some of the queries to the syntax of that DBMS. Crowd should be shutdown while these queries are being executed.

1. Run this query to find inconsistencies between the two tables:

   SELECT m.child_name, m.lower_child_name, u.user_name, u.lower_user_name, u.id
   FROM cwd_membership m
   JOIN cwd_user u
     ON m.child_id = u.id
   WHERE membership_type = 'GROUP_USER'
     AND m.child_name <> u.user_name;
   

2. Run this query to update the cwd_membership table in order to resolve the inconsistencies

   UPDATE cwd_membership m
   SET child_name = u.user_name
   FROM cwd_user u
   WHERE m.child_id = u.id
     AND m.membership_type = 'GROUP_USER'
     AND m.child_name <> u.user_name;