Database - User and Group tables

Changed:

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.

User and Group Tables

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

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

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

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:

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 (eg 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 recognise the user in the highest priority directory.
To find which user is in effect, you can change the query to:

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)

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.

Was this page helpful?

Have a question about this article?

See questions about this article

Powered by Confluence and Scroll Viewport