How to get a list of permissions granted to groups assigned to Project Roles.
The following query will give you a list of projects, the roles within that project, the group assigned to that role, and the permissions granted to that role. It only shows the association between group and project permission. If the permission is assigned through the project role or single user name, it won't be returned.
This was written in MySQL syntax, so you may need to tweak it depending on the database you are using.
This was written for JIRA schemas from v6.0 up to v7.x.x
select distinct p.pname, r.ROLETYPEPARAMETER, rr.NAME, s.PERMISSION_KEY from projectroleactor r left outer join projectrole rr on rr.id = r.projectroleID inner join schemepermissions s on s.perm_parameter = r.roletypeparameter inner join project p on p.id = r.pid order by p.pname, rr.NAME, r.ROLETYPEPARAMETER, s.PERMISSION_KEY;
This is a pretty basic query, so you will probably have to alter it a bit to get the exact results you want. For example, you can add some criteria to limit the results by Project (where p.pname = "My Project") or by role (pr.NAME = "Developers").
Here is some explanation of what the columns are returning:
pname= Project Name
NAME = Project Role Name
ROLETYPEPARAMETER = This returns either a username or a group name depending on what is assigned to the Project Role
PERMISSION_KEY = This returns the permission assigned to the user or group in the project role