How to view a list of all space creators and administrators for all spaces
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
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.
The purpose of this document is to get a list of all space creators and administrators for all spaces.
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:
1
2
3
4
5
6
7
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;If you'd like to exclude certain spacetype...
Personal Space is identified with personal spacetype in the spaces table, whereby Global Space is identified by using global spacetype in the spaces table accordingly. As such, we could also filter out the above queries further depending on which spacetype we'd like to exclude. In the example below, personal spacetype is to be filtered out:
1
2
3
4
5
6
7
8
SELECT s.spacename,
s.spacekey,
s.creator,
u.*
FROM spaces AS s
JOIN user_mapping u ON s.creator = u.user_key
WHERE s.spacetype = 'global'
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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;List all the spaces where a specific user (replace the string USERNAMEgoesHERE) has Space Admin permissions:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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;List all users that have the Space Admin permission added to them individually:
1
2
3
4
5
6
7
8
9
10
11
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;List all users that have Space Admin permissions added individually without being members of groups having Space Admin Permission:
1
2
3
4
5
6
7
8
9
10
11
12
SELECT DISTINCT s.spaceid,
s.spacekey,
s.spacename, c.email_address,sp.permgroupname as user_group_name,
u.lower_username AS individual_lower_username
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
WHERE s.spacestatus = 'CURRENT'
AND sp.permtype = 'SETSPACEPERMISSIONS'
AND sp.permgroupname is null
ORDER BY spacekeySQL Queries (Confluence 3.5.x to 5.1.x)
List all users that are Space Admins:
1
2
3
4
5
6
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:
1
2
3
4
5
6
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
The following User Macros list space admins
1. List space admins for all spaces
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
## Macro title: Space Administrators
## Macro has a body: N
## Body processing: Selected body processing option
## Output: Selected output option
##
## Developed by: Andrew Frayling (21/03/2012)
## Modified by: Foogie Sim (01/05/2013)
## Modified by: Mal Ninnes (19/03/2024)
## Installed by: <your name>
## Macro to display a list of space administrators for all spaces
## @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($spaces = $spaceManager.getAllSpaces())
<table class="confluenceTable">
<tr>
<th class="confluenceTh">Space</th><th class="confluenceTh">Space Administrator</th>
</tr>
#foreach($spacer in $spaces)
<tr>
<td class="confluenceTd">$spacer.name</a></td>
<td class="confluenceTd">#set($admins=$spaceManager.getSpaceAdmins($spacer)) #foreach($admin in $admins) $admin.name, #end</td>
</tr>
#end
</table>2. List space admins for selected spaces After inserting this macro in a page, you will need to edit it and manually enter the spaces you want to display administrators for (use space keys separated by commas).
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
## Macro title: Space Administrators Selected Spaces
## Macro has a body: N
## Body processing: Selected body processing option
## Output: Selected output option
## Developed by: Andrew Frayling (21/03/2012)
## Modified by: Foogie Sim (01/05/2013), Mal Ninnes (19/03/2024), M Kurdi (26/09/2024)
## Installed by: <your name>
## Macro to display a list of space administrators for multiple spaces
## @param spaceKeys:title=Select Spaces|type=string|multiple=true|desc=Choose the spaces you want to display administrators for (use space keys separated by commas)
#set($spaceKeysList = $paramspaceKeys.split(","))
<table class="confluenceTable">
<tr>
<th class="confluenceTh">Space</th>
<th class="confluenceTh">Space Administrators</th>
</tr>
#foreach($spaceKey in $spaceKeysList)
#set($space = $spaceManager.getSpace($spaceKey.trim()))
#if($space)
<tr>
<td class="confluenceTd">$space.name</td>
<td class="confluenceTd">
#set($admins = $spaceManager.getSpaceAdmins($space))
#if($admins.size() > 0)
#foreach($admin in $admins)
$admin.name#if($foreach.hasNext), #end
#end
#else
#end
</td>
</tr>
#end
#end
</table>In Confluence 7.19.x and later versions, if variables do not render in your user macro (similar to outlined in CONFSERVER-82741 - Variables in user macro are not resolved) then you'll need to add the spaceManager velocity object to the macro.required.velocity.context.keys system parameter (in your setenv.sh on Linux and Java service options on Windows):
1
CATALINA_OPTS="-Dmacro.required.velocity.context.keys=spaceManager ${CATALINA_OPTS}"If this is added, a restart of Confluence is required for the setting to take effect. Ref: Confluence System properties
Was this helpful?