SQL to check Bitbucket user permission
Bitbucket permission can be assigned at project level or repository level for a group and user.
The SQL (MSSQL Server) below can be used to check all projects & repositories permission for a given user, include the access assigned to an individual user or a group.
with repo_access as (
select distinct p.id as "Project ID",
p.name as "Project Name",
r.name as "Repo Name",
cu.id as "User ID (Individual)",
cu.user_name as "Username (Individual)",
Concat(cu.first_name, ' ', cu.last_name) as "Fullname (Individual)",
srp.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)"
from dbo.project p
join dbo.sta_normal_project np
on np.project_id = p.id
left join dbo.repository r on r.project_id = p.id
left join dbo.sta_repo_permission srp on srp.repo_id = r.id
left join dbo.sta_normal_user u
on srp.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 = srp.group_name
left join dbo.cwd_user cu1
on cu1.lower_user_name = cm.lower_child_name
where srp.group_name is not null or srp.user_id is not null
union
select distinct p.id as "Project ID",
p.name as "Project Name",
'' as "Repo 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)"
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
where pp.group_name is not null or pp.user_id is not null
)
select * from repo_access where [Username (Individual)] = 'Replace with UserId' or [Username (Group)] = 'Replace with UserID'
order by [Project ID] ;