方案：如何列出 Jira 中授予用户的所有权限

概述

为了进行故障排除或某些管理任务，我们可能想知道给定用户拥有的所有权限。

Jira 通过其 UI 提供权限助手和类似工具，但对于所有权限的列表，我们只能通过作为用户本身进行身份验证的 REST API 请求或通过数据库来获取它。

解决方案

此处提供的两个解决方案都包含嵌套组（假设在实例中配置了支持嵌套组）

REST API

当前用户本身，或者通过 Switch User 类似功能，模拟用户。然后在浏览器中打开此 URL

https://Jira-base-URL/rest/api/2/mypermissions

数据库

POSTGRES、MYSQL 和 MSSQL

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
),
permissions AS 
(
select * from uperm
UNION
select * from gperm
UNION
select * from globalperm
UNION
select * from projrole
UNION
select * from approle
)
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;

ORACLE

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
),
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
)
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;

我们可以根据需要更改第 4 行的用户名，并过滤生成的项目（在从底部开始的第二行）。

输出样例

Project Key |              Permission              |  Type   |       Source        
-------------+--------------------------------------+---------+---------------------
 S1          | ADD_COMMENTS                         | License | jira-software-users
 S1          | ASSIGNABLE_USER                      | License | jira-software-users
 S1          | ASSIGNABLE_USER                      | User    | charlie
 S1          | ASSIGN_ISSUES                        | License | jira-software-users
 S1          | BROWSE_PROJECTS                      | License | jira-software-users
 S1          | BROWSE_PROJECTS                      | Group   | group-c
 S1          | CLOSE_ISSUES                         | License | jira-software-users