Use this document if you are unable to log in to Confluence as administrator. The most common reason for using these instructions is if you have lost the administration password for your Confluence site.

Before you Start

Please note the following before you start:

  • The following instructions include example SQL that should work on MySQL and PostgreSQL. You may need to customise the queries for other databases or for your installation.
  • We strongly recommend testing the queries on a test database before modifying your production database.
New user management in Confluence 3.5 and later
  • Confluence now uses the CWD_USER table in the database to store and refer to its users.
  • During an upgrade from Confluence 3.4.9 or earlier, the upgrade process copied the users from the OS_USER table (for upgrades from versions older than 2.7) or the USERS table (for versions 2.7 to 3.4) into the CWD_USER table.
  • The new user management framework also introduced user directories. Making modifications to users in the database will only fully work for users in Confluence's Internal Directory. The instructions below include extra steps for instances in which the user management has been delegated to external sources (via LDAP, Crowd or JIRA).

Please refer to the older documentation if you are still using OSUser or AtlassianUser.

Using Crowd for SSO
  • If Confluence is configured for SSO through Crowd, you will only be able to authenticate as users from the Crowd server.
  • This document covers how to recover administration rights from the local 'Confluence Internal Directory' only. However, you will not be able to authenticate as a local Confluence administrator while Crowd SSO is enabled. Please refer to Integrating Crowd with Atlassian Confluence for details on how to configure or disable Crowd SSO.

On this page:

Step 0. Get access to the database

If you are using the embedded HSQL database, you can find the files containing your database in <confluence-home-directory>/database. When you shut down Confluence, the SQL will be written to a '.script' or '.log' file in that directory to which you can append the SQL described below.

If you are using a proper production database, connect to the database with your normal tools. You will need to have permission to run queries and update data in the database.

Step 1. Identify Administrator

To find out which usernames have admin privileges, connect to your database using a database admin tool such as DBVisualiser. Please download a database admin tool now if you do not have one installed already. Then connect to your database and retrieve the list of administrator usernames and IDs with:

select u.id, u.user_name, u.active from cwd_user u
join cwd_membership m on u.id=m.child_user_id join cwd_group g on m.parent_id=g.id join cwd_directory d on d.id=g.directory_id
where g.group_name = 'confluence-administrators' and d.directory_name='Confluence Internal Directory';

If there are multiple results, choose one ID/username combination to use for the following steps.
If there are no results, skip down to If No Local Administrator Exists.

 

It is important to make sure that the "active" field contains a value of "T". Without this flag trying to authenticate with this user is a non starter.

To set active to true run the following query replacing "<user_name>" with the user name from the previous query

UPDATE cwd_user
SET active = 'T'
WHERE user_name ='<user_name>';

If No Local Administrator Exists

There may be no administrators in your Internal Directory. If this is the case, you need to add one:

  1. Add a new admin user by running:

    insert into cwd_user(id, user_name, lower_user_name, active, created_date, updated_date, first_name, lower_first_name, last_name, lower_last_name, display_name, lower_display_name, email_address, lower_email_address, directory_id, credential) values (1212121, 'admin', 'admin', 'T', '2009-11-26 17:42:08', '2009-11-26 17:42:08', 'A. D.', 'a. d.', 'Ministrator', 'ministrator', 'A. D. Ministrator', 'a. d. ministrator', 'admin@example.com', 'admin@example.com', (select id from cwd_directory where directory_name='Confluence Internal Directory'), 'x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A==');


insert into user_mapping values ('2c9681954172cf560000000000000001', 'admin', 'admin');

  2. Add new groups by running:

    insert into cwd_group(id, group_name, lower_group_name, active, local, created_date, updated_date, description, group_type, directory_id)
values ( '888888','confluence-administrators','confluence-administrators','T','F','2011-03-21 12:20:29','2011-03-21 12:20:29',NULL,'GROUP',(select id from cwd_directory where directory_name='Confluence Internal Directory'));
insert into cwd_group(id, group_name, lower_group_name, active, local, created_date, updated_date, description, group_type, directory_id)
values ( '999999','confluence-users','confluence-users','T','F','2011-03-21 12:20:29','2011-03-21 12:20:29',NULL,'GROUP',(select id from cwd_directory where directory_name='Confluence Internal Directory'));

  3. Add group memberships into cwd_membership:

    insert into cwd_membership (id, parent_id, child_user_id) values (888888, (select id from cwd_group where group_name='confluence-users' and directory_id=(select id from cwd_directory where directory_name='Confluence Internal Directory')), 1212121);
insert into cwd_membership (id, parent_id, child_user_id) values (999999, (select id from cwd_group where group_name='confluence-administrators' and directory_id=(select id from cwd_directory where directory_name='Confluence Internal Directory')), 1212121);

(warning) If using an Oracle database, use sysdate instead of a string for the created_date column.

Step 2. Replace Administrator Password

Confluence does not store passwords in plain text in the database, but uses hashes computed from the original password. You will need to insert a hash, rather than the plain password, over the existing password in the database. Below is the hash for the password admin

x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A==

For an External Database

To change the password to admin for a given username:

  1. Shut down Confluence.
  2. Connect to your database.

  3. Run the following SQL:

    update cwd_user set credential =
'x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A=='
where id=<id from Stage 1>;

For the Evaluation Embedded HSQL Database

To change the password to admin for a given username:

  1. Shut down Confluence.
  2. Open <confluence-home>/database/confluencedb.script, or confluencedb.log if the .script file looks empty.

  3. Search for:

    INSERT INTO CWD_USER VALUES(
  4. Keep searching until you find the appropriate user, then replace their password with the hash value above.
  5. Save the file.
  6. Restart Confluence.

Step 3. Put the Internal Directory in First Position

Start Confluence, and try logging in with the username of the user you updated/created and the password 'admin'. If this works, skip to Step 4. Otherwise, your Internal Directory does not have high enough priority.

To put your Internal Directory in first position:

  1. Find the directory names and their order:

    select d.id, d.directory_name, m.list_index from cwd_directory d join cwd_app_dir_mapping m on d.id=m.directory_id;
  2. Take note of the ID with list_index 0, and the list_index and ID of the Confluence Internal Directory.

  3. Switch the order of the directories:

    update cwd_app_dir_mapping set list_index = 0 where directory_id = <Internal Directory id>;
update cwd_app_dir_mapping set list_index = <Noted Internal Directory list_index> where directory_id = <Directory id that had list_index 0>;

  4. Check to see if the directory is active (the 'active' column should be set to 'T'):

    select id, directory_name, active from cwd_directory where id = <Internal Directory id>;

  5. If necessary, activate the directory:

    update cwd_directory set active = 'T' where id = <Internal Directory id>;

Step 4. Clean Up

To tidy up:

  1. Start Confluence.
  2. Log in with your modified/created username and use password admin
  3. Change your password. Do not leave your password as admin, or your instance will not be secure.
  4. If you created a new user in Stage 2, create a new admin via the UI and delete the admin you created in Stage 2.
  5. If you followed Stage Three, go to Confluence Administration > User Directories and rearrange your directories so they are correctly configured again.

Notes