Reverting from Crowd or Jira applications to Internal User Management

Connecting to Crowd or Jira for User Management

On this page

Still need help?

The Atlassian Community is here for you.

Ask the community

If your Confluence site currently uses Crowd or a Jira application for user management, you can revert to internal user management as described below. If your Confluence instance has only a few users, it is easier to recreate the users and groups in Confluence manually. If you have a large number of users and groups, it is more efficient to migrate the relevant users and groups into the Confluence Internal directory.

Both options provided below will reset the affected users' passwords. When done, be sure to notify them to use the 'Reset My Password' link on the Confluence log in page before they attempt to log in.

On this page:

Option 1 – Manually Recreate Users and Groups in Confluence

Use this option if you have only a few users and groups.

  1. Log in to Confluence as a Confluence system administrator.
  2. Go to the user directories administration screen and move the internal directory to the top of the list of directories, by clicking the arrows in the 'Order' column.
  3. Make sure that you have at least one user from the internal directory in each of the confluence-users and confluence-administrators groups.
  4. Make sure that you have a username in the internal directory with Confluence system administrator permissions. 
    • If you do not have such a user, add a new one now, and log out of Confluence.
    • Log back in as the user you just added, and go back to the user directories administration screen.
  5. Disable the 'Atlassian Crowd' directory.
  6. Manually add the required users and groups in Confluence. They will be added to the internal directory, because you have moved it to the top of the list of directories.
    • If you have assigned Confluence permissions to a group which exists in your Jira application, you must create a group in Confluence with the same name.
    • If a user who exists in your Jira application has created content or has had permissions assigned to them in Confluence, you must also create that user in Confluence.
  7. Add the users to the required groups.

Option 2 – Transfer Crowd/Jira application Users and Groups to the Confluence Database

This method is not officially supported. The Atlassian Support team won't be able to assist you with this process.

We strongly recommend trying this in a test environment, and then making a full backup of your database before deciding to deploy the change in your production environment.

Use this option to migrate External Application (Crowd or Jira applications) users into the Confluence database. You need a knowledge of SQL to perform this task.

The SQL commands given below are tailored for MySQL. If you are using a database other than MySQL, you will need to modify the SQL to work in your database.

Step 1. Create Backups

Creating backups is the only way to restore your data if something goes wrong.

  1. From Confluence, create a full XML site backup including attachments.
  2. Stop Confluence.
  3. Make a backup copy of the Confluence home and installation directories.
  4. Repeat the above steps for your External Application.
  5. From your MySQL administration tool, create a database backup for the Crowd/Jira application and Confluence databases.

Step 2. Replace Confluence User Management

Use the SQL below to move groups and users from your External Application to Confluence by transferring table content. The SQL provided is specific to MySQL and must be modifed for other databases.

Find the IDs for your Directories

  1. Run the following command and take note of the resulting number. It will be referenced throughout the following instructions as <Confluence Internal ID>.

    select id from cwd_directory where directory_name='Confluence Internal Directory';
    
  2. From the User Directories administration page, find the name of the directory who's users/groups you want to move. Run the following command and take note of the resulting number. It will be referenced throughout the following instructions as <External Application ID>.

    select id from cwd_directory where directory_name='<External Directory Name>';
    

Find and remove duplicate users who belong to the same group in multiple directories

To make sure you don't introduce duplicates in the next step, when you move groups to Confluence, use the following SQL query to locate any users that belong to a group with the same name in both your external directory and internal Confluence directory. 

  1. Run the following command to find any users with the same name, that belong to the same group across different directories:

    SELECT count(*), a.user_name, c.group_name from cwd_user a
    join cwd_membership b on b.child_user_id = a.id
    join cwd_group c on c.id = b.parent_id group by 2,3 having count(*)>1

    Make a note of each of the usernames and groups returned. You'll need this in the next step. 

  2. In your external directory, remove the users from their respective groups. Their membership will still be retained in the Confluence internal directory. 
  3. Run the SQL query above again. Once it returns no results, you can move to the next step. 

Move Groups to Confluence

  1. It is possible that you have several groups in your Internal Directory that have the same name as groups in your External Application. To find these, run:

    select distinct a.id, a.directory_id, a.group_name, d.directory_name from cwd_group a join cwd_group b on a.group_name=b.group_name join cwd_directory d on d.id=a.directory_id where a.directory_id != b.directory_id;
    


    1. If you have results from the previous query, for each of the group names that have duplicates, find the id for the group in the Confluence Internal Directory (<internal group id>) and the External Application (<external group id>). Run the following:

      update cwd_group_attribute set group_id=<internal group id>, directory_id=<Confluence Internal Id> where group_id=<external group id>;
      update cwd_membership set child_group_id=<internal group id> where child_group_id=<external group id>;
      update cwd_membership set parent_id=<internal group id> where parent_id=<external group id>;
      delete from cwd_group where id=<external group id>;
      
  2. Move all the groups in the External Application to the Confluence Internal Directory.

    update cwd_group set directory_id=<Confluence Internal ID> where directory_id=<External Application ID>;
    

Move Users to Confluence

  1. It is possible that you have several users in your Internal Directory that have the same name as users in your External Application. To find these, run:

    select distinct a.id, a.directory_id, a.user_name, d.directory_name from cwd_user a join cwd_user b on a.user_name=b.user_name join cwd_directory d on d.id=a.directory_id where a.directory_id != b.directory_id;
    


    1. If you have results from the previous query, for each of the user names that have duplicates, find the id for the user in the Confluence Internal Directory (<internal user id>) and the External Application (<external user id>). Run the following:

      update cwd_membership set child_user_id=<internal user id> where child_user_id=<external user id>;
      update cwd_user_credential_record set user_id=<internal user id> where user_id=<external user id>;
      update cwd_user_attribute set user_id=<internal user id>, directory_id=<Confluence Internal ID> where user_id=<external user id>;
      delete from cwd_user where id=<external user id>;
      
  2. Move all the users in the External Application to the Confluence Internal Directory.

    update cwd_user set directory_id=<Confluence Internal ID> where directory_id=<External Application ID>;
    

Delete the External Application directory

  1. You need to change the order of your directories so that the Internal directory is at the top, and active.
    1. If you have only two directories - the Internal and the External Application directory you are deleting, then do the following:

      update cwd_app_dir_mapping set list_index = 0 where directory_id = <Confluence Internal ID>;
      
    2. If you have more than two directories, you need to rearrange them so the Internal Directory is at the top (list_index 0) and the External Application directory you are deleting is at the bottom.
      • List the directories and their order using

        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 order by m.list_index;
        
      • Change the list indexes so that they are in the order you want. Directory order can be rearranged using

        update cwd_app_dir_mapping set list_index = <position> where directory_id = <directory id>;
        
    3. Check that the internal directory is enabled.
      • List the internal directory. An enabled directory will have its 'active' column set to 'T'

        select id, directory_name, active from cwd_directory where id = <Internal Directory id>;
        
      • If the internal directory is not active, activate it by

        update cwd_directory set active = 'T' where id = <Internal Directory id>;
        
  2. When the directories are ordered correctly, delete the External Application directory from the directory order:

    delete from cwd_app_dir_operation where app_dir_mapping_id = (select id from cwd_app_dir_mapping where directory_id = <External Application ID>);
    delete from cwd_app_dir_mapping where directory_id = <External Application ID>;
    
  3. The External Application directory is referenced in several other tables in the database. You need to remove the remaining references to it:

    delete from cwd_directory_attribute where directory_id=<External Application ID>;
    delete from cwd_directory_operation where directory_id=<External Application ID>;
    
  4. All references to the External Directory should now have been removed. Delete the directory using:

    delete from cwd_directory where id = <External Application ID>;
    

Reset passwords

All users who were in the External Directory you deleted, including admins, will be unable to log in. Their passwords need to be reset by choosing the 'Forgot your password?' link on the login page. Alternatively, use the instructions at Restore Passwords To Recover Admin User Rights to reset the administrator password, then set the users' passwords for them via the Manage Users page in the administration screen.

Last modified on Apr 13, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.