How to report on permissions in Bitbucket Server and Data Center

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

Summary

In some cases, it may be required to report on the levels of permissions.

Bitbucket Server provides the capability to set permissions at various levels (see Controlling access to code). However, an overview of the set permissions is not available (e.g. showing the project permission at the repository level).

Solution

Solution #1 - REST API endpoint (preferred)

The non-aggregated information can be retrieved by using the Bitbucket Server REST APIs

  • The permissions set at a global level can be accessed with the following endpoints (for both groups and users, also with no permissions)

    /rest/api/1.0/admin/permissions/groups
    /rest/api/1.0/admin/permissions/groups/none
    /rest/api/1.0/admin/permissions/users
    /rest/api/1.0/admin/permissions/users/none
  • The permissions set at project level can be retrieved with the following endpoints (for both groups and users) 

    /rest/api/1.0/projects/{projectKey}/permissions/groups
    /rest/api/1.0/projects/{projectKey}/permissions/groups/none
    /rest/api/1.0/projects/{projectKey}/permissions/users
    /rest/api/1.0/projects/{projectKey}/permissions/users/none
  • For the permissions at repositories, level use the following endpoints

    /rest/api/1.0/projects/{projectKey}/repos/{repositorySlug}/permissions/groups
    /rest/api/1.0/projects/{projectKey}/repos/{repositorySlug}/permissions/groups/none
    /rest/api/1.0/projects/{projectKey}/repos/{repositorySlug}/permissions/users
    /rest/api/1.0/projects/{projectKey}/repos/{repositorySlug}/permissions/users/none

When the goal is to report which users have access to the repositories, after retrieving group permissions we will need to find the group members with the following endpoints:

  • Retrieves a list of users that are members of a specified group.
/REST/API/1.0/ADMIN/GROUPS/MORE-MEMBERS?CONTEXT&FILTER
  • Retrieves a list of users that are not members of a specified group
/REST/API/1.0/ADMIN/GROUPS/MORE-NON-MEMBERS?CONTEXT&FILTER
  • Retrieves a list of groups the specified user is a member of:
/REST/API/1.0/ADMIN/USERS/MORE-MEMBERS?CONTEXT&FILTER
  • Retrieves a list of groups the specified user is not a member of
/REST/API/1.0/ADMIN/USERS/MORE-NON-MEMBERS?CONTEXT&FILTER


Solution #2 - Database query

Important Note on DB Queries

  • This Database Query / Functionality is not officially supported!

  • Atlassian is not responsible for translating queries for different databases.
    • This query should only be used as an example. Any desired adjustments should be checked with your local Database Administrator, including queries related to different DBMS that are not present/listed in this KB article, like Oracle Database. 
  • Atlassian will not be providing support for changes, adjustments, or inquiries regarding the below queries, as this is not part of our Atlassian Support Offerings.
    • Atlassian does not provide any support to sample database queries provided in our Knowledge Base documents. Extraction of data from Bitbucket should be performed using our REST API Documentation because we do not keep all data relationships in the DB. Many connections to data are handled in the code. The REST API can go through the code paths to get the data. A SQL would have to do that manually or, in some cases, it can’t be done at all.

Overview of the permission tables

  •  the permission tables contain two columns
    • group_name: this is populated in case a group is configured in the permissions user interface in Bitbucket Server at any level. This cell contains the string of the group name.
    • user_id: this is populated in case a user is configured in the permissions user interface in Bitbucket Server at any level. This contains the user id, and is a foreign key for the stash_user table.
    • the perm_id can be mapped to the following values (not in the database):
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

Please also consider the "Permission matrix" at the end of Controlling access to code to better understand how the permissions at the various level work together.

Queries

This query provides the information regarding the Global Permissions, they are accessible in the user interface from the <BITBUCKET_URL>/admin/permissions URL:

SELECT * FROM STA_GLOBAL_PERMISSION;

This query returns the list of Project Permissions excluding the private projects (project_type = 0 means "not private projects"):

SELECT p.id, p.name, p.project_key, pp.perm_id, pp.group_name, u.name, pp.user_id 
FROM STA_PROJECT_PERMISSION pp LEFT JOIN STA_NORMAL_USER u ON (pp.user_id = u.user_id) , PROJECT p 
WHERE pp.project_id = p.id 
AND p.project_type = 0 
ORDER BY p.id;

This query returns the list of Repository Permissions:

SELECT r.id, p.project_key, r.project_id, r.slug, r.name, rp.perm_id, rp.group_name, u.name, rp.user_id 
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 PROJECT p on r.project_id = p.id 
ORDER BY r.id;


Default Permissions

This query will return the default permissions for a project. These are not specific to a user or group. If the default permission is "No Access", no rows will be returned:

SELECT p.name, 
CASE WHEN pp.perm_id = 2 THEN 'PROJECT_READ' 
     WHEN pp.perm_id = 3 THEN 'PROJECT_WRITE' 
     WHEN pp.perm_id = 4 THEN 'PROJECT_ADMIN' 
END AS default_perm 
FROM sta_project_permission pp 
JOIN project p ON (pp.project_id = p.id) 
WHERE user_id IS null 
AND group_name IS null;

Additional queries (useful but not strictly related to set of permissions)
This query does not provide a details of the permissions set, and for this reason is different from the ones above. They can be helpful to report on private vs public projects, this information is saved in the is_public column:

SELECT p.id, p.name, p.project_key, np.is_public
FROM STA_NORMAL_PROJECT np, PROJECT p
WHERE p.id = np.project_id
AND p.project_type = 0
ORDER BY p.id;

This query will help in identifying the relationship between projects and repositories.

SELECT r.id, r.slug, r.name, r.project_id
FROM REPOSITORY r
ORDER BY r.id;


This query lists all branch rules, projects, and repos they apply to and any exempted users to a particular branch rule.


select 
  a."REF_TYPE", 
  a."REF_VALUE" branch_name, 
  a."REF_ID" rule_id, 
  a."RESTRICTION_TYPE", 
  a."SCOPE_TYPE", 
  p."name" project_name, 
  p.project_key project_key, 
  r."name" repo_name, 
  r.slug repo_slug, 
  b."FK_RESTRICTED_ID" referenced_rule, 
  u."name" exempted_user 
from 
  "AO_6978BB_RESTRICTED_REF" a 
  left join project p on a."RESOURCE_ID" = p.id 
  left join "AO_6978BB_PERMITTED_ENTITY" b on b."FK_RESTRICTED_ID" = a."REF_ID" 
  left join sta_normal_user u on u.user_id = b."USER_ID" 
  left join repository r on r.project_id = p.id;


Solution #3 - User permissions export

Starting from Bitbucket 8.5, it is possible to export users and user permissions in CSV format.

Please see: Exporting list of users and user permissions

Note that this feature requires a Data Center license.




Last modified on Oct 27, 2023

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.