How to list which spaces a group can access
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, an administrator may want to see which spaces a given group can access (i.e. at least have view permissions). This can be done via a few SQL queries.
Resolution
Run the following SQL queries against the Confluence database, replacing <group_name> with the group name, in lowercase.
The following will list all the spaces that contain permissions:
1 2 3 4 5 6 7 8 9 10 11 12SELECT s.SPACEKEY FROM SPACEPERMISSIONS sp JOIN SPACES s ON s.SPACEID = sp.SPACEID JOIN cwd_group g ON sp.PERMGROUPNAME = g.group_name WHERE sp.PERMGROUPNAME IN ( SELECT g.group_name FROM cwd_group g WHERE g.lower_group_name = '<group_name>' ) GROUP BY s.SPACEKEY ORDER BY s.SPACEKEY;The following will list all the spaces that the group has been granted permissions with the permission type:
1 2 3 4 5 6 7 8 9 10SELECT s.SPACEKEY, sp.PERMTYPE as PERMISSIONS FROM SPACEPERMISSIONS sp JOIN SPACES s ON s.SPACEID = sp.SPACEID JOIN cwd_group g ON sp.PERMGROUPNAME = g.group_name WHERE sp.PERMGROUPNAME IN ( SELECT g.group_name FROM cwd_group g WHERE g.lower_group_name = '<group_name>' );The following will list all spaces, along with all users that have access to each space:
1 2 3 4 5 6 7 8 9 10 11 12SELECT s.spacename as Space, u.user_name as Username FROM spacepermissions sp JOIN spaces s ON s.spaceid = sp.spaceid JOIN cwd_group g ON sp.permgroupname = g.group_name JOIN cwd_membership m ON g.id = m.parent_id JOIN cwd_user u ON u.id = m.child_user_id WHERE sp.permgroupname IN ( SELECT g.group_name FROM cwd_group g ) ORDER BY s.spacekey;The following will list all groups, along with spaces they have access to:
1 2 3 4 5SELECT DISTINCT sp.permgroupname, s.spacename FROM SPACEPERMISSIONS sp JOIN SPACES s ON sp.spaceid = s.spaceid LEFT JOIN user_mapping um ON sp.permusername = um.user_key WHERE sp.permgroupname IS NOT NULL order by sp.permgroupname;For oracle to search for all groups with a like name and output as a list with key, name and group:
1 2 3 4 5 6SELECT s.spacekey,s.SPACENAME,sp.PERMGROUPNAME FROM spacepermissions sp JOIN spaces s ON s.spaceid = sp.spaceid JOIN cwd_group g ON sp.permgroupname = g.group_name WHERE g.lower_group_name like 'department%' ORDER BY s.spacekey,s.SPACENAME,g.lower_group_name;
ℹ️ Please note that these results will also not accurately reflect any groups in nested groups.
Was this helpful?