This page last changed on Mar 14, 2011 by akdominguez.

Use this document if you are unable to login as administrator, to manually replace administrator passwords or give users administration rights.

New Confluence User Management

From Confluence 3.5 onwards the user management is handled by Embedded Crowd. Hence in the database, Confluence will refer to the 'CWD_USERS' table to store and refer to its users. When you imported your backup on upgrade, the users in the 'OS_USER' table (for upgrades from versions older than 2.7) or 'USERS' table (for versions 2.7 to 3.4) will have been copied into the 'CWD_USERS' table. Embedded Crowd also introduced the idea of User Directories. Making modifications to users in the database will only fully work for users in Confluence's Internal Directory. The following instructions 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.

Learn more about the algorithm Confluence is using.

The following instructions have been tested on MySQL and PostgreSQL. Please be cautious when using other databases.

Stage One - 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. Once installed, connect to your database and retrieve the list of administrator usernames and ids with:

select u.id, u.user_name 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 stages.
If there are no results, skip down to "If No Local Users Exist" in Stage Two.

Stage Two - 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==

To change the password to admin for a given username:

  1. Shutdown Confluence
  2. Connect to your database.
  3. The SQL to run is:
    update cwd_user set credential =
    'x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A=='
    where id=<id from Stage 1>;

For the evaluation embedded database

  1. Shut down Confluence.
  2. Open <confluence-home>/database/confluencedb.script, or confluencedb.log if the .script file looks empty. Search for:
    INSERT INTO CWD_USER VALUES(
  3. Keep searching until you find the appropriate user, then replace their password with the hash value above.
  4. Save the file, and restart.

If No Local Users Exist

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

  1. Find the id for the internal directory by running
    select id from cwd_directory where directory_name='Confluence Internal Directory';
  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', <Internal Directory Id>, 'x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A==');
  1. Then find out the ID's of Groups
    select id from cwd_group where group_name='confluence-users' and directory_id=<Internal Directory Id>;
    select id from cwd_group where group_name='confluence-administrators' and directory_id=<Internal Directory Id>;
    
    # Add group memberships into cwd_membership
    {code:sql}
    insert into cwd_membership (id, parent_id, child_user_id) values (888888, <confluence-users id>, 1212121);
    insert into cwd_membership (id, parent_id, child_user_id) values (999999, <confluence-administrators id>, 1212121);

With Oracle, use sysdate instead of a string to the created column.

Stage Three - 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 Stage Four. Otherwise, your Internal Directory does not have high enough priority. You can fix this like so:

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

Stage Four - Cleanup

  1. Start Confluence
  2. Login 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.
Document generated by Confluence on Mar 16, 2011 18:34