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

• 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)

  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.