How to retrieve user and group access mappings / permissions from the Bamboo database

Still need help?

The Atlassian Community is here for you.

Ask the community

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'
        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'              
        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;
Deployent 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'
         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'
         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 PLAN'
          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;

Last modified on Sep 6, 2018

Was this helpful?

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