Querying the Bitbucket Server and Data Center database to get a list of users, group memberships and source directory
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
Important Note on DB Queries
This Database Query / Functionality is not officially supported!
- Atlassian is not responsible for translating queries for different databases.
- This query should only be used as an example. Any desired adjustments should be checked with your local Database Administrator, including queries related to different DBMS that are not present/listed in this KB article, like Oracle Database.
- Atlassian will not be providing support for changes, adjustments, or inquiries regarding the below queries, as this is not part of our Atlassian Support Offerings.
- Atlassian does not provide any support to sample database queries provided in our Knowledge Base documents. Extraction of data from Bitbucket should be performed using our REST API Documentation because we do not keep all data relationships in the DB. Many connections to data are handled in the code. The REST API can go through the code paths to get the data. A SQL would have to do that manually or, in some cases, it can’t be done at all.
Summary
The following query lists users in Bitbucket, their group memberships and the source user directory:
Solution
SELECT u.user_name,
g.group_name,
g.id AS "group_id",
d.directory_name,
d.id AS "directory_id"
FROM cwd_membership m
INNER JOIN cwd_user u ON m.child_id = u.id
INNER JOIN cwd_group g ON m.parent_id = g.id
INNER JOIN cwd_directory d ON g.directory_id = d.id
ORDER BY d.directory_name, u.user_name, g.group_name;
Last modified on Nov 23, 2023
Powered by Confluence and Scroll Viewport.