List all permissions and users of Projects and Repositories in Bitbucket

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

The following SQL queries provide list of users and groups related to projects and repositories in Bitbucket. These queries have been tested with PostgreSQL and MS SQL.

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

PostgreSQL
select distinct p.id as "Project ID", p.name as "Project Name", 
cu.id as "User ID (Individual)",cu.user_name as "Username (Individual)",
concat(cu.first_name, ' ', cu.last_name) AS "Fullname (Individual)", 
pp.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 (Group)",
cd1.directory_name as "Directory (Group)",cd1.directory_type as "Directory Type (Group)"
from project p
join sta_normal_project np on np.project_id=p.id
left join sta_project_permission pp on pp.project_id=p.id
left join sta_normal_user u on pp.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=pp.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 pp.group_name is not null or pp.user_id is not null
ORDER BY p.id
MS SQL
select distinct p.id as "Project ID", p.name as "Project Name", 
cu.id as "User ID (Individual)",cu.user_name as "Username (Individual)",
concat(cu.first_name, ' ', cu.last_name) AS "Fullname (Individual)", 
pp.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 (Group)",
cd1.directory_name as "Directory (Group)",cd1.directory_type as "Directory Type (Group)"
from dbo.project p
join dbo.sta_normal_project np on np.project_id=p.id
left join dbo.sta_project_permission pp on pp.project_id=p.id
left join dbo.sta_normal_user u on pp.user_id = u.user_id
left join dbo.cwd_user cu on cu.lower_user_name=u.name
left join dbo.cwd_membership cm on cm.lower_parent_name=pp.group_name
left join dbo.cwd_user cu1 on cu1.lower_user_name=cm.lower_child_name
left join dbo.cwd_directory cd on cd.id = cu.directory_id
left join dbo.cwd_directory cd1 on cd1.id = cu1.directory_id
where pp.group_name is not null or pp.user_id is not null
ORDER BY p.id


(tick) 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)", 
pp.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 project p
join sta_normal_project np on np.project_id=p.id
join repository r on r.project_id=p.id
left join sta_project_permission pp on pp.project_id=p.id
left join sta_normal_user u on pp.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=pp.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 pp.group_name is not null or pp.user_id is not null
ORDER BY r.id
MS SQL
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)", 
pp.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 dbo.project p
join dbo.sta_normal_project np on np.project_id=p.id
join dbo.repository r on r.project_id=p.id
left join dbo.sta_project_permission pp on pp.project_id=p.id
left join dbo.sta_normal_user u on pp.user_id = u.user_id
left join dbo.cwd_user cu on cu.lower_user_name=u.name
left join dbo.cwd_membership cm on cm.lower_parent_name=pp.group_name
left join dbo.cwd_user cu1 on cu1.lower_user_name=cm.lower_child_name
left join dbo.cwd_directory cd on cd.id = cu.directory_id
left join dbo.cwd_directory cd1 on cd1.id = cu1.directory_id
where pp.group_name is not null or pp.user_id is not null
ORDER BY r.id

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


Sample output

(tick) View information about directories 

PostgreSQL
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.



最終更新日 2019 年 5 月 31 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.