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;
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:

PostgreSQL 
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;
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

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 (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
## @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>

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


Last modified on Mar 30, 2024

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

はい
いいえ
この記事についてのフィードバックを送信する

関連コンテンツ

  • 関連コンテンツがありません
Powered by Confluence and Scroll Viewport.