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.

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

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

      The cwd_membership table contains the details of the groups the user is part of, so before deleting the user we should delete the related record in table cwd_membership and cwd_user_attribute table. If we do not delete entries in this order, we might see this error similar to this: 

  Error : The DELETE statement conflicted with the REFERENCE constraint "fk_child_user". The conflict occurred in database "prd-osconfluence-sqldb", table "dbo.cwd_membership", column 'child_user_id'.

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

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