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

• • REST API for Bitbucket versions 7.21.x
  • REST API for Bitbucket versions 8.x

• 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

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.