How to List Users That Have Space Administrator Privileges
Platform Notice: Data Center Only - This article only applies to Atlassian products 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 users that have space administrator privileges. This can be done via a SQL query.
Resolution
Run the following SQL queries against the Confluence database:
The following will list all the spaces that contain admin permissions for the user, and will work for Confluence 5.1.X and below:
1 2 3 4 5SELECT cu.user_name, cu.email_address, s.spacename FROM cwd_user cu JOIN spacepermissions sp ON cu.user_name = sp.permusername JOIN spaces s ON sp.spaceid = s.spaceid WHERE sp.permtype = 'SETSPACEPERMISSIONS';The following will list all the spaces that contain admin permissions for the user, and will work for Confluence 5.2.X and above:
1 2 3 4 5 6SELECT cu.user_name, cu.email_address, s.spacename FROM cwd_user cu JOIN user_mapping um ON cu.user_name = um.username JOIN spacepermissions sp ON um.user_key = sp.permusername JOIN spaces s ON sp.spaceid = s.spaceid WHERE sp.permtype = 'SETSPACEPERMISSIONS';
If you would like to get a list of Users that have space admin access due to group membership you can run the following:
The following will list all the spaces that contain admin permissions for the user due to group membership, and will work for Confluence 5.1.X and below:
1 2 3 4 5 6 7SELECT sp.permgroupname, cu.user_name, cu.email_address, s.spacename FROM spacepermissions sp JOIN cwd_group cg ON cg.group_name = sp.permgroupname JOIN cwd_membership cm ON cg.id = cm.parent_id JOIN spaces s ON sp.spaceid = s.spaceid JOIN cwd_user cu ON cm.child_user_id = cu.id WHERE permtype = 'SETSPACEPERMISSIONS' AND permgroupname IS NOT NULL;The following will list all the spaces that contain admin permissions for the user due to group membership, and will work for Confluence 5.2.X and above:
1 2 3 4 5 6 7 8SELECT sp.permgroupname, cu.user_name, cu.email_address, s.spacename FROM spacepermissions sp JOIN cwd_group cg ON cg.group_name = sp.permgroupname JOIN cwd_membership cm ON cg.id = cm.parent_id JOIN spaces s ON sp.spaceid = s.spaceid JOIN cwd_user cu ON cm.child_user_id = cu.id JOIN user_mapping um ON cu.user_name = um.username WHERE permtype = 'SETSPACEPERMISSIONS' AND permgroupname IS NOT NULL;
ℹ️ 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?