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;

    
    

  • The following will list all groups, along with spaces they have access to:

    SELECT DISTINCT sp.permgroupname, s.spacename
    FROM SPACEPERMISSIONS sp
    JOIN SPACES s ON sp.spaceid = s.spaceid
    LEFT JOIN user_mapping um ON sp.permusername = um.user_key
    WHERE sp.permgroupname IS NOT NULL order by sp.permgroupname;

  • For oracle to search for all groups with a like name and output as a list with key, name and group:

    SELECT s.spacekey,s.SPACENAME,sp.PERMGROUPNAME
    FROM spacepermissions sp
    JOIN spaces s ON s.spaceid = sp.spaceid
    JOIN cwd_group g ON sp.permgroupname = g.group_name
    WHERE g.lower_group_name like 'department%'
    ORDER BY s.spacekey,s.SPACENAME,g.lower_group_name;

Please note that these results will also not accurately reflect any groups in nested groups.