List all permissions and users of Repositories in Bitbucket

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

This article provides an SQL query provides a list of users and groups related to repositories in Bitbucket.

Solution

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.

SQL query for providing a list of repositories with their assigned users

PostgreSQL

select distinct r.id                                       as "Repository ID",
                r.name                                     as "Repository Name",
                cu.id                                      as "User ID (Individual)",
                cu.user_name                               as "Username (Individual)",
                Concat(cu.first_name, ' ', cu.last_name)   as "Fullname (Individual)",
                rp.group_name                              as "Group Access",
                cu1.id                                     as "User ID (Group)",
                cu1.user_name                              as "Username (Group)",
                Concat(cu1.first_name, ' ', cu1.last_name) as "Fullname (Group)",
                cd.directory_name                          as "Directory (Individual)",
                cd.directory_type                          as "Directory Type (Individual)",
                cd1.directory_name                         as "Directory (Group)",
                cd1.directory_type                         as "Directory Type (Group)"
from repository r
         left join sta_repo_permission rp
                   on rp.REPO_ID = r.id
         left join sta_normal_user u
                   on rp.user_id = u.user_id
         left join cwd_user cu
                   on cu.lower_user_name = u.name
         left join cwd_membership cm
                   on cm.lower_parent_name = rp.group_name
         left join cwd_user cu1
                   on cu1.lower_user_name = cm.lower_child_name
         left join cwd_directory cd
                   on cd.id = cu.directory_id
         left join cwd_directory cd1
                   on cd1.id = cu1.directory_id
where rp.group_name is not null
   or rp.user_id is not null
ORDER BY r.id;

Oracle

SELECT DISTINCT r.id                                       AS "Repository ID",
                r.name                                     AS "Repository Name",
                cu.id                                      AS "User ID (Individual)",
                cu.user_name                               AS "Username (Individual)",
                cu.first_name || ' ' || cu.last_name       AS "Fullname (Individual)",
                rp.group_name                              AS "Group Access",
                cu1.id                                     AS "User ID (Group)",
                cu1.user_name                              AS "Username (Group)",
                cu1.first_name || ' ' || cu1.last_name     AS "Fullname (Group)",
                cd.directory_name                          AS "Directory (Individual)",
                cd.directory_type                          AS "Directory Type (Individual)",
                cd1.directory_name                         AS "Directory (Group)",
                cd1.directory_type                         AS "Directory Type (Group)"
FROM repository r
LEFT JOIN sta_repo_permission rp
       ON rp.REPO_ID = r.id
LEFT JOIN sta_normal_user u
       ON rp.user_id = u.user_id
LEFT JOIN cwd_user cu
       ON cu.lower_user_name = u.name
LEFT JOIN cwd_membership cm
       ON cm.lower_parent_name = rp.group_name
LEFT JOIN cwd_user cu1
       ON cu1.lower_user_name = cm.lower_child_name
LEFT JOIN cwd_directory cd
       ON cd.id = cu.directory_id
LEFT JOIN cwd_directory cd1
       ON cd1.id = cu1.directory_id
WHERE rp.group_name IS NOT NULL
   OR rp.user_id IS NOT NULL
ORDER BY r.id;

SQL query for providing the list of repositories with their active assigned users

PostgreSQL

select distinct r.id                                       as "Repository ID",
                r.name                                     as "Repository Name",
                cu.id                                      as "User ID (Individual)",
                cu.user_name                               as "Username (Individual)",
                Concat(cu.first_name, ' ', cu.last_name)   as "Fullname (Individual)",
                rp.group_name                              as "Group Access",
                cu1.id                                     as "User ID (Group)",
                cu1.user_name                              as "Username (Group)",
                Concat(cu1.first_name, ' ', cu1.last_name) as "Fullname (Group)",
                cd.directory_name                          as "Directory (Individual)",
                cd.directory_type                          as "Directory Type (Individual)",
                cd1.directory_name                         as "Directory (Group)",
                cd1.directory_type                         as "Directory Type (Group)"
from repository r
         left join sta_repo_permission rp
                   on rp.REPO_ID = r.id
         left join sta_normal_user u
                   on rp.user_id = u.user_id
         left join cwd_user cu
                   on cu.lower_user_name = u.name
         left join cwd_membership cm
                   on cm.lower_parent_name = rp.group_name
         left join cwd_user cu1
                   on cu1.lower_user_name = cm.lower_child_name
         left join cwd_directory cd
                   on cd.id = cu.directory_id
         left join cwd_directory cd1
                   on cd1.id = cu1.directory_id
where  (cu.is_active ='T' or cu1.is_active ='T') and (rp.group_name is not null or rp.user_id is not null)
ORDER BY r.id;

Oracle

SELECT DISTINCT r.id                                       AS "Repository ID",
                r.name                                     AS "Repository Name",
                cu.id                                      AS "User ID (Individual)",
                cu.user_name                               AS "Username (Individual)",
                cu.first_name || ' ' || cu.last_name       AS "Fullname (Individual)",
                rp.group_name                              AS "Group Access",
                cu1.id                                     AS "User ID (Group)",
                cu1.user_name                              AS "Username (Group)",
                cu1.first_name || ' ' || cu1.last_name     AS "Fullname (Group)",
                cd.directory_name                          AS "Directory (Individual)",
                cd.directory_type                          AS "Directory Type (Individual)",
                cd1.directory_name                         AS "Directory (Group)",
                cd1.directory_type                         AS "Directory Type (Group)"
FROM repository r
LEFT JOIN sta_repo_permission rp
       ON rp.REPO_ID = r.id
LEFT JOIN sta_normal_user u
       ON rp.user_id = u.user_id
LEFT JOIN cwd_user cu
       ON cu.lower_user_name = u.name
LEFT JOIN cwd_membership cm
       ON cm.lower_parent_name = rp.group_name
LEFT JOIN cwd_user cu1
       ON cu1.lower_user_name = cm.lower_child_name
LEFT JOIN cwd_directory cd
       ON cd.id = cu.directory_id
LEFT JOIN cwd_directory cd1
       ON cd1.id = cu1.directory_id
WHERE (cu.is_active = 'T' OR cu1.is_active = 'F') AND (rp.group_name IS NOT NULL OR rp.user_id IS NOT NULL)
ORDER BY r.id;

ℹ️ The result shows users that have access through a group and users that have individual access to repositories

View information about directories

PostgreSQL and Oracle

select directory_name,
       description,
       created_date,
       directory_type
from   cwd_directory

MSSQL

select directory_name,
       description,
       created_date,
       directory_type
from   dbo.cwd_directory

Reported Issue

If you are having issues with permission on objects, when you run the queries in MS SQL, you will need to enable mapping with the master database through the database properties as shown in the image below.

(Auto-migrated image: description temporarily unavailable)

Updated on May 22, 2025

Was this helpful?

It wasn't accurateIt wasn't clearIt wasn't relevant

List all permissions and users of Repositories in Bitbucket

Summary

Solution

Still need help?