Retrieving users and groups from a database

We can find all the information about JIRA users and their groups from the database by running a few simple SQL queries. In this recipe, we will see the various tables involved.

JIRA uses Embedded Crowd as its user management framework. Here, the users are stored in the cwd_user table, groups are in the cwd_group table and the membership details are in the cwd_membership table. It is possible to have the group-user membership or the group-group membership, the latter for nested groups, and this information is also stored in the cwd_membership table. User attributes are stored in the cwd_user_attributes table and group attributes are stored in the cwd_group_attributes table.

JIRA also have the concept of user directories. A JIRA instance can have multiple directories and different directories can have the same name in them. The directory details are stored in the cwd_directory table and its attributes in the cwd_directory_attribute table. There...