How to List Spaces In Which a User Has Administrator Privileges

Use Case

For auditing or administration purposes, a Confluence administrator may want to see the spaces on which a given user has administrator privileges. This can be done via a SQL query.

Resolution

Run the following SQL queries against the Confluence database, replacing <user_name> with the actual username of the user.

• The following will list all the spaces that contain admin permissions for the user, and will work for Confluence 5.1.X and below:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 SELECT s.spacekey, s.spacename, sp.permgroupname, sp.permusername FROM spacepermissions sp LEFT JOIN spaces s ON s.spaceid = sp.spaceid LEFT JOIN cwd_group g ON sp.permgroupname = g.group_name LEFT JOIN cwd_membership m ON g.id = m.parent_id LEFT JOIN cwd_user u ON m.child_user_id = u.id WHERE ( sp.permgroupname IN ( SELECT g.group_name FROM cwd_group g JOIN cwd_membership m ON g.id = m.parent_id JOIN cwd_user u ON m.child_user_id = u.id WHERE u.user_name = '<username>' ) OR (sp.permusername = '<username>') ) AND sp.permtype = 'SETSPACEPERMISSIONS' GROUP BY s.spacekey, s.spacename, sp.permgroupname, sp.permusername ORDER BY s.spacekey;

• The following will list all the spaces that contain admin permissions for the user, and will work for Confluence 5.2.X and above:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 SELECT s.spacekey, s.spacename, sp.permgroupname, um.lower_username FROM spacepermissions sp LEFT JOIN spaces s ON s.spaceid = sp.spaceid LEFT JOIN cwd_group g ON sp.permgroupname = g.group_name LEFT JOIN cwd_membership m ON g.id = m.parent_id LEFT JOIN cwd_user u ON m.child_user_id = u.id LEFT JOIN user_mapping um ON um.user_key = sp.permusername WHERE ( sp.permgroupname IN ( SELECT g.group_name FROM cwd_group g JOIN cwd_membership m ON g.id = m.parent_id JOIN cwd_user u ON m.child_user_id = u.id WHERE u.user_name = '<username>') OR (sp.permusername = (SELECT user_key FROM user_mapping WHERE lower_username= '<username>')) ) AND sp.permtype = 'SETSPACEPERMISSIONS' GROUP BY s.spacekey, s.spacename, sp.permgroupname, um.lower_username ORDER BY s.spacekey;

ℹ️ Please note that these results will not accurate 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.