How to list which spaces a group can access

Still need help?

The Atlassian Community is here for you.

Ask the community

Use Case

For auditing or administration purposes, an administrator may want to see which spaces a given group can access (i.e. at least have view permissions). This can be done via a few SQL queries.

Resolution

Run the following SQL queries against the Confluence database, replacing <group_name> with the group name, in lowercase.

    • The following will list all the spaces that contain permissions:

      SELECT s.SPACEKEY
      FROM SPACEPERMISSIONS sp
      JOIN SPACES s ON s.SPACEID = sp.SPACEID
      JOIN cwd_group g ON sp.PERMGROUPNAME = g.group_name
      WHERE sp.PERMGROUPNAME IN
      (
      SELECT g.group_name
      FROM cwd_group g
      WHERE g.lower_group_name = '<group_name>'
      )
      GROUP BY s.SPACEKEY
      ORDER BY s.SPACEKEY;
    • The following will list all the spaces that the group has been granted permissions with the permission type:

      SELECT s.SPACEKEY, sp.PERMTYPE as PERMISSIONS
      FROM SPACEPERMISSIONS sp
      JOIN SPACES s ON s.SPACEID = sp.SPACEID
      JOIN cwd_group g ON sp.PERMGROUPNAME = g.group_name
      WHERE sp.PERMGROUPNAME IN
      (
      SELECT g.group_name
      FROM cwd_group g
      WHERE g.lower_group_name = '<group_name>'
      );
    • The following will list all spaces, along with all users that have access to each space:

      SELECT s.spacename as Space, u.user_name as Username
      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 u.id = m.child_user_id
      WHERE sp.permgroupname IN
      (
      SELECT g.group_name
      FROM cwd_group g
      ) 
      ORDER BY s.spacekey;





Last modified on Nov 2, 2018

Was this helpful?

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