すべてのスペースのすべてのスペース作成者および管理者のリストを表示する方法

プラットフォームについて: Data Center のみ。 - This article only applies to Atlassian apps on the Data Center プラットフォーム。

この KB は Data Center バージョンの製品用に作成されています。Data Center 固有ではない機能の Data Center KB は、製品のサーバーバージョンでも動作する可能性はありますが、テストは行われていません。 Server* 製品のサポートは 2024 年 2 月 15 日に終了しました。Server 製品を実行している場合は、アトラシアン Server サポート終了のお知らせにアクセスして、移行オプションを確認してください。

*Fisheye および Crucible は除く

要約

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.

ソリューション

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

Pick the right method: This article covers two distinct approaches to retrieve space admin lists. Choose based on your access and comfort level:

メソッド	要件	最適な状況
SQL クエリ	Direct database access; PostgreSQL, MySQL, or MSSQL knowledge; ability to run SELECT queries	Confluence administrators who have DB access and need a comprehensive, exportable list in one query
User Macro	Confluence System Administrator privileges; access to General Configuration > User Macros	Administrators who want a reusable, in-page display of space admins without direct database access

If you do not have database access and cannot create User Macros, contact your Confluence System Administrator or Atlassian Support.

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.

Database-specific SQL syntax: The SQL queries in this article use standard ANSI SQL, but small syntax differences exist between database platforms:

PostgreSQL: Use SELECT syntax as-is; no modifications needed.
MySQL: String functions and datetime handling may differ; consult your MySQL version documentation if you encounter errors.
MSSQL (SQL Server): Use CONVERT or FORMAT for date/time columns instead of PostgreSQL's TO_CHAR. Refer to MSSQL CAST and CONVERT documentation for exact syntax.

If a query fails with a syntax error, check your database platform in Confluence Administration > System Information > Database, then adjust the query accordingly.

List of all spaces and their creators:

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:

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:

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:

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:


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:

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 spacekey

Verification — SQL path: After running the query, spot-check the results against a space you manage. Log in to that space, go to Space settings > Permissions, and confirm the space admin names match the query output.

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

The following User Macros list space admins

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

## 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):

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

Verification — User Macro path: After inserting the macro on a test page, view the rendered page and confirm it displays space names with their administrators. Cross-check one space against Space settings > Permissions to verify accuracy.

更新日時: June 2, 2026

この内容はお役に立ちましたか?

正確ではなかった明確ではなかった関係なかった

アトラシアンサポート