How to delete all internal Confluence users except the local administrator

Still need help?

The Atlassian Community is here for you.

Ask the community

Purpose

This is a very specific use case that applies to administrators running Confluence version 3.5.x or higher.

Confluence was originally set up with local user management but then switched to an external user management system (JIRA, Crowd, or LDAP), keeping the exact same usernames. Normally, the external user directory would be set to the top position, thereby allowing the external users to  supersede  the internal users of the same usernames (the local users will be "shadowed").  However, the administrator would like to continue to use the internal directory to create users/groups local to Confluence.

The issue is that Confluence will not allow the creation of a local user unless the local user directory is at the top position. However, if the local directory is at the top position, then the external users will be "shadowed".  There is an improvement request to allow the administrator to add users to specific user directories. If this is implemented, then the external user directory can always remain at the top position, and users can be added to the local directory on command.  Please keep watch on the following ticket if you are interested in receiving updates on this feature:  CONF-22296 - Getting issue details... STATUS

Workaround

A workaround is to swap the user directory order so that the local directory is on top every time a local user needs to be added to Confluence.

  Solution

Another solution is to permanently keep the local directory at the top and remove all internal users (except a local administrator account in case of emergencies).

Confluence will not allow the removal of users if the username is associated with any content in the application (even if the same username exists in other directories). Therefore, the removal will have to be done via the database.

This operation is not supported and should be thoroughly tested in a test environment to avoid unexpected results. In other words, please proceed at your own risk. As usual, please ensure that you have ample database backups prior to running any SQL modifications.

This operation was tested in Confluence 8.5. This is not guaranteed to work in future versions as the product may change.

(warning) The below queries will delete ALL local users, except one local admin (determined by whatever <local_admin_username> is set to):

  • Postgres/MySQL:

    CREATE TEMPORARY TABLE tmp_userids
    AS (SELECT u.id FROM cwd_user u
    JOIN cwd_directory d ON u.directory_id = d.id
    WHERE d.lower_directory_name = 'confluence internal directory' AND u.user_name <> '<local_admin_username>');

     (info) you can index the tmp_userids by running the following query after their creation:

    CREATE INDEX tmp_userids_index
    ON tmp_userids (id);
      
    DELETE FROM cwd_user_credential_record WHERE user_id IN (SELECT id FROM tmp_userids);
    DELETE FROM cwd_membership WHERE child_user_id IN (SELECT id FROM tmp_userids);
    DELETE FROM cwd_user_attribute WHERE user_id IN (SELECT id FROM tmp_userids);
    DELETE FROM cwd_user WHERE id IN (SELECT id FROM tmp_userids);
  • Microsoft SQL Server:

    CREATE TABLE #tmp_userids(id numeric)
    INSERT INTO #tmp_userids (id) (SELECT u.id FROM cwd_user u
    JOIN cwd_directory d ON u.directory_id = d.id
    WHERE d.lower_directory_name = 'confluence internal directory' AND u.user_name != '<local_admin_username>');

    (info) you can index the tmp_userids by running the following query after their creation:

    CREATE INDEX tmp_userids_index
    ON tmp_userids (id);
      
    DELETE FROM cwd_user_credential_record WHERE user_id IN (SELECT id FROM #tmp_userids);
    DELETE FROM cwd_membership WHERE child_user_id IN (SELECT id FROM #tmp_userids);
    DELETE FROM cwd_user_attribute WHERE user_id IN (SELECT id FROM #tmp_userids);
    DELETE FROM cwd_user WHERE id IN (SELECT id FROM #tmp_userids);
Last modified on Jun 11, 2024

Was this helpful?

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