Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,365,269
Community Members
 
Community Events
168
Community Groups

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

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?

1 answer

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

Do we have a similar query for POSTGRESQL? Thank you. 

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
VERSION
6.6.3
TAGS

Atlassian Community Events