How to retrieve user and group access mappings / permissions from the Bamboo database
Platform Notice: Data Center Only - This article only applies to Atlassian apps on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Purpose
To quickly get user and group access mappings / permissions from Bamboo, you can query your Bamboo database. The SQL queries below will give you the mappings for Build Projects and Plans, Deployment Plans, Deployment Environments and Global Permissions.
Solution
The boolean true is not supported in MS SQL Server, if you use SQL Server, please replace true with 1.
Build Projects
SELECT PROJECT.TITLE AS PROJECT_NAME,
PROJECT_KEY,
(CASE
WHEN AE.TYPE = 'PRINCIPAL'
THEN 'USER'
WHEN AE.TYPE = 'GROUP_PRINCIPAL'
THEN 'GROUP'
END) as USER_OR_GROUP,
(CASE
WHEN AE.SID = 'ROLE_ANONYMOUS'
THEN 'Anonymous users'
WHEN AE.SID = 'ROLE_USER'
THEN 'Logged in users'
ELSE AE.SID
END) AS NAME,
MASK,
(CASE
WHEN MASK = 4
THEN 'CREATE_PLAN'
WHEN MASK = 16
THEN 'ADMIN'
WHEN MASK = 1
THEN 'VIEW PROJECT'
WHEN MASK = 1024
THEN 'CREATE REPOSITORY'
END) as PERMISSION_TYPE
FROM ACL_ENTRY AS AE
JOIN ACL_OBJECT_IDENTITY AS AOI
ON AE.ACL_OBJECT_IDENTITY = AOI.ID
JOIN PROJECT
ON AOI.OBJECT_ID_IDENTITY = PROJECT.PROJECT_ID
WHERE AE.GRANTING = true
ORDER BY PROJECT_NAME, PERMISSION_TYPE;Build Plans
SELECT PROJECT.TITLE AS PROJECT_NAME,
BUILD.TITLE AS PLAN_NAME,
BUILD.FULL_KEY AS PLAN_KEY,
(CASE
WHEN AE.TYPE = 'PRINCIPAL'
THEN 'USER'
WHEN AE.TYPE = 'GROUP_PRINCIPAL'
THEN 'GROUP'
END) as USER_OR_GROUP,
(CASE
WHEN AE.SID = 'ROLE_ANONYMOUS'
THEN 'Anonymous users'
WHEN AE.SID = 'ROLE_USER'
THEN 'Logged in users'
ELSE AE.SID
END) AS NAME,
MASK,
(CASE
WHEN MASK = 1
THEN 'VIEW'
WHEN MASK = 2
THEN 'EDIT'
WHEN MASK = 16
THEN 'ADMIN'
WHEN MASK = 64
THEN 'BUILD'
WHEN MASK = 128
THEN 'CLONE'
WHEN MASK = 2048
THEN 'VIEW CONFIGURATION'
WHEN MASK = 8192 --Available from 10.1.0
THEN 'CREATE PLAN BRANCH'
END) as PERMISSION_TYPE
FROM ACL_ENTRY AS AE
JOIN ACL_OBJECT_IDENTITY AS AOI
ON AE.ACL_OBJECT_IDENTITY = AOI.ID
JOIN BUILD AS BUILD
ON AOI.OBJECT_ID_IDENTITY = BUILD.BUILD_ID
JOIN PROJECT
ON BUILD.PROJECT_ID = PROJECT.PROJECT_ID
WHERE AE.GRANTING = true
AND BUILD.BUILD_TYPE = 'CHAIN'
ORDER BY PLAN_KEY, NAME, PERMISSION_TYPE;Deployment Projects
SELECT NAME as Deployment_project,
DP.DEPLOYMENT_PROJECT_ID,
AE.SID,
(CASE
WHEN AE.TYPE = 'PRINCIPAL'
THEN 'USER'
WHEN AE.TYPE = 'GROUP_PRINCIPAL'
THEN 'GROUP'
END) as USER_OR_GROUP,
(CASE
WHEN AE.SID = 'ROLE_ANONYMOUS'
THEN 'Anonymous users'
WHEN AE.SID = 'ROLE_USER'
THEN 'Logged in users'
ELSE AE.SID
END) AS NAME,
MASK,
(CASE
WHEN MASK = 1
THEN 'VIEW'
WHEN MASK = 2
THEN 'EDIT'
WHEN MASK = 16
THEN 'ADMIN'
WHEN MASK = 64
THEN 'BUILD'
WHEN MASK = 128
THEN 'CLONE'
WHEN MASK = 2048
THEN 'VIEW CONFIGURATION'
END) as PERMISSION_TYPE
FROM ACL_ENTRY AS AE
JOIN ACL_OBJECT_IDENTITY AS AOI
ON AE.ACL_OBJECT_IDENTITY = AOI.ID
JOIN DEPLOYMENT_PROJECT AS DP
ON DEPLOYMENT_PROJECT_ID = OBJECT_ID_IDENTITY
WHERE AE.GRANTING = true
ORDER BY NAME, AE.SID, PERMISSION_TYPE;Deployment Environments
SELECT DP.NAME as DeploymentProject,
DE.NAME as DeploymentEnvironment,
(CASE
WHEN AE.TYPE = 'PRINCIPAL'
THEN 'USER'
WHEN AE.TYPE = 'GROUP_PRINCIPAL'
THEN 'GROUP'
END) as USER_OR_GROUP,
(CASE
WHEN AE.SID = 'ROLE_ANONYMOUS'
THEN 'Anonymous users'
WHEN AE.SID = 'ROLE_USER'
THEN 'Logged in users'
ELSE AE.SID
END) AS NAME,
MASK,
(CASE
WHEN MASK = 1
THEN 'VIEW'
WHEN MASK = 2
THEN 'EDIT'
WHEN MASK = 64
THEN 'DEPLOY'
WHEN MASK = 2048
THEN 'VIEW CONFIGURATION'
END) as PERMISSION_TYPE
FROM ACL_ENTRY AS AE
JOIN ACL_OBJECT_IDENTITY AS AOI
ON AE.ACL_OBJECT_IDENTITY = AOI.ID
JOIN DEPLOYMENT_ENVIRONMENT DE ON DE.ENVIRONMENT_ID = OBJECT_ID_IDENTITY
JOIN DEPLOYMENT_PROJECT DP ON DE.PACKAGE_DEFINITION_ID = DP. DEPLOYMENT_PROJECT_ID
WHERE AE.GRANTING = true
ORDER BY DP.NAME, DE.NAME, AE.SID, PERMISSION_TYPE;
Global Permissions
SELECT (CASE
WHEN AE.TYPE = 'PRINCIPAL'
THEN 'USER'
WHEN AE.TYPE = 'GROUP_PRINCIPAL'
THEN 'GROUP'
END) as USER_OR_GROUP,
(CASE
WHEN AE.SID = 'ROLE_ANONYMOUS'
THEN 'Anonymous users'
WHEN AE.SID = 'ROLE_USER'
THEN 'Logged in users'
ELSE AE.SID
END) AS NAME,
MASK,
(CASE
WHEN MASK = 1
THEN 'ACCESS'
WHEN MASK = 4
THEN 'CREATE'
WHEN MASK = 1024
THEN 'CREATE REPOSITORY'
WHEN MASK = 16
THEN 'ADMIN'
END) as PERMISSION_TYPE
FROM ACL_ENTRY AS AE
JOIN ACL_OBJECT_IDENTITY AS AOI
ON AE.ACL_OBJECT_IDENTITY = AOI.ID
WHERE AE.GRANTING = true
AND AOI.OBJECT_ID_CLASS='com.atlassian.bamboo.security.GlobalApplicationSecureObject'
ORDER BY USER_OR_GROUP, PERMISSION_TYPE;Was this helpful?