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

[Martin Bayer _MoroSystems_ s_r_o__ · Accepted solution]

Hi @Tony Rice and thank you @Vikrant Yadav for mentioning me :D, 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)

[Tony Rice]

Yes!  Thank you!  This is the complete answer.

It enables us to fully audit who has been granted this important permission and manage that access appropriately

[Vikrant Yadav]

😎 Glad to hear it works for you. 🥳🥳

kindly Accept @Martin Bayer _MoroSystems_ s_r_o__  solution…he is the real hero 🦸 .. and provided the right solution. 

Thanks

Vikrant Yadav

[Martin Bayer _MoroSystems_ s_r_o__]

Yeah, thank you @Vikrant Yadav I'm hero because I prepared SQL query, but you're hero too because you shared someone else's problem with me... so kudos to you too :)

[Marco Franzen]

Hi, I needed the same SQL query only for the Permission Assignable User. The SQL query here helped me a lot, thanks :)

But I noticed two problems with it:
Nested groups are not resolved
example:

User Developer 1 is member of group Development Team A
User Developer 2 is member of group Development Department
The group Development Team A is also member of group Development Department. As the group Development Team A is member of group Development Department, all members of Development Team A (Developer 1) are indirectly also members of Development Department.

• Group: Development Department
  • User: Developer 2
  • Group: Development Team A
    • User Developer 1

• project X
  • Administer projects permission
    • Group: Development Department

With the current SQL Query only Developer 2 would be part of the results list.
I ended up using some recursion the get all of the nested groups.

Inactive users / inactive user directories

Inactive users or users from inactive user directories have no longer access to Jira.

For permissions like "administer project" you might want to have them included, to see who gets the permission in case the account is reactivated, but in my use case (assignable users) i don't want to have them included.

This is what my SQL query looks like:

DECLARE @groups TABLE(name nvarchar(255))

DECLARE @projectKey AS nvarchar(255)
SET @projectKey = 'PROJECT_KEY'

DECLARE @permissionKey AS nvarchar(255)
SET @permissionKey = 'Permission_KEY'

INSERT INTO @groups
-- groups from the project roles
SELECT cg.lower_group_name
from jiraschema_new.project p
join jiraschema_new.nodeassociation na on na.source_node_id = p.id and p.pkey = @projectKey
join jiraschema_new.permissionscheme ps ON na.sink_node_id = ps.id and na.sink_node_entity = 'PermissionScheme'
join jiraschema_new.schemepermissions s2 on s2.scheme = ps.id and s2.permission_key = @permissionKey and s2.perm_type ='projectrole'
join jiraschema_new.projectrole p2 on p2.id = s2.perm_parameter
join jiraschema_new.projectroleactor p3 on p3.projectroleid = p2.id and pid = p.id and p3.roletype = 'atlassian-group-role-actor'
join jiraschema_new.cwd_group cg on cg.group_name = p3.roletypeparameter
join jiraschema_new.cwd_directory cd on cd.ID = cg.directory_id
WHERE cd.active = 1
union
-- groups from the permission scheme
SELECT cg.lower_group_name
from jiraschema_new.project p
join jiraschema_new.nodeassociation na on na.source_node_id = p.id and p.pkey = @projectKey
join jiraschema_new.permissionscheme ps ON na.sink_node_id = ps.id and na.sink_node_entity = 'PermissionScheme'
join jiraschema_new.schemepermissions s2 on s2.scheme = ps.id and s2.permission_key = @permissionKey and s2.perm_type = 'group'
join jiraschema_new.cwd_group cg on cg.group_name = s2.perm_parameter
join jiraschema_new.cwd_directory cd on cd.ID = cg.directory_id
WHERE cd.active = 1;

-- recursively get all nested groups
WITH CHILDGROUP AS (
SELECT DISTINCT lower_child_name AS childname
FROM jiraschema_new.cwd_group cg
join jiraschema_new.cwd_membership cm on (cm.parent_name = cg.group_name and cm.membership_type= 'GROUP_GROUP')
join jiraschema_new.cwd_directory cd on cd.ID = cg.directory_id
WHERE cg.active = 1 AND cd.active = 1 AND cm.lower_parent_name IN (SELECT name FROM @groups)
UNION ALL
SELECT lower_child_name
FROM CHILDGROUP, jiraschema_new.cwd_group cg
join jiraschema_new.cwd_membership cm on (cm.parent_name = cg.group_name and cm.membership_type= 'GROUP_GROUP')
join jiraschema_new.cwd_directory cd on cd.ID = cg.directory_id
WHERE cg.active = 1 AND cd.active = 1 AND cm.lower_parent_name = CHILDGROUP.childname)

INSERT INTO @groups
SELECT DISTINCT childname FROM CHILDGROUP

-- users from the groups
SELECT DISTINCT lower_user_name, display_name
FROM jiraschema_new.cwd_group cg
join jiraschema_new.cwd_membership cm on (cm.parent_name = cg.group_name and cm.membership_type= 'GROUP_USER')
join jiraschema_new.cwd_user cu on cu.id = cm.child_id
join jiraschema_new.cwd_directory cd on cd.ID = cu.directory_id
WHERE lower_group_name IN (SELECT * FROM @groups) AND cd.active = 1 AND cg.active = 1 AND cu.active = 1
union
-- users from the project roles
SELECT cu.lower_user_name, cu.display_name
from jiraschema_new.project p
join jiraschema_new.nodeassociation na on na.source_node_id = p.id and p.pkey = @projectKey
join jiraschema_new.permissionscheme ps ON na.sink_node_id = ps.id and na.sink_node_entity = 'PermissionScheme'
join jiraschema_new.schemepermissions s2 on s2.scheme = ps.id and s2.permission_key = @permissionKey and s2.perm_type ='projectrole'
join jiraschema_new.projectrole p2 on p2.id = s2.perm_parameter
join jiraschema_new.projectroleactor p3 on p3.projectroleid = p2.id and pid = p.id and p3.roletype = 'atlassian-user-role-actor'
join jiraschema_new.app_user au on au.user_key = p3.roletypeparameter
join jiraschema_new.cwd_user cu on cu.lower_user_name = au.lower_user_name
join jiraschema_new.cwd_directory cd on cd.ID = cu.directory_id
WHERE cd.active = 1 AND cu.active = 1
union
-- users from the permission scheme
SELECT cu.lower_user_name, cu.display_name
from jiraschema_new.project p
join jiraschema_new.nodeassociation na on na.source_node_id = p.id and p.pkey = @projectKey
join jiraschema_new.permissionscheme ps ON na.sink_node_id = ps.id and na.sink_node_entity = 'PermissionScheme'
join jiraschema_new.schemepermissions s2 on s2.scheme = ps.id and s2.permission_key = @permissionKey and s2.perm_type = 'user'
join jiraschema_new.cwd_user cu on cu.lower_user_name = s2.perm_parameter
join jiraschema_new.cwd_directory cd on cd.ID = cu.directory_id
WHERE cd.active = 1 AND cu.active = 1

[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 

1. group
2. lead
3. user
4. assignee
5. reporter
6. groupCF
7. userCF
8. sd.customer.portal.only
9. 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

[Vikrant Yadav]

Hi @Tony Rice  You can check this post ,it might help you :- https://community.atlassian.com/t5/Jira-Core-Server-questions/MySQL-query-to-list-all-project-roles-based-on-a-project/qaq-p/1137182

@Martin Bayer _MoroSystems_ s_r_o__ Kindly help us on SQL query.

Thanks

V.Y  

[Tony Rice]

Doesn't this SQL query focus on project roles? While a major way to grant privileges of all kinds, it's just one of the ways listed above.