Changed: | Jira 4.3 and later uses "Embedded Crowd" as its user management framework. |
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.
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.
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 |
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
.
Consider a query like the following:
1 2select 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 2select 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 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 2mysql> 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.
Rate this page: