Restoring Passwords To Recover Admin User Rights

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 2.7 onwards the user management is handled by AtlassianUser. Hence in the database, Confluence will refer to 'USERS' table to store and refer to its users. When you imported your backup on upgrade, what should happen is the users in the 'OS_USER' table should get copied into 'USERS' table.

If you are still using OSUser please refer to our older document.

Learn more about the algorithm Confluence is using.

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

select name from users u, local_members l, groups g where g.groupname = 'confluence-administrators' and g.id=l.groupid and u.id=l.userid;

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 instead cut and a paste a hash, rather than the plain password, over the existing password. 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 users set password =
   'x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A=='
   where name='<USERNAME>';
   

4. Start Confluence
5. Login with your modified username and use password admin

For the evaluation embedded database

1. Shut down Confluence.
2. Open <confluence-home>/database/confluence-db.script. Search for:

   INSERT INTO USERS VALUES(
   

3. Replace the password for the appropriate user, you can copy and paste the hash value above.
4. Save the file, and restart.

If No Local Users Exist

In rare circumstances, when local users are deleted and only LDAP users exist, it may be required to insert a user. Here's how to do that:

insert into users (id, name, password, email, created, fullname) values (1212121, 'admin','x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A==', 'a@a.com', '2009-11-26 17:42:08', 'admin');

// Then find out the ID's of Groups
SELECT * FROM groups;
SELECT * FROM users;

// Add group memberships into local_users
insert into local_members (userid, groupid) values (<from select above for user>,<from_select_above_for_conf_users_group>);
insert into local_members (userid, groupid) values (<from select above for user>,<from_select_above_for_conf_admin_group>);

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

insert into users (id, name, password, email, created, fullname) values(1212121, 'admin',
 'x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A==', 'a@a.com',sysdate,'admin');