How to remove users without 'Can Use' permission from Confluence People Directory
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).
目的
Users are listed in the People Directory who do not have Confluence Use permission, and should not appear in the People Directory.
ソリューション
The following has been tested in PostgreSQL, and may need to be modified for other database types.
- Confluence を停止します。
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');
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'));
- Confluence を起動します。
- Reindex Confluence
- Check People Directory to confirm users are removed.
データベースの変更を行う場合は必ず事前にバックアップを取得してください。可能な場合は、まずステージング サーバーで SQL コマンドの変更、挿入、更新、または削除を行うようにします。