How to List Users That Have Space 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 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;

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