This page last changed on Mar 20, 2011 by mlassau.

User and Group Tables

JIRA 4.3 and higher uses "Embedded Crowd" as its user management framework.
For the old User and Group tables see Database Schema v4.2.

Users

Users are stored in the CWD_USER table:

COLUMN_NAME DATA_TYPE COMMENTS
ID NUMBER(18,0)  
DIRECTORY_ID NUMBER(18,0) Links to CWD_DIRECTORY
USER_NAME VARCHAR2(255)  
LOWER_USER_NAME VARCHAR2(255) used for case-insensitive search
ACTIVE NUMBER(9,0)  
CREATED_DATE DATE  
UPDATED_DATE DATE  
FIRST_NAME VARCHAR2(255) Not used
LOWER_FIRST_NAME VARCHAR2(255) Not used
LAST_NAME VARCHAR2(255) Not used
LOWER_LAST_NAME VARCHAR2(255) Not used
DISPLAY_NAME VARCHAR2(255)  
LOWER_DISPLAY_NAME VARCHAR2(255)  
EMAIL_ADDRESS VARCHAR2(255)  
LOWER_EMAIL_ADDRESS VARCHAR2(255)  
CREDENTIAL VARCHAR2(255)  

See also CWD_USER_ATTRIBUTES which stores arbitrary "Attributes" against the User.

Group Tables

The groups are stored in the CWD_GROUP table:

COLUMN_NAME DATA_TYPE COMMENTS
ID NUMBER(18,0)  
GROUP_NAME VARCHAR2(255)  
LOWER_GROUP_NAME VARCHAR2(255) used for case-insensitive search
ACTIVE NUMBER(9,0)  
LOCAL NUMBER(9,0)  
CREATED_DATE DATE  
UPDATED_DATE DATE  
DESCRIPTION VARCHAR2(255)  
LOWER_DESCRIPTION VARCHAR2(255)  
GROUP_TYPE VARCHAR2(60)  
DIRECTORY_ID NUMBER(18,0) Links to CWD_DIRECTORY

See also CWD_GROUP_ATTRIBUTES which stores arbitrary "Attributes" against the Group.

Group Membership

The CWD_MEMBERSHIP table records which users belong to which groups.
Note that it is also used to store parent/child relationships for nested groups.

COLUMN_NAME DATA_TYPE COMMENTS
ID NUMBER(18,0)  
PARENT_ID NUMBER(18,0) Parent Group
CHILD_ID NUMBER(18,0) User or nested Group ID
MEMBERSHIP_TYPE VARCHAR2(60) Indicates a Group-User membership or Group-Group membership
GROUP_TYPE VARCHAR2(60) not used
PARENT_NAME VARCHAR2(255) Parent Group
LOWER_PARENT_NAME VARCHAR2(255) used for case-insensitive search
CHILD_NAME VARCHAR2(255) User or child Group
LOWER_CHILD_NAME VARCHAR2(255) used for case-insensitive search
DIRECTORY_ID NUMBER(18,0) Note that this must match the DirectoryId for the Group and User

User Directories

JIRA can have multiple "User Directories".
The Directory Configuration is stored in CWD_DIRECTORY, CWD_DIRECTORY_ATTRIBUTE, and CWD_DIRECTORY_OPERATION.

Watches and Votes

Watches and votes are recorded in the USERASSOCIATION table:

COLUMN_NAME DATA_TYPE COMMENTS
SOURCE_NAME VARCHAR2(60) username
SINK_NODE_ID NUMBER(18,0)  
SINK_NODE_ENTITY VARCHAR2(60)  
ASSOCIATION_TYPE VARCHAR2(60)  
SEQUENCE NUMBER(9,0)  

For example:

mysql> select * from userassociation;
+---------------+--------------+------------------+------------------+----------+
| SOURCE_NAME   | SINK_NODE_ID | SINK_NODE_ENTITY | ASSOCIATION_TYPE | SEQUENCE |
+---------------+--------------+------------------+------------------+----------+
| asmith        |     108433   | Issue            | WatchIssue       |     NULL |
| droberts      |     100915   | Issue            | WatchIssue       |     NULL |
| dfernandez    |     106387   | Issue            | VoteIssue        |     NULL |
...

For example, here user 'asmith' is watching issue with id 108433.

Document generated by Confluence on Mar 27, 2011 18:54