Need Assistance with Querying Project and Plan Details in Bamboo for a Specific User

[Arun Sankar]

Hello Atlassian Community,

I am currently developing a web application that integrates with Bamboo. The application needs to programmatically determine which projects and plans a specific user has access to, whether they are newly created or previously assigned.

Problem Statement:

I need to display the project and plan details for the logged-in user. When I query the database, I can successfully retrieve details for the newly created projects and plans. However, I am unable to fetch the details for the existing projects and plans that the user should have access to. This leads me to believe that my query might be incorrect or that I might need to query different tables.

Here is a summary of what I need to achieve:

• Retrieve all projects and plans accessible to a specific user (both newly created and existing ones).
• Ensure that the query accounts for the user's permissions, including those granted via group memberships.

Current Query:

WITH UserGroups AS (
SELECT cu.user_name AS sid
FROM cwd_user cu
WHERE cu.user_name = 'arunsa'
UNION
SELECT cg.group_name AS sid
FROM cwd_membership cm
JOIN cwd_group cg ON cm.parent_id = cg.id
WHERE cm.lower_child_name = 'arunsa'
),
ProjectPermissions AS (
SELECT DISTINCT
p.title AS PROJECT_NAME,
NULL AS PLAN_NAME
FROM acl_entry ae
JOIN acl_object_identity aoi ON ae.acl_object_identity = aoi.id
JOIN project p ON aoi.object_id_identity::bigint = p.project_id
WHERE ae.granting = TRUE
AND ae.sid IN (SELECT sid FROM UserGroups)
),
PlanPermissions AS (
SELECT DISTINCT
p.title AS PROJECT_NAME,
b.title AS PLAN_NAME
FROM acl_entry ae
JOIN acl_object_identity aoi ON ae.acl_object_identity = aoi.id
JOIN build b ON aoi.object_id_identity::bigint = b.build_id
JOIN project p ON b.project_id = p.project_id
WHERE ae.granting = TRUE
AND ae.sid IN (SELECT sid FROM UserGroups)
)
SELECT DISTINCT
PROJECT_NAME,
PLAN_NAME
FROM (
SELECT * FROM ProjectPermissions
UNION ALL
SELECT * FROM PlanPermissions
) AS CombinedNames
ORDER BY PROJECT_NAME, PLAN_NAME NULLS FIRST;

[Shashank Kumar]

Hello Arun,

Welcome to Atlassian community.

I believe what you are looking for can be found at How to retrieve user and group access mappings / permissions from the Bamboo database 

Regards,

Shashank Kumar

**please don't forget to Accept the answer if your query was answered**

[Arun Sankar]

@Shashank Kumar 

Thank you for your help. However, the current query retrieves all projects and plans created by the user, as well as projects assigned to the user. Unfortunately, it does not retrieve plans assigned (or with permissions granted) to the user.

[Shashank Kumar]

Hello Arun,

The below 2 queries from the link which I provided provides Project and plan permissions granted to each user.

* Bamboo project permission 

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;

 * Bamboo plan Permission

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

Regards,

Shashank kumar

If you are looking for anything specific can you please let me know with an example!  

[Arun Sankar]

@Shashank Kumar 

I am attaching the project and plan details as shown in the Bamboo dashboard, along with the query and the query result from the database.

Bamboo Dashboard Details:

Database Query:

Query Result:

[Shashank Kumar]

Hello Arun,

The Database is the source of truth, Bamboo Dashboard can be little misleading.

For example the Project and Plans which you are able to see probably for these the default setting allows any logged in user to view the project or plans, you can validate this my going to the permission tab of the projects and the plans.

Other case can be your user id is part of a group which has access to these plans then the above queries won't capture that.

Regards,

Shashank Kumar

[Arun Sankar]

@Shashank Kumar 

I understand that the database is the source of truth and that the Bamboo Dashboard can sometimes be misleading. However, I have granted project and plan permissions to the user, so this information should be stored somewhere. When fetching this data, it should provide all the details, right?

[Shashank Kumar]

Hello Arun,

The details related to access are stored in below 2 tables

* acl_entry

* acl_object_identity

These are then linked to Either Project table or Build table to fetch respective Project or Build plans details.

Can you do the following and check

1. I assume you have provide user arunsa access to the Project and plans in Bamboo separately and not via group access, please confirm if my understanding is wrong

2. If the above case is true you'll be able to see the permissions granted under plan and Project permission settings on the Bamboo GUI for both Project and Plans, can you confirm that.

3. Now can you run this simple query 

select * from acl_entry where sid='arunsa' - This will give you lot of rows, make a note of distinct acl_object_identifty

select * from acl_object_identity where id in () - Pass all the distinct acl_object_identifty here

Validate how many distinct com.atlassian.bamboo.chains.DefaultChain entries are coming under object_id_class

All the object_id_identity is nothing but build_id from build table which tell you how many plans this user has access to. 

So you can reverse Engineer this and check this.

Regards,

Shashank Kumar

[Arun Sankar]

@Shashank Kumar 

1. Yes, I've granted user 'arunsa' access to the project and plans in Bamboo separately.
2. Yes, I can see the permissions granted under both project and plan settings in the Bamboo GUI.
3. Not all projects and plans are returned when executing the query as mentioned. There is a discrepancy between the access shown in the GUI and the results from the query.

Point in case :

Query response:

Here, I am able to see only one com.atlassian.bamboo.chains.DefaultChain, which is "Test" (plans of test) that I created. The remaining two (plans of Project 1 and Project 2) have assigned permissions that are visible in the GUI, but I am unable to fetch them using the query.