特定のグループがアクセス可能なスペースの一覧を作成する方法
プラットフォームについて: 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 は除く
ユースケース
監査や管理のために、特定のグループがアクセス可能な (最低でも表示権限を持っている) スペースを管理者が確認したい場合があります。これはいくつかの SQL クエリで実行できます。
ソリューション
Run the following SQL queries against the Confluence database, replacing <group_name > with the group name, in lowercase.
The following will list all the spaces that contain permissions :
SELECT s.SPACEKEY FROM SPACEPERMISSIONS sp JOIN SPACES s ON s.SPACEID = sp.SPACEID JOIN cwd_group g ON sp.PERMGROUPNAME = g.group_name WHERE sp.PERMGROUPNAME IN ( SELECT g.group_name FROM cwd_group g WHERE g.lower_group_name = '<group_name>' ) GROUP BY s.SPACEKEY ORDER BY s.SPACEKEY;
The following will list all the spaces that the group has been granted permissions with the permission type:
SELECT s.SPACEKEY, sp.PERMTYPE as PERMISSIONS FROM SPACEPERMISSIONS sp JOIN SPACES s ON s.SPACEID = sp.SPACEID JOIN cwd_group g ON sp.PERMGROUPNAME = g.group_name WHERE sp.PERMGROUPNAME IN ( SELECT g.group_name FROM cwd_group g WHERE g.lower_group_name = '<group_name>' );
次のクエリは、すべてのスペースと、各スペースにアクセス可能なすべてのユーザーを返します。
SELECT s.spacename as Space, u.user_name as Username FROM spacepermissions sp JOIN spaces s ON s.spaceid = sp.spaceid JOIN cwd_group g ON sp.permgroupname = g.group_name JOIN cwd_membership m ON g.id = m.parent_id JOIN cwd_user u ON u.id = m.child_user_id WHERE sp.permgroupname IN ( SELECT g.group_name FROM cwd_group g ) ORDER BY s.spacekey;
The following will list all groups, along with spaces they have access to:
SELECT DISTINCT sp.permgroupname, s.spacename FROM SPACEPERMISSIONS sp JOIN SPACES s ON sp.spaceid = s.spaceid LEFT JOIN user_mapping um ON sp.permusername = um.user_key WHERE sp.permgroupname IS NOT NULL order by sp.permgroupname;
For oracle to search for all groups with a like name and output as a list with key, name and group:
SELECT s.spacekey,s.SPACENAME,sp.PERMGROUPNAME FROM spacepermissions sp JOIN spaces s ON s.spaceid = sp.spaceid JOIN cwd_group g ON sp.permgroupname = g.group_name WHERE g.lower_group_name like 'department%' ORDER BY s.spacekey,s.SPACENAME,g.lower_group_name;
Please note that these results will also not accurately reflect any groups in nested groups.