How to view a list of all space creators and administrators for all spaces
このページの内容は、Confluence でのカスタマイズや開発の変更に関連しています。したがって、アトラシアン サポートは、このページに記載されている手順に対するサポートの提供を保証できません。この資料は情報提供のみを目的としているため、お客様自身の責任でご使用ください。
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
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.
*Fisheye および Crucible は除く
要約
The purpose of this document is to get a list of all space creators and administrators for all spaces.
環境
- Confluence Server または Data Center 3.5+
ソリューション
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:
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;
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;
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;
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:
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;
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;
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;
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:
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;
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;
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
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:
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;
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;
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;
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:
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;
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;
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;
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;
List all users that have Space Admin permissions added individually without being members of groups having Space Admin Permission:
PostgreSQL
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
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:
## 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>
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) 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