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.

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_id' in step 2.    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 step 2>;
   
   update cwd_user_attribute set attribute_value = 'false', attribute_lower_value = 'false'
    where attribute_name = 'requiresPasswordChange' and user_id=<id from step 2>;
    
   update cwd_user_attribute set attribute_value = '0', attribute_lower_value = '0'
    where attribute_name = 'invalidPasswordAttempts' and user_id=<id from step 2>;

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

      • SQL
      • MySQL

      update cwd_directory set active = 'T' where id = (select id from cwd_directory where directory_type='INTERNAL');

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

ステップ 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.

注意

• Learn more about the password hash algorithm Crowd is using.