How to get a list of permissions granted to groups and ones assigned to Project Roles.
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
The following queries will give you a list of permission schemes, project associated with them and the users that belong to each project role.
Environment
Jira Server / Data Center 8.x-9.x
Solution
These queries were tested in PostgreSQL, so you may need to tweak it depending on the database you are using.
Project associations with permission schemes:
SELECT p.id as Project_ID, p.pname as Project, p.pkey as Project_key, ps.id as Permission_scheme_ID, ps.name as Permission_scheme
FROM nodeassociation n
JOIN project p on p.id=n.source_node_id
JOIN permissionscheme ps on ps.id=n.sink_node_id
WHERE sink_node_entity = 'PermissionScheme'Permission scheme details:
SELECT ps.id,
ps.name,
s.permission_key,
s.perm_type,
s.perm_parameter,
CASE WHEN s.perm_type='group' AND s.perm_parameter IS NULL THEN 'Anyone on the web'
WHEN s.perm_type='applicationRole' AND (s.perm_parameter IS NULL OR s.perm_parameter='') THEN 'Any logged in user'
WHEN s.perm_type='projectrole' AND s.perm_parameter IS NOT NULL THEN (SELECT name FROM projectrole WHERE id=CAST(s.perm_parameter AS INTEGER))
ELSE s.perm_parameter
END as Permission_Result
FROM schemepermissions s
JOIN permissionscheme ps on ps.id=s.scheme
ORDER BY ps.name,s.permission_key
The query above tries to cover most cases to interpret the permissions meaning to "Permission_Result". If some results are cryptic, we suggest opening the permission scheme in the UI to check it.
Users and groups associated to project roles:
SELECT p.pname as project,
p.pkey,
rr.name as Role,
rr.id as Role_ID,
COALESCE(u.lower_user_name,r.roletypeparameter) as Target,
r.roletype
FROM projectroleactor r
JOIN projectrole rr on rr.id = r.projectroleID
JOIN project p on p.id = r.pid
LEFT JOIN app_user u on r.roletypeparameter=u.user_key
ORDER BY p.pname, rr.NAME, r.ROLETYPEPARAMETER