How to get a list of all users that inherit access to Fisheye and Crucible through groups
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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.
*Except Fisheye and Crucible
Summary
Access to Fisheye and Crucible is managed inside the global permissions page and is set on a per-group or per-user basis. The purpose of this knowledge base article is to show you how to get a list of all users that have access to Fisheye and Crucible based on the groups they belong to and have been granted access to the applications inside the global permissions page directly from the database.
Solution
Since both applications (Fisheye and Crucible) are licensed separately (i.e. user seats are counted separately) you'll need two select queries:
Users with access to Fisheye (controlled by Global Permissions > Groups)
1
2
3
4
5
6
7
8
select distinct cu.user_name
from cwd_user cu
join cwd_membership cm
on cu.id = cm.child_id
join cru_global_group_permission cggp
on cm.parent_name = cggp.cru_group_name
where cggp.cru_global_permission = 'FISHEYE_USER'
and cu.active = 'T';Users with access to Crucible (controlled by Global Permissions > Groups)
1
2
3
4
5
6
7
8
select distinct cu.user_name
from cwd_user cu
join cwd_membership cm
on cu.id = cm.child_id
join cru_global_group_permission cggp
on cm.parent_name = cggp.cru_group_name
where cggp.cru_global_permission = 'CRUCIBLE_USER'
and cu.active = 'T';The select queries above were built for PostgreSQL 9.6. You may need to adjust them to work with other databases e.g. Oracle, MySQL and etc.
Was this helpful?