List all permissions and users of Repositories in Bitbucket

Still need help?

The Atlassian Community is here for you.

Ask the community

The following SQL query provides a list of users and groups related to repositories in Bitbucket. This query has been tested with PostgreSQL.

(tick) SQL query for providing list of repositories with their assigned users

select distinct                                       as "Repository ID",
                                           as "Repository Name",
                                            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",
                                           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 =
         left join sta_normal_user u
                   on rp.user_id = u.user_id
         left join cwd_user cu
                   on cu.lower_user_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 = cu.directory_id
         left join cwd_directory cd1
                   on = cu1.directory_id
where rp.group_name is not null
   or rp.user_id is not null

(info) The result shows users that have access through a group and users that have individual access to repositories

(tick) View information about directories 
PostgreSQL and Oracle
select directory_name,
from   cwd_directory 
select directory_name,
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.

Last modified on Aug 11, 2020

Was this helpful?

Provide feedback about this article
Powered by Confluence and Scroll Viewport.