nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

SQL query for users with ADMINISTER PROJECT privledges

Tony Rice

I'm trying to put together a SQL query which lists all users across all projects with ADMINISTER PROJECT privileges.

Looking at the schemepermission table, it appears to have some use here with perm_type giving some clue about where to go look to map this info back to projects and users

select sp.id, sp.scheme, sp.perm_type, perm_parameter from schemepermissions sp
where PERMISSION_KEY = 'ADMINISTER_PROJECTS'

Example output

ID,SCHEME,PERMISSION,perm_type,perm_parameter,PERMISSION_KEY
10050,10001,23,projectrole,10002,ADMINISTER_PROJECTS
10095,10001,23,lead,,ADMINISTER_PROJECTS
10242,10111,23,group,jira-administrators,ADMINISTER_PROJECTS

Accepted answers

Martin Bayer _MoroSystems_ s_r_o__

Hi @Tony Rice and thank you @Vikrant Yadav for mentioning me , it helped

I read the thread and I just want to sum it all.

If I understand it correctly, you need to get users who has Admin project permission in project X and you are interested mostly in the following

project X
- Administer projects permission
  - project role
    - user
    - group
      - user
  - group
    - user
  - user

Am I correct?

All comments

Vikrant Yadav

Hi @Tony Rice Try below query to get list of users added in Administrator project role for all JIRA projects.

SELECT p.pname, pr.NAME, u.display_name 
FROM projectroleactor pra
INNER JOIN projectrole pr ON pr.ID = pra.PROJECTROLEID
INNER JOIN project p ON p.ID = pra.PID
INNER JOIN app_user au ON au.user_key = pra.ROLETYPEPARAMETER
INNER JOIN cwd_user u ON u.lower_user_name = au.lower_user_name
WHERE name = 'Administrators';

https://confluence.atlassian.com/jirakb/retrieve-a-list-of-users-assigned-to-project-roles-in-jira-server-705954232.html

Hope it works for you!

Thanks,

V.Y

Tony Rice

I think this meets my particular needs.

buuut, to make it a 100% solution....

Aren't project roles and their names user create-able though? This assumes the role with permission to administer projects is called "Administrators". While probably a safe assumption (this is the default isn't it?), any suggestions on incorporating the schemepermissions table, particularly those with the PERMISSION_KEY of ADMINISTER_KEY (which is the immutable piece here)

Vikrant Yadav

Hi @Tony Rice By default, the 'Administer projects' permission is assigned to the 'administrators' group (via the Administrators role) for projects.

JIRA admins never change Administer permission or remove Administrator from this permission.

I think you need to check JIRA Database Schema for this and create a SQL query. PERMISSION_KEY and ADMINISTER_KEY are out of my mind

https://developer.atlassian.com/server/jira/platform/database-schema/

https://confluence.atlassian.com/jirakb/how-to-get-a-list-of-projects-that-has-anonymous-access-in-jira-from-database-794368099.html

https://confluence.atlassian.com/jirakb/how-to-get-a-list-of-permissions-granted-to-groups-assigned-to-project-roles-790630266.html

Thanks

V.Y

Tony Rice

The above SQL is a start, but provides an incomplete story on who has project admin permissions.

If I'm reading the SQL above right and I think I am based on comparing its output to what I'm seeing in the GUI, as well as the schemepermissions table, it shows only those that have been granted that permission through the Administrators role.

The groups, lead, and specific users showing as having project admin privledges in the GUI, don't show up the SQL query described above.

Any suggestions on extending this sql query, or other queries, that can also show those granted this permission through

group
lead
user
assignee
reporter
groupCF
userCF
sd.customer.portal.only
applicationRole

in addition to projectrole

I'm most interested in also seeing who has been granted this important permission as a lead, single user or as a member of a specific group

Martin Bayer _MoroSystems_ s_r_o__

Hi @Tony Rice and thank you @Vikrant Yadav for mentioning me , it helped

I read the thread and I just want to sum it all.

If I understand it correctly, you need to get users who has Admin project permission in project X and you are interested mostly in the following

project X
- Administer projects permission
  - project role
    - user
    - group
      - user
  - group
    - user
  - user

Am I correct?

Tony Rice

I think that sums it up well. The other ways you can grant admin privileges are either trivial (e.g. applicationRole, i.e. any logged in user) or so issue dependent (Group customer field, user custom field, assignee, or reporter that they don't make sense to query in this context.

Vikrant Yadav

@Martin Bayer _MoroSystems_ s_r_o__ Thank to you Martin for joining the thread , when i stuck somewhere, you come up like an angel...hahahha....

Martin Bayer _MoroSystems_ s_r_o__

@Tony Rice I tried to prepare something and tested it a little. You need to replace all the 'XXX' with your project key if you need to collect admin users for particular project:

(SELECT p.pname , cu.lower_user_name 
from project p
join nodeassociation na on na.source_node_id = p.id and p.pkey = 'XXX'
join permissionscheme ps ON na.sink_node_id = ps.id and na.sink_node_entity = 'PermissionScheme'
join schemepermissions s2 on s2.scheme = ps.id and s2.permission_key ='ADMINISTER_PROJECTS' and s2.perm_type ='projectrole'
join projectrole p2 on p2.id = s2.perm_parameter::integer
join projectroleactor p3 on p3.projectroleid = p2.id and pid = p.id and p3.roletype = 'atlassian-group-role-actor'
join cwd_group cg on cg.group_name = p3.roletypeparameter
join cwd_membership cm on (cm.parent_name = cg.group_name and cm.membership_type= 'GROUP_USER')
join cwd_user cu on cu.id = cm.child_id)
union 
(SELECT p.pname, cu.lower_user_name 
from project p
join nodeassociation na on na.source_node_id = p.id and p.pkey = 'XXX'
join permissionscheme ps ON na.sink_node_id = ps.id and na.sink_node_entity = 'PermissionScheme'
join schemepermissions s2 on s2.scheme = ps.id and s2.permission_key ='ADMINISTER_PROJECTS' and s2.perm_type ='projectrole'
join projectrole p2 on p2.id = s2.perm_parameter::integer
join projectroleactor p3 on p3.projectroleid = p2.id and pid = p.id and p3.roletype = 'atlassian-user-role-actor'
join app_user au on au.user_key = p3.roletypeparameter 
join cwd_user cu on cu.lower_user_name = au.lower_user_name )
union
(SELECT p.pname, cu.lower_user_name 
from project p
join nodeassociation na on na.source_node_id = p.id and p.pkey = 'XXX'
join permissionscheme ps ON na.sink_node_id = ps.id and na.sink_node_entity = 'PermissionScheme'
join schemepermissions s2 on s2.scheme = ps.id and s2.permission_key ='ADMINISTER_PROJECTS' and s2.perm_type = 'user'
join cwd_user cu on cu.lower_user_name = s2.perm_parameter)
union
(SELECT p.pname, cu.lower_user_name 
from project p
join nodeassociation na on na.source_node_id = p.id and p.pkey = 'XXX'
join permissionscheme ps ON na.sink_node_id = ps.id and na.sink_node_entity = 'PermissionScheme'
join schemepermissions s2 on s2.scheme = ps.id and s2.permission_key ='ADMINISTER_PROJECTS' and s2.perm_type = 'group'
join cwd_group cg on cg.group_name = s2.perm_parameter 
join cwd_membership cm on (cm.parent_name = cg.group_name and cm.membership_type= 'GROUP_USER')
join cwd_user cu on cu.id = cm.child_id)

or you can use following query to get all the administrators in all projects:

(SELECT p.pname , cu.lower_user_name 
from project p
join nodeassociation na on na.source_node_id = p.id
join permissionscheme ps ON na.sink_node_id = ps.id and na.sink_node_entity = 'PermissionScheme'
join schemepermissions s2 on s2.scheme = ps.id and s2.permission_key ='ADMINISTER_PROJECTS' and s2.perm_type ='projectrole'
join projectrole p2 on p2.id = s2.perm_parameter::integer
join projectroleactor p3 on p3.projectroleid = p2.id and pid = p.id and p3.roletype = 'atlassian-group-role-actor'
join cwd_group cg on cg.group_name = p3.roletypeparameter
join cwd_membership cm on (cm.parent_name = cg.group_name and cm.membership_type= 'GROUP_USER')
join cwd_user cu on cu.id = cm.child_id)
union 
(SELECT p.pname, cu.lower_user_name 
from project p
join nodeassociation na on na.source_node_id = p.id
join permissionscheme ps ON na.sink_node_id = ps.id and na.sink_node_entity = 'PermissionScheme'
join schemepermissions s2 on s2.scheme = ps.id and s2.permission_key ='ADMINISTER_PROJECTS' and s2.perm_type ='projectrole'
join projectrole p2 on p2.id = s2.perm_parameter::integer
join projectroleactor p3 on p3.projectroleid = p2.id and pid = p.id and p3.roletype = 'atlassian-user-role-actor'
join app_user au on au.user_key = p3.roletypeparameter 
join cwd_user cu on cu.lower_user_name = au.lower_user_name )
union
(SELECT p.pname, cu.lower_user_name 
from project p
join nodeassociation na on na.source_node_id = p.id
join permissionscheme ps ON na.sink_node_id = ps.id and na.sink_node_entity = 'PermissionScheme'
join schemepermissions s2 on s2.scheme = ps.id and s2.permission_key ='ADMINISTER_PROJECTS' and s2.perm_type = 'user'
join cwd_user cu on cu.lower_user_name = s2.perm_parameter)
union
(SELECT p.pname, cu.lower_user_name 
from project p
join nodeassociation na on na.source_node_id = p.id
join permissionscheme ps ON na.sink_node_id = ps.id and na.sink_node_entity = 'PermissionScheme'
join schemepermissions s2 on s2.scheme = ps.id and s2.permission_key ='ADMINISTER_PROJECTS' and s2.perm_type = 'group'
join cwd_group cg on cg.group_name = s2.perm_parameter 
join cwd_membership cm on (cm.parent_name = cg.group_name and cm.membership_type= 'GROUP_USER')
join cwd_user cu on cu.id = cm.child_id)

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

jira

jira-software

jira-software-server