List all permissions and users of Repositories in Bitbucket
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.
The following SQL query provides a list of users and groups related to repositories in Bitbucket. This query has been tested with PostgreSQL.
- SQL query for providing 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;- SQL query for providing 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;
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
MS SQL
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.
最終更新日: 2022 年 12 月 13 日
Powered by Confluence and Scroll Viewport.