Restoring Passwords To Recover Admin User Rights via Database

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

プラットフォームについて: Server と Data Center のみ - この記事は、サーバーおよびデータセンター プラットフォームのアトラシアン製品にのみ適用されます。


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

Please note that this process works for all versions of Crowd, but there is an alternate method to gain access to Crowd if you've lost the admin password which applies to versions 3.5.2, 3.6.2, 3.7.1 and greater . Please see Restore Passwords To Recover Admin User Rights Using System Properties

作業を開始する前に

Please note the following:

  • 次の手順には、MySQL と PostgreSQL で動作する SQL の例が含まれています。他のデータベースまたは使用中のインストールのクエリをカスタマイズする必要が生じる可能性があります。
  • We strongly recommend testing the queries on a test database before modifying your production database.

このページの内容

Step 1. Get access to the database

If you are using the embedded HSQL database, you can either refer to this document here to connect to your HSQL database then append the SQL described below, or access the database scripts directly. You can find the files containing your database in <crowd-home-directory>/database. If you are using HSQL, please be aware that it is not supported, and you should migrate to a production database at your earliest convenience.

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.

ステップ 2.管理者の特定

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.directory_id from cwd_user u
 join cwd_membership m on u.id=m.child_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 = 'crowd-administrators' and d.directory_type='INTERNAL';

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 Users Exist' in Step 4.

ステップ 3.管理者パスワードの交換

Crowd 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 a table which list the hashes for the password 'admin' in all the encryption types supported by Crowd.

attribute_valuehash value for password 'admin'
atlassian-security 

{PKCS5S2}8WEZjkCbLWysbcbZ5PRgMbdJgJOhkzRT3y1jxOqke2z1Zr79q8ypugFQEYaMoIZt

sha{SHA}0DPiKuNIrrVmD8IUCuw1hQxNqZc=
ssha{SSHA}OfBWORKqf1RNxfmyY8mYwQn9tD1MbHp4TEhiYQ==
desu2a3KD0vo/A=
bcrypt$2a$10$cRKdFPUYlww5u1adaQlANu2Kthk4vct6JYDbJo18S98QzQMAcEwES
md5{MD5}ISMvKXpXpadDiUoOSoAfww==
plaintext管理

If you are not sure what encryption type is in use for the directory, run the following SQL command and be sure to replace XXXXXX with the value of 'directory' in step 1.    The most common is 'atlassian-security' and is the assumed encryption for the remainder of this article.  Should a different one be in use please be sure to adjust the SQL commands accordingly.

SELECT attribute_value FROM cwd_directory_attribute where attribute_name='user_encryption_method' and directory_id='XXXXXX'; 

For an External Database

To change the password to admin for a given username:

  1. Shut down Crowd.
  2. Connect to your database.
  3. 次の SQL を実行します。

    update cwd_user set credential = '{PKCS5S2}8WEZjkCbLWysbcbZ5PRgMbdJgJOhkzRT3y1jxOqke2z1Zr79q8ypugFQEYaMoIZt'
     where id=<id from Stage 1>;
    
    update cwd_user_attribute set attribute_value = 'false', attribute_lower_value = 'false'
     where attribute_name = 'requiresPasswordChange' and user_id=<id from Stage 1>;
     
    update cwd_user_attribute set attribute_value = '0', attribute_lower_value = '0'
     where attribute_name = 'invalidPasswordAttempts' and user_id=<id from Stage1>;

For the Evaluation Embedded HSQL Database

To change the password to admin for a given username:

  1. Shut down Crowd.
  2. Open <crowd-home>/database/defaultdb.script, or defaultdb.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. Search for:

    INSERT INTO CWD_USER_ATTRIBUTE VALUES(
  6. Keep searching until you find the appropriate user, and the "requiresPasswordChange" attribute, and ensure that the values in both "attribute_value" and "attribute_lower_value" columns are "false"
  7. Keep searching until you find the appropriate user, and the "invalidPasswordAttempts" attribute, and ensure that the values in both "attribute_value" and "attribute_lower_value" columns are 0
  8. ファイルを保存する。
  9. Restart Crowd.

Step 4. If No Local Users Exist

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

    1. Add a new admin user (with password "admin") 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_type='INTERNAL'), '{PKCS5S2}8WEZjkCbLWysbcbZ5PRgMbdJgJOhkzRT3y1jxOqke2z1Zr79q8ypugFQEYaMoIZt');
      
    2. 次を実行して、新しいグループを追加します。

      insert into cwd_group(id, group_name, lower_group_name, active, is_local, created_date, updated_date, description, group_type, directory_id)
       values ( '888888','crowd-administrators','crowd-administrators','T','F','2011-03-21 12:20:29','2011-03-21 12:20:29',NULL,'GROUP',(select id from cwd_directory where directory_type='INTERNAL'));
      
    3. cwd_membership にグループ メンバーシップを追加します。

      insert into cwd_membership (id, parent_id, child_id, membership_type, group_type, parent_name, lower_parent_name, child_name, lower_child_name, directory_id) 
       values (777777, (select id from cwd_group where group_name='crowd-administrators' and directory_id=(select id from cwd_directory where directory_type='INTERNAL')), 1212121, 'GROUP_USER', 'GROUP', 'crowd-administrators', 'crowd-administrators', 'admin', 'admin', (select id from cwd_directory where directory_type='INTERNAL'));
      
    4. Ensure the internal directory is active

      update cwd_directory set active = 'T' where id = (select id from cwd_directory where directory_type='INTERNAL');
      tip/resting Created with Sketch.

      Use this one if you're running Crowd in MySQL database:

      MySQL
       update cwd_directory set active = 'T' where id = (select id from (select id from cwd_directory where directory_type='INTERNAL') as temp);
    5. Find the directory-to-application mapping

       select m.id, m.application_id, m.directory_id from cwd_app_dir_mapping m join cwd_directory d
        on m.directory_id = d.id join cwd_application a
        on m.application_id = a.id
         where d.directory_type='INTERNAL' and a.application_type = 'CROWD';

      If there is no directory-to-application mapping, you will need to create one. Otherwise proceed to Step f below.

      insert into cwd_app_dir_mapping (id, application_id, directory_id, allow_all, list_index)
       values (787878, (select id from cwd_application where application_type='CROWD'), (select id from cwd_directory where directory_type='INTERNAL'), 'F', 0);
    6. Give the groups access to the application

      insert into cwd_app_dir_group_mapping (id, app_dir_mapping_id, application_id, directory_id, group_name)
       values (898990, <id from number 5>, <application_id from number 5>, <directory_id from number 5>, 'crowd-administrators');
    7. For Crowd 2.8+, make sure the System Administrator flag is enabled for 'crowd-administrators'

      update cwd_granted_perm set permission_id = 2 where group_mapping = 898990;

      If there is no related date, you will need to create one.

      insert into cwd_granted_perm (id, created_date, permission_id, app_dir_mapping_id, group_name)
       values (999999, '<current time (i.e. 2015-09-25 12:08:00)', 2, <id from step "e" >, <group name>);
tip/resting Created with Sketch.

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

ステップ 5.クリーンアップ

整理するには、

  1. Start Crowd.
  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 Step 4, create a new admin via the UI and delete the admin you created in Step 4.

注意


Last modified on Mar 27, 2020

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.