List all permissions and users of Repositories in Bitbucket
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
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;
The result shows users that have access through a group and users that have individual access to repositories
View information about directories
select directory_name, description, created_date, directory_type from cwd_directory
select directory_name, description, created_date, directory_type from dbo.cwd_directory
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.