ユースケース

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.

ソリューション

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:
  

  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:

  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.