# How to list which spaces a user can access

#### Still need help?

The Atlassian Community is here for you.

## Purpose

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:

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
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 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.