How do you list the users and permissions for all projects?

Dan Reid_325ij2

This article is quite handy, but the query it provides will return only repositories that have their own unique assigned users (permissions). But we have a lot of repos that inherit permissions from the project they are in, and those repos won't show in the query from the article. 

Does anyone have suggestions for a SQL query that will return all users of all projects, by project?

Amirsina Haftlangi

The following SQL query provides a list of projects and repositories with their respective users alongside user permission in Bitbucket. This query has been tested with Microsoft SQL Server.

WITH project_users 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)",
                pp.perm_id                                 AS "Permission Type"
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.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
 
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.perm_id                                 AS "Permission Type"
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
WHERE  pp.group_name IS NOT NULL  OR pp.user_id IS NOT NULL
)
 
SELECT * FROM project_users
ORDER  BY [Project ID] ;

 Permission type:

PROJECT_VIEW=10
REPO_READ=0
REPO_WRITE=1
REPO_ADMIN=8
PROJECT_READ=2
PROJECT_WRITE=3
PROJECT_ADMIN=4
LICENSED_USER=9
PROJECT_CREATE=5
ADMIN=6
SYS_ADMIN=7