How to list which spaces a user can access

Summary

For auditing or administration purposes, an administrator may want to see which spaces a given user can access (i.e. at least have view permissions). This can be done via a few SQL queries.

Solution

Run the following SQL queries against the Confluence database, replacing <user_name> with the actual username of the user, in lowercase.

• The following will list all the spaces that contain permissions based on the groups that the user belongs in:

  1 2 3 4 5 6 7 8 9 SELECT s.SPACEKEY 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 m.child_user_id = u.id WHERE u.lower_user_name = '<user_name>' GROUP BY s.SPACEKEY ORDER BY s.SPACEKEY;

• The following will list all the spaces that the user has been individually granted permissions:

  1 2 3 4 5 6 7 SELECT s.SPACEKEY FROM SPACEPERMISSIONS sp  JOIN SPACES s ON s.SPACEID = sp.SPACEID JOIN user_mapping um ON um.user_key = sp.PERMUSERNAME WHERE um.lower_username = '<user_name>' GROUP BY s.SPACEKEY ORDER BY s.SPACEKEY;

• You can view the restrictions on pages assigned to the user with the following (this will show pages that grants viewing/editing to this username but limits it otherwise).

  1 2 3 4 5 6 7 8 9 10 SELECT p.cp_type,u.username,c.title FROM content_perm p JOIN user_mapping u ON p.username = u.user_key JOIN content_perm_set s ON p.cps_id = s.id JOIN content c ON s.content_id = c.contentid WHERE c.contenttype = 'PAGE' AND u.username = '<user_name>'

• You can view the restrictions on a page assigned to a group with the following (This will show pages that grants viewing.editing permissions for a specific page to a group)

  1 2 3 4 5 6 7 8 9 10 SELECT p.cp_type,u.group_name,c.title FROM content_perm p JOIN cwd_group u ON p.groupname = u.group_name JOIN content_perm_set s ON p.cps_id = s.id JOIN content c ON s.content_id = c.contentid WHERE c.contenttype = 'PAGE' AND c.contentid = '<content-id>';

ℹ️ Please note that these results will not accurately 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.