Use Case

For auditing or administration purposes, a Confluence administrator may want to see the users that have space administrator privileges. This can be done via a SQL query.

Resolution

Run the following SQL queries against the Confluence database:

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

  SELECT cu.user_name, cu.email_address, s.spacename
  FROM cwd_user cu
  JOIN spacepermissions sp ON cu.user_name = sp.permusername
  JOIN spaces s ON sp.spaceid = s.spaceid
  WHERE sp.permtype = 'SETSPACEPERMISSIONS';

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

  SELECT cu.user_name, cu.email_address, s.spacename 
  FROM cwd_user cu 
  JOIN user_mapping um ON cu.user_name = um.username 
  JOIN spacepermissions sp ON um.user_key = sp.permusername 
  JOIN spaces s ON sp.spaceid = s.spaceid 
  WHERE sp.permtype = 'SETSPACEPERMISSIONS';

If you would like to get a list of Users that have space admin access due to group membership you can run the following:

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

  SELECT sp.permgroupname, cu.user_name, cu.email_address, s.spacename
  FROM spacepermissions sp
  JOIN cwd_group cg ON cg.group_name = sp.permgroupname
  JOIN cwd_membership cm ON cg.id = cm.parent_id
  JOIN spaces s ON sp.spaceid = s.spaceid
  JOIN cwd_user cu ON cm.child_user_id = cu.id
  WHERE permtype = 'SETSPACEPERMISSIONS' AND permgroupname IS NOT NULL;

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

  SELECT sp.permgroupname, cu.user_name, cu.email_address, s.spacename
  FROM spacepermissions sp
  JOIN cwd_group cg ON cg.group_name = sp.permgroupname
  JOIN cwd_membership cm ON cg.id = cm.parent_id
  JOIN spaces s ON sp.spaceid = s.spaceid
  JOIN cwd_user cu ON cm.child_user_id = cu.id
  JOIN user_mapping um ON cu.user_name = um.username
  WHERE permtype = 'SETSPACEPERMISSIONS' AND permgroupname IS NOT NULL;

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.