How to List Spaces In Which a User Has Administrator Privileges

Still need help?

The Atlassian Community is here for you.

Ask the community

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:

    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;
    

(info) 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.

Last modified on Feb 26, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.