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;
