Retrieving users and groups from a database

When external user management is not turned ON, 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.

In versions prior to JIRA 4.3, user information is stored in the userbase table, the group information is stored in the groupbase table, and the details of which users belong to which groups are stored in the membershipbase table.

In those versions, user properties are stored using PropertySet, as we have seen earlier in one of the recipes (where we added an address against a user). There will be an entry for the user in the propertyentry table with the entity_name as OSUser and entity_id as the ID of the user in the userbase table. Examples of properties stored are full name and e-mail address and they are stored as String values in the propertystring table.

There is another table, userassociation, that holds the information...