This article describes how to set the JIRA administrator account to a known password. I need this all the time when working with customer databases to get access to the JIRA administrator functions even when I am not a registered user.
| Note | ||
|---|---|---|
| ||
It is not possible to break into an arbitrary JIRA site using this information. You must have access to the JIRA database to get administrator privileges. |
In newer JIRA instances, the user information is stored in tables starting with cwd_. In older JIRA instances, other tables were used to store the user information. I have documented the SQL statements for both variants.
Find username with administrator permission
You probably need administrator permissions anyway. It is easier to use an existing account with which already is in the jira-administrator group than to modify your own account to be in this group.
For newer JIRA instances use this query:
| No Format |
|---|
jira=# select * from cwd_membership where parent_name='jira-administrators'; id | parent_id | child_id | membership_type | parent_name | lower_parent_name | child_name | lower_child_name | directory_id -------+-----------+----------+-----------------+---------------------+---------------------+-------------+------------------+------------- 10421 | 10002 | 10200 | GROUP_USER | jira-administrators | jira-administrators | schirmacher | schirmacher | 1 jira=# |
For older JIRA instances that do not have the cwd_membership table, use this query:
| No Format |
|---|
mysql> select * from membershipbase where group_name='jira-administrators'; +-------+-------------+---------------------+ | ID | USER_NAME | GROUP_NAME | +-------+-------------+---------------------+ | 10000 | schirmacher | jira-administrators | +-------+-------------+---------------------+ 1 row in set (0.00 sec) |
In case you need to map usernames to actual persons, try this query:
| No Format |
|---|
jira=# select user_name, display_name, email_address from cwd_user; user_name | display_name | email_address -------------+------------------+-------------------- schirmacher | Arne Schirmacher | arne@schirmacher.de jira=# |
Or - if the cwd_user table does not exist - try this one:
| No Format |
|---|
select
username, propertyentry.property_key, propertystring.propertyvalue
from
userbase, propertyentry, propertystring
where
userbase.id=propertyentry.entity_id and
propertyentry.id=propertystring.id
order by
username,property_key;
+---------------------+-----------------------------+---------------------------------------+
| username | property_key | propertyvalue |
+---------------------+-----------------------------+---------------------------------------+
| schirmacher | email | arne.schirmacher@softwaretestingarne@schirmacher.de |
| schirmacher | fullName | Arne Schirmacher |
| schirmacher | jira.user.locale | de_DE |
| schirmacher | login.count | 77 |
| schirmacher | login.lastLoginMillis | 1261244908672 |
| schirmacher | login.previousLoginMillis | 1261135754821 |
| schirmacher | user.notifications.mimetype | text |
...
|
If there are too many rows you can add a and property_key='email' to the where clause.
...
Select the existing password for this user, so that it can be restored at a later time.
| No Format |
|---|
jira=# select user_name, credential from cwd_user where user_name = 'schirmacher'; user_name | credential ------------+--------------------------------------------------------------------------- schirmacher | {PKCS5S2}Ah1Xm7aWkMKyMGE6GZsIeLG1rKA6LXy5dpgWGMPFEx6rL2RrwyH5T2d1v8OzWOQ== |
And for old JIRA instances without cwd_user:
| No Format |
|---|
mysql> select * from userbase where username='schirmacher'; +-------+-------------+------------------------------------------------------------------------------------------+ | ID | username | PASSWORD_HASH | +-------+-------------+------------------------------------------------------------------------------------------+ | 10000 | schirmacher | rRU8enAt79v+s2IMeNDHFbTSf68Cl4gwmPdksIeLG1rMPFEx6r3H6qFudTNsGb5KA6LXy5dpgWGJCo4xbLqKgA== | +-------+-------------+------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
...
Update the password hash of the selected user to a known password hash. The password hash below is from the password "admin".
| No Format |
|---|
jira=# update cwd_user set credential='x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A==' where user_name='schirmacher'; UPDATE 1 jira=# |
And for old JIRA instances without cwd_user:
| No Format |
|---|
mysql> update
userbase
set
password_hash='x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A=='
where
username='schirmacher';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
|
...