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 s ame 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 into 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 keep the local directory permanently at the top position and remove all the 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.

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

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

CREATE INDEX tmp_userids_index
ON tmp_userids (id);

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

  
  

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

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