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

関連コンテンツ

  • 関連コンテンツがありません

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

このページの内容は、Confluence でのカスタマイズや開発の変更に関連しています。したがって、アトラシアン サポートは、このページに記載されている手順に対するサポートの提供を保証できません。この資料は情報提供のみを目的としているため、お客様自身の責任でご使用ください。

プラットフォームについて: サーバーと Data Center のみ。この記事は、サーバーおよび Data Center プラットフォームのアトラシアン製品にのみ適用されます。

要約

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:

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

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>



最終更新日 2022 年 7 月 4 日

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

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

関連コンテンツ

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