How to List Permissions for Groups and Project Roles in Jira Data Center
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, projects associated with them and the users that belong to each project role.
Environment
Jira Data Center 8.x,9.x,10.x
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