Database – User and Group tables

Changed:

Jira 4.3 and later 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 APP\_USER table.

COLUMN_NAME

DATA_TYPE

COMMENTS

ID

NUMBER(18,0)

 

USER_KEY

VARCHAR(255)

Unique user key

LOWER_USER_NAME

VARCHAR(255)

Links to CWD_USER.LOWER_USER_NAME

CWD_USER table.

COLUMN_NAME

DATA_TYPE

COMMENTS

ID

NUMBER(18,0)

 

DIRECTORY_ID

NUMBER(18,0)

Links to CWD_DIRECTORY

USER_NAME

VARCHAR(255)

 

LOWER_USER_NAME

VARCHAR(255)

used for case-insensitive search

ACTIVE

NUMBER(9,0)

 

CREATED_DATE

DATE

 

UPDATED_DATE

DATE

 

FIRST_NAME

VARCHAR(255)

Not used

LOWER_FIRST_NAME

VARCHAR(255)

Not used

LAST_NAME

VARCHAR(255)

Not used

LOWER_LAST_NAME

VARCHAR(255)

Not used

DISPLAY_NAME

VARCHAR(255)

 

LOWER_DISPLAY_NAME

VARCHAR(255)

 

EMAIL_ADDRESS

VARCHAR(255)

 

LOWER_EMAIL_ADDRESS

VARCHAR(255)

 

CREDENTIAL

VARCHAR(255)

 

See also CWD\_USER\_ATTRIBUTES that 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

VARCHAR(255)

 

LOWER_GROUP_NAME

VARCHAR(255)

used for case-insensitive search

ACTIVE

NUMBER(9,0)

 

LOCAL

NUMBER(9,0)

 

CREATED_DATE

DATE

 

UPDATED_DATE

DATE

 

DESCRIPTION

VARCHAR(255)

 

LOWER_DESCRIPTION

VARCHAR(255)

 

GROUP_TYPE

VARCHAR(60)

 

DIRECTORY_ID

NUMBER(18,0)

Links to CWD_DIRECTORY

See also CWD\_GROUP\_ATTRIBUTES that 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

VARCHAR(60)

Indicates a Group-User membership or Group-Group membership

GROUP_TYPE

VARCHAR(60)

not used

PARENT_NAME

VARCHAR(255)

Parent Group

LOWER_PARENT_NAME

VARCHAR(255)

used for case-insensitive search

CHILD_NAME

VARCHAR(255)

User or child Group

LOWER_CHILD_NAME

VARCHAR(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 main config is stored in CWD\_DIRECTORY.

COLUMN_NAME

DATA_TYPE

COMMENTS

ID

NUMBER(18,0)

 

DIRECTORY_NAME

VARCHAR(255)

 

LOWER_DIRECTORY_NAME

VARCHAR(255)

 

CREATED_DATE

DATE

 

UPDATED_DATE

DATE

 

ACTIVE

NUMBER(9,0)

 

DESCRIPTION

VARCHAR(255)

 

IMPL_CLASS

VARCHAR(255)

 

LOWER_IMPL_CLASS

VARCHAR(255)

 

DIRECTORY_TYPE

VARCHAR(60)

Distinguishes Internal, LDAP, Crowd, etc

DIRECTORY_POSITION

VARCHAR(18,0)

Hierarchy of directories

Details and custom settings are stored in CWD\_DIRECTORY\_ATTRIBUTE.
Available operations (permissions) are stored in CWD\_DIRECTORY\_OPERATION.

Shadowed users

Consider a query like the following:

1
2
3
select user_name, directory_id, display_name, email_address
from cwd_user
where user_name = 'fred'

Normally this should return a single row, however, Jira allows you to set up multiple user directories (for example, multiple LDAP directories, or a single LDAP directory mixed with local users).
It is possible that two or more directories contain the same username.
Now the User Directories have a sort hierarchy and Jira will only recognize the user in the highest priority directory.
To find out which user is in effect, you can change the query to:

1
2
3
4
5
select user_name, directory_id, display_name, email_address, dir.directory_position as position
from cwd_user usr
join cwd_directory dir on dir.id = usr.directory_id
where user_name = 'fred'
order by dir.directory_position

The first user in the list is the actual one that Jira will use.
Any other users are considered as "shadowed" by the first and will be ignored by Jira.

Watches and votes

Watches and votes are recorded in the USERASSOCIATION table.

COLUMN_NAME

DATA_TYPE

COMMENTS

SOURCE_NAME

VARCHAR(60)

username

SINK_NODE_ID

NUMBER(18,0)

 

SINK_NODE_ENTITY

VARCHAR(60)

 

ASSOCIATION_TYPE

VARCHAR(60)

 

SEQUENCE

NUMBER(9,0)

 

CREATED

DATETIME

 

For example:

1
2
3
4
5
6
7
8
mysql> select * from userassociation;
+---------------+--------------+------------------+------------------+----------+---------------------+
| SOURCE_NAME   | SINK_NODE_ID | SINK_NODE_ENTITY | ASSOCIATION_TYPE | SEQUENCE | CREATED             |
+---------------+--------------+------------------+------------------+----------+---------------------+
| asmith        |     108433   | Issue            | WatchIssue       |     NULL | 2018-06-01 11:55:01 |
| droberts      |     100915   | Issue            | WatchIssue       |     NULL | 2018-06-01 12:08:01 |
| dfernandez    |     106387   | Issue            | VoteIssue        |     NULL | 2018-06-01 12:18:35 |
...

For example, here user asmith watches issue with ID 108433.