How to list all permissions granted to a user in Jira
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
For troubleshooting or some administration tasks, we may want to know all the permissions a given user has.
Jira offers permission helpers and similar tools through it's UI, but for a list of all permissions, we can only get it through a REST API request authenticated as the users themselves or through the Database.
Related articles
Solution
Both the solutions presented here include Nested Groups, if you have configured them in your instance.
REST API
Authenticating as the user you want to check the permissions of, open this URL in the browser or call it through some REST API client of your choice:
1
https://Jira-base-URL/rest/api/2/mypermissionsDatabase
We can also query Jira's Database to list all permissions of a given user — charlie on these examples.
Please note that this query matches the most common permission configurations only—it doesn't match all possible configurations, especially on the Issue Security Level.
For Postgres and MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
WITH RECURSIVE nested AS
(
select m.* from cwd_membership m where m.membership_type = 'GROUP_USER'
and m.lower_child_name = 'charlie'
UNION ALL
select m.* from cwd_membership m
join nested on m.lower_child_name = nested.lower_parent_name
where m.membership_type = 'GROUP_GROUP'
),
uperm AS
(
select distinct 'User' as "Type", sp.permission_key as "Permission", p.pkey as "Project_Key", u.lower_user_name as "Source"
from nested n
join cwd_user u on u.lower_user_name = n.lower_child_name
join app_user a on a.lower_user_name = u.lower_user_name
join schemepermissions sp on sp.perm_type = 'user' and sp.perm_parameter = a.user_key
join permissionscheme s on s.id = sp.scheme
join nodeassociation na on na.sink_node_id = s.id and na.sink_node_entity = 'PermissionScheme'
join project p on p.id = na.source_node_id
where n.membership_type = 'GROUP_USER'
),
gperm AS
(
select distinct 'Group' as "Type", sp.permission_key as "Permission", p.pkey as "Project_Key", sp.perm_parameter as "Source"
from nested n
join schemepermissions sp on sp.perm_type = 'group' and sp.perm_parameter = n.lower_parent_name
join permissionscheme s on s.id = sp.scheme
join nodeassociation na on na.sink_node_id = s.id and na.sink_node_entity = 'PermissionScheme'
join project p on p.id = na.source_node_id
),
projrole AS
(
select distinct 'Role' as "Type", sp.permission_key as "Permission", p.pkey as "Project_Key", concat('Role "', concat(pr.name, concat('": ', pra.roletypeparameter))) as "Source"
from nested n
join projectroleactor pra
on ((pra.roletype = 'atlassian-group-role-actor' and lower(pra.roletypeparameter) = n.lower_parent_name)
or (pra.roletype = 'atlassian-user-role-actor' and lower(pra.roletypeparameter) = n.lower_child_name))
join projectrole pr on pr.id = pra.projectroleid
join schemepermissions sp on sp.perm_type = 'projectrole' and sp.perm_parameter = concat(pr.id, '')
join project p on p.id = pra.pid
),
approle AS
(
select distinct 'License' as "Type", sp.permission_key as "Permission", p.pkey as "Project_Key", l.group_id as "Source"
from nested n
join licenserolesgroup l on lower(l.group_id) = n.lower_parent_name
join schemepermissions sp on sp.perm_type = 'applicationRole'
join permissionscheme s on s.id = sp.scheme
join nodeassociation na on na.sink_node_id = s.id and na.sink_node_entity = 'PermissionScheme'
join project p on p.id = na.source_node_id
),
globalperm AS
(
select distinct 'Global' as "Type", gp.permission as "Permission", null as "Project_Key", null as "Source"
from globalpermissionentry gp
join nested on gp.group_id = nested.lower_parent_name
),
securitylevel AS
(
select distinct 'Security Level' as "Type", concat(sisl.name, concat(' (from ', concat(iss.name, ')'))) as "Permission", p.pkey as "Project_Key", case when (sis.sec_type = 'user') then au.lower_user_name else sis.sec_parameter end as "Source"
from nested n
left join app_user au on au.lower_user_name = n.lower_child_name
join schemeissuesecurities sis on (sis.sec_parameter = n.lower_parent_name or sis.sec_parameter = au.user_key)
join issuesecurityscheme iss on iss.id = sis.security
join schemeissuesecuritylevels sisl on sisl.scheme = sis.id
join nodeassociation na on na.sink_node_entity = 'IssueSecurityScheme'
join project p on p.id = na.source_node_id
),
permissions AS
(
select * from uperm
UNION
select * from gperm
UNION
select * from globalperm
UNION
select * from projrole
UNION
select * from approle
UNION
select * from securitylevel
)
select Project_Key, Permission, Type, Source from permissions
-- where ("Project Key" in ('S1', 'S2', 'S3') or "Project Key" is null)
order by Project_Key asc, Permission asc;For Microsoft SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
WITH nested AS
(
SELECT m.*
FROM cwd_membership m
WHERE m.membership_type = 'GROUP_USER'
AND m.lower_child_name = 'charlie'
UNION ALL
SELECT m.*
FROM cwd_membership m
JOIN nested ON m.lower_child_name = nested.lower_parent_name
WHERE m.membership_type = 'GROUP_GROUP'
),
uperm AS
(
SELECT DISTINCT 'User' AS "Type", sp.permission_key AS "Permission", p.pkey AS "Project_Key", u.lower_user_name AS "Source"
FROM nested n
JOIN cwd_user u ON u.lower_user_name = n.lower_child_name
JOIN app_user a ON a.lower_user_name = u.lower_user_name
JOIN schemepermissions sp ON sp.perm_type = 'user' AND sp.perm_parameter = a.user_key
JOIN permissionscheme s ON s.id = sp.scheme
JOIN nodeassociation na ON na.sink_node_id = s.id AND na.sink_node_entity = 'PermissionScheme'
JOIN project p ON p.id = na.source_node_id
WHERE n.membership_type = 'GROUP_USER'
),
gperm AS
(
SELECT DISTINCT 'Group' AS "Type", sp.permission_key AS "Permission", p.pkey AS "Project_Key", sp.perm_parameter AS "Source"
FROM nested n
JOIN schemepermissions sp ON sp.perm_type = 'group' AND sp.perm_parameter = n.lower_parent_name
JOIN permissionscheme s ON s.id = sp.scheme
JOIN nodeassociation na ON na.sink_node_id = s.id AND na.sink_node_entity = 'PermissionScheme'
JOIN project p ON p.id = na.source_node_id
),
projrole AS
(
SELECT DISTINCT 'Role' AS "Type", sp.permission_key AS "Permission", p.pkey AS "Project_Key", CONCAT('Role "', CONCAT(pr.name, CONCAT('": ', pra.roletypeparameter))) AS "Source"
FROM nested n
JOIN projectroleactor pra
ON ((pra.roletype = 'atlassian-group-role-actor' AND LOWER(pra.roletypeparameter) = n.lower_parent_name)
OR (pra.roletype = 'atlassian-user-role-actor' AND LOWER(pra.roletypeparameter) = n.lower_child_name))
JOIN projectrole pr ON pr.id = pra.projectroleid
JOIN schemepermissions sp ON sp.perm_type = 'projectrole' AND sp.perm_parameter = CONCAT(pr.id, '')
JOIN project p ON p.id = pra.pid
),
approle AS
(
SELECT DISTINCT 'License' AS "Type", sp.permission_key AS "Permission", p.pkey AS "Project_Key", l.group_id AS "Source"
FROM nested n
JOIN licenserolesgroup l ON LOWER(l.group_id) = n.lower_parent_name
JOIN schemepermissions sp ON sp.perm_type = 'applicationRole'
JOIN permissionscheme s ON s.id = sp.scheme
JOIN nodeassociation na ON na.sink_node_id = s.id AND na.sink_node_entity = 'PermissionScheme'
JOIN project p ON p.id = na.source_node_id
),
globalperm AS
(
SELECT DISTINCT 'Global' AS "Type", gp.permission AS "Permission", NULL AS "Project_Key", NULL AS "Source"
FROM globalpermissionentry gp
JOIN nested ON gp.group_id = nested.lower_parent_name
),
securitylevel AS
(
SELECT DISTINCT 'Security Level' AS "Type", CONCAT(sisl.name, CONCAT(' (from ', CONCAT(iss.name, ')'))) AS "Permission", p.pkey AS "Project_Key", CASE WHEN (sis.sec_type = 'user') THEN au.lower_user_name ELSE sis.sec_parameter END AS "Source"
FROM nested n
LEFT JOIN app_user au ON au.lower_user_name = n.lower_child_name
JOIN schemeissuesecurities sis ON (sis.sec_parameter = n.lower_parent_name OR sis.sec_parameter = au.user_key)
JOIN issuesecurityscheme iss ON iss.id = sis.security
JOIN schemeissuesecuritylevels sisl ON sisl.scheme = sis.id
JOIN nodeassociation na ON na.sink_node_entity = 'IssueSecurityScheme'
JOIN project p ON p.id = na.source_node_id
),
permissions AS
(
SELECT * FROM uperm
UNION
SELECT * FROM gperm
UNION
SELECT * FROM globalperm
UNION
SELECT * FROM projrole
UNION
SELECT * FROM approle
UNION
SELECT * FROM securitylevel
)
SELECT Project_Key, Permission, Type, Source FROM permissions
ORDER BY Project_Key ASC, Permission ASC;For Oracle
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
WITH nested AS
(
SELECT m.* FROM cwd_membership m
START WITH m.membership_type = 'GROUP_USER' AND m.lower_child_name = 'charlie'
CONNECT BY PRIOR m.lower_parent_name = m.lower_child_name AND m.membership_type = 'GROUP_GROUP'
),
uperm AS
(
select distinct 'User' as "Type", sp.permission_key as "Permission", p.pkey as "Project_Key", u.lower_user_name as "Source"
from nested n
join cwd_user u on u.lower_user_name = n.lower_child_name
join app_user a on a.lower_user_name = u.lower_user_name
join schemepermissions sp on sp.perm_type = 'user' and sp.perm_parameter = a.user_key
join permissionscheme s on s.id = sp.scheme
join nodeassociation na on na.sink_node_id = s.id and na.sink_node_entity = 'PermissionScheme'
join project p on p.id = na.source_node_id
where n.membership_type = 'GROUP_USER'
),
gperm AS
(
select distinct 'Group' as "Type", sp.permission_key as "Permission", p.pkey as "Project_Key", sp.perm_parameter as "Source"
from nested n
join schemepermissions sp on sp.perm_type = 'group' and sp.perm_parameter = n.lower_parent_name
join permissionscheme s on s.id = sp.scheme
join nodeassociation na on na.sink_node_id = s.id and na.sink_node_entity = 'PermissionScheme'
join project p on p.id = na.source_node_id
),
projrole AS
(
select distinct 'Role' as "Type", sp.permission_key as "Permission", p.pkey as "Project_Key", concat('Role "', concat(pr.name, concat('": ', pra.roletypeparameter))) as "Source"
from nested n
join projectroleactor pra
on ((pra.roletype = 'atlassian-group-role-actor' and lower(pra.roletypeparameter) = n.lower_parent_name)
or (pra.roletype = 'atlassian-user-role-actor' and lower(pra.roletypeparameter) = n.lower_child_name))
join projectrole pr on pr.id = pra.projectroleid
join schemepermissions sp on sp.perm_type = 'projectrole' and sp.perm_parameter = concat(pr.id, '')
join project p on p.id = pra.pid
),
approle AS
(
select distinct 'License' as "Type", sp.permission_key as "Permission", p.pkey as "Project_Key", l.group_id as "Source"
from nested n
join licenserolesgroup l on lower(l.group_id) = n.lower_parent_name
join schemepermissions sp on sp.perm_type = 'applicationRole'
join permissionscheme s on s.id = sp.scheme
join nodeassociation na on na.sink_node_id = s.id and na.sink_node_entity = 'PermissionScheme'
join project p on p.id = na.source_node_id
),
securitylevel AS
(
select distinct 'Security Level' as "Type", concat(sisl.name, concat(' (from ', concat(iss.name, ')'))) as "Permission", p.pkey as "Project_Key", case when (sis.sec_type = 'user') then au.lower_user_name else sis.sec_parameter end as "Source"
from nested n
left join app_user au on au.lower_user_name = n.lower_child_name
join schemeissuesecurities sis on (sis.sec_parameter = n.lower_parent_name or sis.sec_parameter = au.user_key)
join issuesecurityscheme iss on iss.id = sis.security
join schemeissuesecuritylevels sisl on sisl.scheme = sis.id
join nodeassociation na on na.sink_node_entity = 'IssueSecurityScheme'
join project p on p.id = na.source_node_id
),
globalperm AS
(
select distinct 'Global' as "Type", gp.permission as "Permission", null as "Project_Key", null as "Source"
from globalpermissionentry gp
join nested on gp.group_id = nested.lower_parent_name
),
permissions AS
(
select * from uperm
UNION
select * from gperm
UNION
select * from globalperm
UNION
select * from projrole
UNION
select * from approle
UNION
select * from securitylevel
)
select Project_Key, Permission, Type, Source from permissions
-- where ("Project Key" in ('S1', 'S2', 'S3') or "Project Key" is null)
order by Project_Key asc, Permission asc;We can change the username on lines #4 as needed, and filter the resulting Projects, too (on the second line from the bottom).
Updated on March 19, 2025
Was this helpful?
Still need help?
The Atlassian Community is here for you.