How to list which spaces a user can access
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.
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:
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:
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).
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>'
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.