Recover lost Confluence password
复方法：
1. 运行此sql 找到你的管理员帐户：
- 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';
并记住管理员帐户的id
2. 运行此sql, 恢复管理员密码为 admin
- update cwd_user set credential =
- 'x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A=='
- where id=xxxxxx;
注意此处xxxxxx 为上一步的 id
如果你的密码是{PKCS5S2}前缀开头的，则用下面这个sql:
- update cwd_user set credential =
- '{PKCS5S2}ltrb9LlmZ0QDCJvktxd45WgYLOgPt2XTV8X7av2p0mhPvIwofs9bHYVz2OXQ6/kF'
- where id=xxxxxx;
这个管理员密码为 Ab123456
如果使用hsql，请用下面语句登录hsql数据库
java -cp /opt/atlassian/confluence/WEB-INF/lib/hsqldb-2.3.0.jar org.hsqldb.util.DatabaseManager -user sa -url jdbc:hsqldb:/data/confluence/data/database/confluencedb java -cp hsqldb-2.3.0.jar org.hsqldb.util.DatabaseManager -user sa -url jdbc:hsqldb:confluencedb
http://blog.javachen.com/2014/02/21/install-confluence5-4-2.html
https://confluence.atlassian.com/display/JIRAKB/Running+SQL+commands+in+a+HSQL+database
https://confluence.atlassian.com/display/DOC/Restoring+Passwords+To+Recover+Admin+User+Rights
hsql数据库使用详解(入门)及快速使用
http://blog.csdn.net/today1858/article/details/5949633
安装confluence-5.4.4-x64
1、安装Centos6.5
默认安装完成。并设置好网络。
Running SQL commands in a HSQL database
About
On rare occasions, you may wish to run raw SQL queries on a JIRA application internal database which is used for evaluation purposes. This page describes how to obtain a SQL console for HSQLDB databases, which are built into JIRA applications for evaluation purposes.
How to obtain an HSQL Console
Step 1: Locate HSQLDB directory
HSQL stores its database as text files in the filesystem. Typically these files will be in a database subdirectory of your JIRA application Home Directory:
Step 2: Locate HSQLDB jar
The hsqldb jar file is located in the lib sub-directory of the JIRA application Installation directory:
Step 3: Shut down your JIRA application
Step 4: Run The Console
Use the following command to bring up the HSQLDB console (replacing the JIRA_HOME and JIRA_INSTALL with the absolute paths)
Window:
java -cp "C:/Program Files/Atlassian/JIRA/lib/hsqldb-1.8.0.5.jar" org.hsqldb.util.DatabaseManager -user sa -url "jdbc:hsqldb:C:/Program Files/Atlassian/Application Data/JIRA/database/jiradb;shutdown=true"
Unix:
java -cp JIRA_INSTALL/lib/hsqldb-1.8.0.5.jar org.hsqldb.util.DatabaseManager -user sa -url 'jdbc:hsqldb:JIRA_HOME/database/jiradb;shutdown=true'
In versions of JIRA prior to JIRA 4.1 the jar file was in common/lib
The hsqldb console should load, listing tables in the database in the left panel. You can run SQL commands in the top panel:
Step 5: Shutdown The Console
Once you have finished running SQL queries, shut down the console before starting your JIRA application.
Troubleshooting
No tables are shown
If you can successfully bring up the console, but no tables are shown, the jdbc:hsqldb: the usual reasons are
- JIRA application/Confluence is still running. Ensure that the applications are shutdown before running the console.
- Path of the database file may be incorrect. You can update this by navigating to
File > Connectand entering in the absolute path toJIRA_HOME/database/jiradbin theURL box and clicking Ok.
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 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:
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:
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:
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:
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.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.
Note existing password
Select the existing password for this user, so that it can be restored at a later time.
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:
mysql> select * from userbase where username='schirmacher'; +-------+-------------+------------------------------------------------------------------------------------------+ | ID | username | PASSWORD_HASH | +-------+-------------+------------------------------------------------------------------------------------------+ | 10000 | schirmacher | rRU8enAt79v+s2IMeNDHFbTSf68Cl4gwmPdksIeLG1rMPFEx6r3H6qFudTNsGb5KA6LXy5dpgWGJCo4xbLqKgA== | +-------+-------------+------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Reset password
Update the password hash of the selected user to a known password hash. The password hash below is from the password "admin".
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:
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
Log in
Now log in using the chosen username with password "admin". If it doesn't work, you need to reboot the JIRA application server as the password was probably cached in the application.
After finishing your administration tasks, remember to put back the original password.
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_USERtable 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_USERtable (for upgrades from versions older than 2.7) or theUSERStable (for versions 2.7 to 3.4) into theCWD_USERtable. - 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 usingOSUser 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 toIntegrating Crowd with Atlassian Confluence for details on how to configure or disable Crowd SSO.
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 ujoin 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_idwhere 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_userSET 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:
-
Add a new admin user by running:
insertintocwd_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', (selectidfromcwd_directorywheredirectory_name='Confluence Internal Directory'),'x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A=='); -
Add new groups by running:
insertintocwd_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',(selectidfromcwd_directorywheredirectory_name='Confluence Internal Directory'));insertintocwd_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',(selectidfromcwd_directorywheredirectory_name='Confluence Internal Directory')); -
Add group memberships into cwd_membership:
insertintocwd_membership (id, parent_id, child_user_id)values(888888, (selectidfromcwd_groupwheregroup_name='confluence-users'anddirectory_id=(selectidfromcwd_directorywheredirectory_name='Confluence Internal Directory')), 1212121);insertintocwd_membership (id, parent_id, child_user_id)values(999999, (selectidfromcwd_groupwheregroup_name='confluence-administrators'anddirectory_id=(selectidfromcwd_directorywheredirectory_name='Confluence Internal Directory')), 1212121);
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:
- Shut down Confluence.
- Connect to your database.
-
Run the following SQL:
updatecwd_usersetcredential ='x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A=='whereid=<idfromStage 1>;
For the Evaluation Embedded HSQL Database
To change the password to admin for a given username:
- Shut down Confluence.
- Open
<confluence-home>/database/confluencedb.script, orconfluencedb.logif the .script file looks empty. -
Search for:
INSERT INTO CWD_USER VALUES( - Keep searching until you find the appropriate user, then replace their password with the hash value above.
- Save the file.
- 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:
-
Find the directory names and their order:
selectd.id, d.directory_name, m.list_indexfromcwd_directory djoincwd_app_dir_mapping mond.id=m.directory_id; - Take note of the ID with list_index 0, and the list_index and ID of the Confluence Internal Directory.
-
Switch the order of the directories:
updatecwd_app_dir_mappingsetlist_index = 0wheredirectory_id = <Internal Directory id>;updatecwd_app_dir_mappingsetlist_index = <Noted Internal Directory list_index>wheredirectory_id = <Directory id that had list_index 0>; -
Check to see if the directory is active (the 'active' column should be set to 'T'):
selectid, directory_name, activefromcwd_directorywhereid = <Internal Directory id>; -
If necessary, activate the directory:
updatecwd_directorysetactive ='T'whereid = <Internal Directory id>;
Step 4. Clean Up
To tidy up:
- Start Confluence.
- Log in with your modified/created username and use password
admin - Change your password. Do not leave your password as admin, or your instance will not be secure.
- 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.
- If you followed Stage Three, go to Confluence Administration > User Directories and rearrange your directories so they are correctly configured again.
Occasionally it is necessary to log in into a Confluence System for which you do not have the password.
This happens if you work in customer support and a customer sends you a Confluence dump for support or debugging purposes. Most likely the customer has set his Confluence instance to private mode, so that you can't create an account to log in. Even if not, you do not have admin privileges.
The solution is to find an existing Confluence Administrator account and reset the password to a known one.
To list all Confluence Administrators in a given Confluence 4.x instance, issue the SQL command below:
select
user_name,
display_name,
email_address
from
cwd_user,
cwd_membership,
cwd_group
where
cwd_user.id = cwd_membership.child_user_id and
cwd_membership.parent_id = cwd_group.id and
cwd_group.group_name='confluence-administrators';
For previous Confluence releases, use this command instead:
select
users.id,
name,
email
from
local_members,
users,
groups
where
local_members.userid=users.id and
local_members.groupid=groups.id and
groupname='confluence-administrators';
You should get a response similar to this:
+--------+---------------------------+-------------------------------------+--------------------------------------------------------------+ | id | name | email | | +--------+---------------------------+-------------------------------------+--------------------------------------------------------------+ | 393217 | admin | arne.schirmacher@softwaretesting.de | rRU7enAt79vrMPFEx6r3H6qFudTNsGb5KA6LXy5dpgWGJCo4xbLqKgA...== | +--------+---------------------------+-------------------------------------+--------------------------------------------------------------+ 1 rows in set (0.00 sec)
If you have an old Confluence site (< v. 2.5), the user information might be stored in different tables. If the statement above does not return any rows, try the next one:
select
*
from
os_user,
os_group,
os_user_group
where
os_group.groupname='confluence-administrators' and
os_user_group.group_id=os_group.id and
os_user_group.user_id=os_user.id;
You should get a response similar to this:
+----+-------------+---------------------------------------------+----+---------------------------+----------+---------+ | id | username | passwd | id | groupname | group_id | user_id | +----+-------------+---------------------------------------------+----+---------------------------+----------+---------+ | 1 | admin | 5d7edTmEZWDz19dlpCnuF4eSXZx7ZD+2cVdi9w...== | 1 | confluence-administrators | 1 | 1 | | 3 | admin2 | AIemXIs3vSn1NnR/KMlnK+wcrndi0snza2AT8g...== | 1 | confluence-administrators | 1 | 3 | +----+-------------+---------------------------------------------+----+---------------------------+----------+---------+ 2 rows in set (0.01 sec)
The data in the password (or passwd) field is the hashed value of the user's password.
To force it to a known password, enter the hash
'x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A=='
for the user. You can then login with this user name and the password 'admin'. If it does not work, restart the Tomcat server (Confluence caches data and will not see manual changes to the database).
update
users
set
password='x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A=='
where
id=393217;
To update the passwd field in the os_user table in an old Confluence instance, use
update
os_user
set
passwd='x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A=='
where id=1;
Note
This is not a hack to break in an arbitrary Confluence Site. You must have access to this site's database.
It is also possible to edit the XML Backup File and insert the password hash string manually.
For the Evaluation Embedded HSQL Database
To change the password to admin for a given username:
Shut down Confluence.
Open <confluence-home>/database/confluencedb.script, or confluencedb.log if the .script file looks empty.
Search for:
INSERT INTO CWD_USER VALUES(
Keep searching until you find the appropriate user, then replace their password with this hash value:
x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A==
Save the file.
Restart Confluence.
FYI, Confluence Home folder is defined in <Confluence install folder>/confluence/WEB-INF/classes/confluence-init.properties file.
If you are using HSQL then an alternative way of accessing the database is to use HSQL's Database Manager (http://confluence.atlassian.com/display/JIRA/Running+SQL+commands+in+a+HSQL+database) - those instructions are for JIRA, but the steps are the same for Confluence:
1. Shutdown Confluence
2. Locate the HSQL JAR file (in my Confluence 4.1.4 installation it's <confluence installation>/WEB-INF/lib/hsqldb-2.2.4.jar
3. From a command line run java -cp ./hsqldb-2.2.4.jar org.hsqldb.util.DatabaseManager -user sa -url jdbc:hsqldb:<confluence home>/database/confluencedb;hsqldb.tx=MVCC
4. Execute the relevant SQL in the HSQL Database Manager GUI
5. Exit the Database Manager
6. Start Confluence
If none of the above works then you're probably looking at recovering from backup (if you have one) or uninstalling and re-installing from scratch. Depending on how much work you would lose if you had to re-install from scratch a re-install may be less effort than resetting the admin password?
Does that help?
2 Comments
Anonymous
Have You tested this?
Seems that neither
update cwd_user set credential='x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A==' where user_name='jiraadmin';
or
update cwd_user set credential='{PKCS5S2}x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A==' where user_name='jiraadmin';
is working on my 4.4