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 are references in the cwd_user table and the cwd_group table, both with the name directory_id, and pointing to the appropriate directory id. The cwd_directory_operation table stores the available operations on a directory, based on user permissions.
When there are multiple users with the same name in different directories, JIRA will only recognize the user in the directory with the highest priority. The priority is stored in the directory_ position column.
The ER diagram for user/group relations in JIRA can be drawn as follows:
There is another table, userassociation, that holds the information about watching an issue and voting on an issue. In this table, the source_name column holds the unique username and sink_node_id holds the id of the issue. sink_node_entity has the value Issue and association_type has the value WatchIssue or VoteIssue, depending on the operation.
With the simple layout of the table structure, it is quite easy to list the users, groups, or their relationships by directly accessing the database. For example, we can find all the users in a group by simply running the following command:
SELECT child_name
FROM cwd_membership
WHERE parent_name='jira-administrators'
AND membership_type = 'GROUP_USER'
AND directory_id = 1;
Here, we consider the directory as well, because we can have the same users and groups in different directories.
Properties like full name and e-mail are part of the cwd_user table. But there can be other attributes such as last login time and invalid password attempts that are stored in the cwd_ user_attributes table. We can access those as shown in the following command:
SELECT attribute_name, attribute_value
FROM cwd_user_attributes
WHERE user_id = (SELECT id FROM cwd_user
WHERE user_name = 'someguy' AND directory_id =1);
Users watching an issue can be retrieved as follows:
SELECT source_name
FROM userassociation
WHERE association_type = 'WatchIssue'
AND sink_node_entity = 'Issue'
AND sink_node_id = (SELECT id FROM jiraissue WHERE id='10000');
Similarly, all the issues watched by a user can be retrieved as follows:
SELECT concat(pr.pkey, '-',ji.issuenum) as issuekey
FROM jiraissue ji
INNER JOIN userassociation ua ON ua.sink_node_id = ji.id
INNER JOIN project pr ON ji.project = pr.id
WHERE ua.association_type = 'WatchIssue'
AND ua.sink_node_entity = 'Issue' AND ua.source_name = 'someguy';
It is the same for votes, except that the association type will be VoteIssue.