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.
|