Platform notice: Server and Data Center only. This article only applies to Atlassian products on the server and data center platforms.

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