Using REST API or MySQL to extract list of roles belonging to users and groups

[Adam Gaudry]

Hi everyone

I am trying to find out if there are any examples of using REST APIs or examples of MySQL queries to discover all of the different roles that a group and/or user has? This would be for the roles a group/user has across all projects and spaces, in JIRA, Confluence, Bamboo and Bitbucket. Clicking on the individual group/user in JIRA, for example, doesn't really tell you anything about their permissions/roles in individual projects.

Apologies, I am new to MySQL and I haven't seen anything quite like this in my searches for more information. If this is very difficult then even a pointer in the right direction would be appreciated.

Best wishes

[davy · Accepted solution]

Hi Adam,

There is a mysql guide to find all the users assigned to a project roles for a project at
- https://confluence.atlassian.com/jirakb/how-to-get-a-list-of-users-assigned-to-project-roles-for-your-project-705954232.html
Which should point you in the right direction.

You could also try the query below, which  does the job on a postgres database on Jira. For mysql  you might need to adjust it a bit

select t1.lower_user_name, t1.email_address, t3.pkey, t3.pname, t3.name as project_role, t3.roletypeparameter 
from (select lower_user_name, email_address from cwd_user) t1 
inner join (select lower_child_name, lower_parent_name from cwd_membership ) t2 on t1.lower_user_name = t2.lower_child_name 
left join (select p.pkey, p.pname, pr.name, pra.roletypeparameter from projectroleactor pra, project p, projectrole pr where pra.pid = p.id and pra.projectroleid = pr.id ) t3 on t1.lower_user_name = t3.roletypeparameter or t2.lower_parent_name = t3.roletypeparameter where t1.email_address = '<email address>' order by email_address, pkey"

Replace <email address> with the user email address

Hope this helps.

Best Regards,

[Adam Gaudry]

Thanks very much Davy!