How to list which spaces a user can access

Still need help?

The Atlassian Community is here for you.

Ask the community

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


(info) 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.

Last modified on Nov 2, 2018

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.