How to List Spaces In Which a User Has Administrator Privileges
Platform Notice: Data Center Only - This article only applies to Atlassian apps on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Use Case
For auditing or administration purposes, a Confluence administrator may want to see the spaces on which a given user has administrator privileges. This can be done via a SQL query.
Resolution
Run the following SQL queries against the Confluence database, replacing <user_name> with the actual username of the user.
The following will list all the spaces that contain admin permissions for the user, and will work for Confluence 5.1.X and below:
SELECT s.spacekey, s.spacename, sp.permgroupname, sp.permusername FROM spacepermissions sp LEFT JOIN spaces s ON s.spaceid = sp.spaceid LEFT JOIN cwd_group g ON sp.permgroupname = g.group_name LEFT JOIN cwd_membership m ON g.id = m.parent_id LEFT JOIN cwd_user u ON m.child_user_id = u.id WHERE ( sp.permgroupname IN ( SELECT g.group_name FROM cwd_group g JOIN cwd_membership m ON g.id = m.parent_id JOIN cwd_user u ON m.child_user_id = u.id WHERE u.user_name = '<username>' ) OR (sp.permusername = '<username>') ) AND sp.permtype = 'SETSPACEPERMISSIONS' GROUP BY s.spacekey, s.spacename, sp.permgroupname, sp.permusername ORDER BY s.spacekey;The following will list all the spaces that contain admin permissions for the user, and will work for Confluence 5.2.X and above:
SELECT s.spacekey, s.spacename, sp.permgroupname, um.lower_username FROM spacepermissions sp LEFT JOIN spaces s ON s.spaceid = sp.spaceid LEFT JOIN cwd_group g ON sp.permgroupname = g.group_name LEFT JOIN cwd_membership m ON g.id = m.parent_id LEFT JOIN cwd_user u ON m.child_user_id = u.id LEFT JOIN user_mapping um ON um.user_key = sp.permusername WHERE ( sp.permgroupname IN ( SELECT g.group_name FROM cwd_group g JOIN cwd_membership m ON g.id = m.parent_id JOIN cwd_user u ON m.child_user_id = u.id WHERE u.user_name = '<username>') OR (sp.permusername = (SELECT user_key FROM user_mapping WHERE lower_username= '<username>')) ) AND sp.permtype = 'SETSPACEPERMISSIONS' GROUP BY s.spacekey, s.spacename, sp.permgroupname, um.lower_username ORDER BY s.spacekey;
ℹ️ Please note that these results will not accurate reflect users in the confluence-administrators group. This group is hardcoded to be a super-user group and will be able to access every space regardless of space-level permissions. These results will also not accurately reflect any users in nested groups.
Was this helpful?