Purpose

For auditing or administration purposes, an administrator may want to export a list of all the users and their associated group memberships. This can be done via a SQL query.

Solution

Use the following SQL query:

SELECT d.directory_name AS DirectoryName, d.directory_type AS DirectoryType, g.group_name AS Group, u.email_address AS Email, u.user_name AS username, u.display_name as displayname
FROM
cwd_user u
JOIN cwd_directory d
ON
u.directory_id = d.id
JOIN cwd_membership m
ON
u.id = m.child_user_id
JOIN cwd_group g
ON
g.id = m.parent_id
ORDER BY u.user_name ASC, d.id, g.group_name ASC; 

Notes:

• The cwd_user table stores information about the users’ profiles.
• The cwd_group table stores the group names
• The cwd_membership table stores the membership between groups and users.
• Group memberships are associated with the directory where the user comes from. If the same user appears in multiple user directories, they could have different group memberships. So, the cwd_directory table is useful to help us identify that.