HowTo: Get Advanced Roadmaps (formerly Portfolio) Plan and Program permissions information
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
Summary
When using Jira and Advanced Roadmaps (formerly Portfolio), it is generally useful for administrators, to have a view of all the permissions of each user for each Plan or Program through the database.
Environment
Applies to Jira Server installations with Advanced Roadmaps (formerly Portfolio).
Solution
Get Program owner's name
SELECT au.lower_user_name "Owner",
prog."ID" "Program_Id",
prog."TITLE" "Program_Title"
FROM "AO_D9132D_PROGRAM" AS prog
JOIN app_user AS au ON prog."OWNER"=au.user_key;
Get Plan owner's name
SELECT au.lower_user_name "Owner",
plan."ID" "Plan_Id",
plan."TITLE" "Plan_Title"
FROM "AO_D9132D_PLAN" AS plan
JOIN app_user AS au ON plan."CREATOR_ID"=au.user_key;
Get Program permissions information
SELECT au.lower_user_name "Username",
prog."TITLE" "Program_Title",
perm."HOLDER_TYPE",
perm."ID" "Permission_Id",
perm."PERMISSION" "Permission_Type",
perm."PROGRAM_ID" "Program_Id"
FROM "AO_D9132D_PERMISSIONS" AS perm
JOIN app_user AS au ON perm."HOLDER_KEY"=au.user_key
JOIN "AO_D9132D_PROGRAM" AS prog ON perm."PROGRAM_ID"=prog."ID";
Get Plan permissions information
SELECT au.lower_user_name "Username",
plan."TITLE" "Plan_Title",
perm."HOLDER_TYPE",
perm."ID" "Permission_Id",
perm."PERMISSION" "Permission_Type",
perm."PLAN_ID" "Plan_Id"
FROM "AO_D9132D_PERMISSIONS" AS perm
JOIN app_user AS au ON perm."HOLDER_KEY"=au.user_key
JOIN "AO_D9132D_PLAN" AS plan ON perm."PLAN_ID"=plan."ID";
Last modified on Mar 16, 2021
Powered by Confluence and Scroll Viewport.