How to view a list of all space creators and administrators for all spaces

Still need help?

The Atlassian Community is here for you.

Ask the community

The information in this page relates to customizations or development changes in Confluence. Consequently, Atlassian Support cannot guarantee to provide any support for the steps described on this page. Please be aware that this material is provided for your information only and that you use it at your own risk.

Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

Summary

The purpose of this document is to get a list of all space creators and administrators for all spaces.

Environment

  • Confluence Server or Data Center 3.5+

Solution

This can be done either by running SQL queries directly against the database or by creating a User Macro via General Configuration > User Macros.

SQL Queries (Confluence 5.2.x and above)

Here is a list of useful SQL queries. The letter case may differ from database to database, so it's worth checking that when running the queries.

List of all spaces and their creators:

SQL Syntax
SELECT s.spacename,
       s.spacekey,
       s.creator,
       u.*
FROM spaces AS s
JOIN user_mapping u ON s.creator = u.user_key
ORDER BY s.spacekey;
PostGreSQL Syntax
SELECT s.spacename,
       s.spacekey,
       s.creator,
       u.*
FROM spaces AS s
JOIN user_mapping u ON s.creator = u.user_key
ORDER BY s.spacekey;
Microsoft SQL Server syntax
SELECT s.SPACENAME,
       s.SPACEKEY,
       s.CREATOR,
       u.*
FROM SPACES s
JOIN user_mapping u ON s.CREATOR = u.user_key
ORDER BY s.SPACEKEY;
Oracle syntax
SELECT s.spacename,
       s.spacekey,
       s.creator,
       u.*
FROM spaces s
JOIN user_mapping u ON s.creator = u.user_key
ORDER BY s.spacekey;

List all users that have Space Admin permissions either as Individual Users or as members of groups with that Space Permission:

SQL Syntax
SELECT DISTINCT s.spaceid,
       s.spacekey,
       s.spacename,
       u.lower_username AS individual_lower_username,
       cu.lower_user_name AS group_member_lower_username,
       sp.permgroupname AS groupname,
       cg.local AS local_group,
       cd.directory_name AS group_directory_name
FROM spaces AS s
JOIN spacepermissions AS sp ON s.spaceid = sp.spaceid
LEFT JOIN user_mapping AS u ON sp.permusername = u.user_key
LEFT JOIN cwd_user AS c ON c.lower_user_name = u.lower_username
LEFT JOIN cwd_group AS cg ON sp.permgroupname = cg.group_name
LEFT JOIN cwd_directory AS cd ON cg.directory_id = cd.id
LEFT JOIN cwd_membership AS cm ON cg.id = cm.parent_id
LEFT JOIN cwd_user AS cu ON cu.id = cm.child_user_id
WHERE s.spacestatus = 'CURRENT'
  AND sp.permtype = 'SETSPACEPERMISSIONS'
ORDER BY spacekey,groupname;
PostgreSQL Syntax
SELECT DISTINCT s.spaceid,
       s.spacekey,
       s.spacename,
       u.lower_username AS individual_lower_username,
       cu.lower_user_name AS group_member_lower_username,
       sp.permgroupname AS groupname,
       cg.local AS local_group,
       cd.directory_name AS group_directory_name
FROM spaces AS s
JOIN spacepermissions AS sp ON s.spaceid = sp.spaceid
LEFT JOIN user_mapping AS u ON sp.permusername = u.user_key
LEFT JOIN cwd_user AS c ON c.lower_user_name = u.lower_username
LEFT JOIN cwd_group AS cg ON sp.permgroupname = cg.group_name
LEFT JOIN cwd_directory AS cd ON cg.directory_id = cd.id
LEFT JOIN cwd_membership AS cm ON cg.id = cm.parent_id
LEFT JOIN cwd_user AS cu ON cu.id = cm.child_user_id
WHERE s.spacestatus = 'CURRENT'
  AND sp.permtype = 'SETSPACEPERMISSIONS'
ORDER BY spacekey,groupname;
Microsoft SQL Server Syntax
SELECT DISTINCT s.SPACEID,
       s.SPACEKEY,
       s.SPACENAME,
       u.lower_username AS individual_lower_username,
       cu.lower_user_name AS group_member_lower_username,
       sp.PERMGROUPNAME AS groupname,
       cg.local AS local_group,
       cd.directory_name AS group_directory_name
FROM SPACES AS s
JOIN SPACEPERMISSIONS AS sp ON s.SPACEID = sp.SPACEID
LEFT JOIN user_mapping AS u ON sp.PERMUSERNAME = u.user_key
LEFT JOIN cwd_user AS c ON c.lower_user_name = u.lower_username
LEFT JOIN cwd_group AS cg ON sp.PERMGROUPNAME = cg.group_name
LEFT JOIN cwd_directory AS cd ON cg.directory_id = cd.id
LEFT JOIN cwd_membership AS cm ON cg.id = cm.parent_id
LEFT JOIN cwd_user AS cu ON cu.id = cm.child_user_id
WHERE s.SPACESTATUS = 'CURRENT'
  AND sp.PERMTYPE = 'SETSPACEPERMISSIONS'
ORDER BY SPACEKEY,groupname;
Oracle Syntax
SELECT DISTINCT s.spaceid,
       s.spacekey,
       s.spacename,
       u.lower_username AS individual_lower_username,
       cu.lower_user_name AS group_member_lower_username,
       sp.permgroupname AS groupname,
       cg.local AS local_group,
       cd.directory_name AS group_directory_name
FROM spaces s
JOIN spacepermissions sp ON s.spaceid = sp.spaceid
LEFT JOIN user_mapping u ON sp.permusername = u.user_key
LEFT JOIN cwd_user c ON c.lower_user_name = u.lower_username
LEFT JOIN cwd_group cg ON sp.permgroupname = cg.group_name
LEFT JOIN cwd_directory cd ON cg.directory_id = cd.id
LEFT JOIN cwd_membership cm ON cg.id = cm.parent_id
LEFT JOIN cwd_user cu ON cu.id = cm.child_user_id
WHERE s.spacestatus = 'CURRENT'
  AND sp.permtype = 'SETSPACEPERMISSIONS'
ORDER BY spacekey,groupname;

List all the spaces where a specific user (replace the string USERNAMEgoesHERE) has Space Admin permissions:

SQL Syntax
SELECT DISTINCT s.spaceid,
       s.spacekey,
       s.spacename,
       u.lower_username AS individual_lower_username,
       cu.lower_user_name AS group_member_lower_username,
       sp.permgroupname AS groupname,
       cg.local AS local_group,
       cd.directory_name AS group_directory_name
FROM spaces AS s
JOIN spacepermissions AS sp ON s.spaceid = sp.spaceid
LEFT JOIN user_mapping AS u ON sp.permusername = u.user_key
LEFT JOIN cwd_user AS c ON c.lower_user_name = u.lower_username
LEFT JOIN cwd_group AS cg ON sp.permgroupname = cg.group_name
LEFT JOIN cwd_directory AS cd ON cg.directory_id = cd.id
LEFT JOIN cwd_membership AS cm ON cg.id = cm.parent_id
LEFT JOIN cwd_user AS cu ON cu.id = cm.child_user_id
WHERE s.spacestatus = 'CURRENT'
  AND sp.permtype = 'SETSPACEPERMISSIONS'
  AND (u.lower_username = 'USERNAMEgoesHERE'
       OR cu.lower_user_name = 'USERNAMEgoesHERE')
ORDER BY spacekey,groupname;
PostgreSQL Syntax
SELECT DISTINCT s.spaceid,
       s.spacekey,
       s.spacename,
       u.lower_username AS individual_lower_username,
       cu.lower_user_name AS group_member_lower_username,
       sp.permgroupname AS groupname,
       cg.local AS local_group,
       cd.directory_name AS group_directory_name
FROM spaces AS s
JOIN spacepermissions AS sp ON s.spaceid = sp.spaceid
LEFT JOIN user_mapping AS u ON sp.permusername = u.user_key
LEFT JOIN cwd_user AS c ON c.lower_user_name = u.lower_username
LEFT JOIN cwd_group AS cg ON sp.permgroupname = cg.group_name
LEFT JOIN cwd_directory AS cd ON cg.directory_id = cd.id
LEFT JOIN cwd_membership AS cm ON cg.id = cm.parent_id
LEFT JOIN cwd_user AS cu ON cu.id = cm.child_user_id
WHERE s.spacestatus = 'CURRENT'
  AND sp.permtype = 'SETSPACEPERMISSIONS'
  AND (u.lower_username = 'USERNAMEgoesHERE'
       OR cu.lower_user_name = 'USERNAMEgoesHERE')
ORDER BY spacekey,groupname;
Microsoft SQL Server Syntax
SELECT DISTINCT s.SPACEID,
       s.SPACEKEY,
       s.SPACENAME,
       u.lower_username AS individual_lower_username,
       cu.lower_user_name AS group_member_lower_username,
       sp.PERMGROUPNAME AS groupname,
       cg.local AS local_group,
       cd.directory_name AS group_directory_name
FROM SPACES s
JOIN SPACEPERMISSIONS sp ON s.SPACEID = sp.SPACEID
LEFT JOIN user_mapping u ON sp.PERMUSERNAME = u.user_key
LEFT JOIN cwd_user c ON c.lower_user_name = u.lower_username
LEFT JOIN cwd_group cg ON sp.PERMGROUPNAME = cg.group_name
LEFT JOIN cwd_directory cd ON cg.directory_id = cd.id
LEFT JOIN cwd_membership cm ON cg.id = cm.parent_id
LEFT JOIN cwd_user cu ON cu.id = cm.child_user_id
WHERE s.SPACESTATUS = 'CURRENT'
  AND sp.PERMTYPE = 'SETSPACEPERMISSIONS'
  AND (u.lower_username = 'USERNAMEgoesHERE'
       OR cu.lower_user_name = 'USERNAMEgoesHERE')
ORDER BY SPACEKEY,groupname
Oracle Syntax
SELECT DISTINCT s.spaceid,
       s.spacekey,
       s.spacename,
       u.lower_username AS individual_lower_username,
       cu.lower_user_name AS group_member_lower_username,
       sp.permgroupname AS groupname,
       cg.local AS local_group,
       cd.directory_name AS group_directory_name
FROM spaces s
JOIN spacepermissions sp ON s.spaceid = sp.spaceid
LEFT JOIN user_mapping u ON sp.permusername = u.user_key
LEFT JOIN cwd_user c ON c.lower_user_name = u.lower_username
LEFT JOIN cwd_group cg ON sp.permgroupname = cg.group_name
LEFT JOIN cwd_directory cd ON cg.directory_id = cd.id
LEFT JOIN cwd_membership cm ON cg.id = cm.parent_id
LEFT JOIN cwd_user cu ON cu.id = cm.child_user_id
WHERE s.spacestatus = 'CURRENT'
  AND sp.permtype = 'SETSPACEPERMISSIONS'
  AND (u.lower_username = 'USERNAMEgoesHERE'
       OR cu.lower_user_name = 'USERNAMEgoesHERE')
ORDER BY spacekey,groupname

List all the pages created or last modified by a specific user (replace the string USERNAMEgoesHERE) and the space where they are located:

SQL Syntax
SELECT c.title,
       s.spacename,
       s.spacekey,
       u1.username AS PAGE_CREATOR,
       u2.username AS PAGE_LAST_MODIFIER
FROM content AS c
JOIN spaces AS s ON s.spaceid=c.spaceid
LEFT JOIN user_mapping AS u1 ON c.creator=u1.USER_KEY
LEFT JOIN user_mapping AS u2 ON c.lastmodifier=u2.USER_KEY
WHERE c.contenttype = 'PAGE'
  AND c.creator IS NOT NULL
  AND c.title IS NOT NULL
  AND c.content_status = 'current'
  AND c.prevver IS NULL
  AND (u1.username = 'USERNAMEgoesHERE'
       OR u2.username = 'USERNAMEgoesHERE')
ORDER BY u1.username,u2.username,c.title;
PostgreSQL Syntax
SELECT c.title,
       s.spacename,
       s.spacekey,
       u1.username AS PAGE_CREATOR,
       u2.username AS PAGE_LAST_MODIFIER
FROM content AS c
JOIN spaces AS s ON s.spaceid=c.spaceid
LEFT JOIN user_mapping AS u1 ON c.creator=u1.USER_KEY
LEFT JOIN user_mapping AS u2 ON c.lastmodifier=u2.USER_KEY
WHERE c.contenttype = 'PAGE'
  AND c.creator IS NOT NULL
  AND c.title IS NOT NULL
  AND c.content_status = 'current'
  AND c.prevver IS NULL
  AND (u1.username = 'USERNAMEgoesHERE'
       OR u2.username = 'USERNAMEgoesHERE')
ORDER BY u1.username,u2.username,c.title;
Microsoft SQL Server Syntax
SELECT c.TITLE,
       s.SPACENAME,
       s.SPACEKEY,
       u1.username AS PAGE_CREATOR,
       u2.username AS PAGE_LAST_MODIFIER
FROM CONTENT c
JOIN SPACES s ON s.SPACEID=c.SPACEID
LEFT JOIN user_mapping u1 ON c.CREATOR=u1.user_key
LEFT JOIN user_mapping u2 ON c.LASTMODIFIER=u2.user_key
WHERE c.CONTENTTYPE = 'PAGE'
  AND c.CREATOR IS NOT NULL
  AND c.TITLE IS NOT NULL
  AND c.CONTENT_STATUS = 'current'
  AND c.PREVVER IS NULL
  AND (u1.username = 'USERNAMEgoesHERE'
       OR u2.username = 'USERNAMEgoesHERE')
ORDER BY u1.username,u2.username,c.TITLE;
Oracle Syntax
SELECT c.title,
       s.spacename,
       s.spacekey,
       u1.username AS PAGE_CREATOR,
       u2.username AS PAGE_LAST_MODIFIER
FROM content c
JOIN spaces s ON s.spaceid=c.spaceid
LEFT JOIN user_mapping u1 ON c.creator=u1.USER_KEY
LEFT JOIN user_mapping u2 ON c.lastmodifier=u2.USER_KEY
WHERE c.contenttype = 'PAGE'
  AND c.creator IS NOT NULL
  AND c.title IS NOT NULL
  AND c.content_status = 'current'
  AND c.prevver IS NULL
  AND (u1.username = 'USERNAMEgoesHERE'
       OR u2.username = 'USERNAMEgoesHERE')
ORDER BY u1.username,u2.username,c.title;

List all users that have the Space Admin permission added to them individually:

SQL Syntax
SELECT s.spacekey,
       s.spacename,
       cu.user_name,
       cu.display_name
FROM spaces AS s
JOIN spacepermissions AS sp ON s.spaceid = sp.spaceid
JOIN user_mapping AS u ON sp.permusername = u.user_key 
JOIN cwd_user AS cu ON u.username = cu.user_name
WHERE sp.permtype = 'SETSPACEPERMISSIONS'
ORDER BY s.spacekey;
PostgreSQL Syntax
SELECT s.spacekey,
       s.spacename,
       cu.user_name,
       cu.display_name
FROM spaces AS s
JOIN spacepermissions AS sp ON s.spaceid = sp.spaceid
JOIN user_mapping AS u ON sp.permusername = u.user_key 
JOIN cwd_user AS cu ON u.username = cu.user_name
WHERE sp.permtype = 'SETSPACEPERMISSIONS'
ORDER BY s.spacekey;
Microsoft SQL Server Syntax
SELECT s.spacekey,
       s.spacename,
       cu.user_name,
       cu.display_name
FROM spaces s
JOIN spacepermissions sp ON s.spaceid = sp.spaceid
JOIN user_mapping u ON sp.permusername = u.user_key 
JOIN cwd_user cu ON u.username = cu.user_name
WHERE sp.permtype = 'SETSPACEPERMISSIONS'
ORDER BY s.spacekey;
Oracle Syntax
SELECT s.spacekey,
       s.spacename,
       cu.user_name,
       cu.display_name
FROM spaces s
JOIN spacepermissions sp ON s.spaceid = sp.spaceid
JOIN user_mapping u ON sp.permusername = u.user_key 
JOIN cwd_user cu ON u.username = cu.user_name
WHERE sp.permtype = 'SETSPACEPERMISSIONS'
ORDER BY s.spacekey;

SQL Queries (Confluence 3.5.x to 5.1.x)

List all users that are Space Admins:

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

List all groups that are Space Admins:

SELECT s.spacename,
       sp.permgroupname
FROM spaces AS s
JOIN spacepermissions AS sp ON s.spaceid = sp.spaceid
WHERE sp.permtype = 'SETSPACEPERMISSIONS'
  AND sp.PERMGROUPNAME != '';

Members with the System Administrator permission in Global Permissions (usual members of the confluence-administrators group) are admins of all spaces, including personal spaces. To check who has this permission go to General Configuration > Global Permissions.

User Macro

User macro to list space admins for all spaces:

## Macro title: Space Administrators
## Macro has a body: N
## Body processing: Selected body processing option
## Output: Selected output option
##
## Developed by: Andrew Frayling
## Modified by: Foogie Sim
## Date created: 21/03/2012
## Date modified: 1/05/2013
## Installed by: <your name>
## Macro to display a list of space administrators
## @noparams
#set($containerManagerClass = $content.class.forName('com.atlassian.spring.container.ContainerManager'))
#set($getInstanceMethod = $containerManagerClass.getDeclaredMethod('getInstance',null))
#set($containerManager = $getInstanceMethod.invoke(null,null))
#set($containerContext = $containerManager.containerContext)
#set($users = $userAccessor.getUsers())
#set($spaces = $spaceManager.getAllSpaces())
<table class="confluenceTable">
  <tr>
    <th class="confluenceTh">Space Administrator</th><th class="confluenceTh">Space</th>
  </tr>
#foreach($user in $users)
#foreach($spacer in $spaces)
#if ($permissionHelper.canAdminister($user, $spacer))
    <tr>
      <td class="confluenceTd">#usernameLink($user.name)</td><td class="confluenceTd"><a href="$req.contextPath/display/$spacer.key">$spacer.name</a></td>
    </tr>
  #end
#end
#end
</table>



Last modified on Aug 24, 2021

Was this helpful?

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