How to remove users without 'Can Use' permission from Confluence People Directory

Still need help?

The Atlassian Community is here for you.

Ask the community

This article applies to Confluence 5.8 and earlier.

In Confluence 5.9 we introduced integration with Jira Service Desk that meant that you could allow all active users and customers to view knowledge base articles in Confluence without granting them a Confluence license or enabling anonymous access to your site. For this reason, any user who does not have the 'Use Confluence' global permission does not appear in the people directory (or are able to be mentioned on a page).

Purpose

Users are listed in the People Directory who do not have Confluence Use permission, and should not appear in the People Directory.

Solution

The following has been tested in PostgreSQL, and may need to be modified for other database types.

  1. Stop Confluence
  2. Run test query to see which results will be deleted:

    SELECT DISTINCT lower_username FROM user_mapping WHERE lower_username not in (
    SELECT DISTINCT u.lower_user_name
    FROM cwd_user u
    JOIN cwd_membership m ON u.id = child_user_id
    JOIN cwd_group g ON m.parent_id = g.id
    JOIN SPACEPERMISSIONS sp ON g.group_name = sp.PERMGROUPNAME
    JOIN cwd_directory d on u.directory_id = d.id
    WHERE PERMTYPE='USECONFLUENCE' AND u.active = 'T' AND d.active = 'T');
  3. Run query to delete users from People Directory:

    DELETE
    FROM CONTENT
    WHERE contenttype = 'USERINFO' AND username IN 
    (SELECT DISTINCT user_key FROM user_mapping WHERE lower_username not in (
    SELECT DISTINCT u.lower_user_name
    FROM cwd_user u
    JOIN cwd_membership m ON u.id = child_user_id
    JOIN cwd_group g ON m.parent_id = g.id
    JOIN SPACEPERMISSIONS sp ON g.group_name = sp.PERMGROUPNAME
    JOIN cwd_directory d on u.directory_id = d.id
    WHERE PERMTYPE='USECONFLUENCE' AND u.active = 'T' AND d.active = 'T'));
  4. Start Confluence
  5. Reindex Confluence
  6. Check People Directory to confirm users are removed.

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

Last modified on Oct 23, 2017

Was this helpful?

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