How to List Users That Have Space Administrator Privileges

ユースケース

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.

ソリューション

Confluence データベースに対して次のクエリを実行します。

• 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.