I was wondering how to query the database to get a listing of users by project and role

I need to get a listing of all the JIRA users we have with what projects they have access to and what permissions the users have in each project.  it would probably be easier to list project by project and then the list of users in each project with their role/permission.

I am not sure what tables/columns to use in order to get this list, can anyone help with this?

2 answers

It's not that easy as this data is stored in many tables. An example: A users full name and unique ID is associated in users_cwd, this ID is associated to groups, which might be associated to roles in projects, which in terms is associated to permission schemes. To unnest all of this into something readable will demands very elaborate queries. I have done very tiny parts of this for specific needs, but don't have anything fullblown that would just solve your question. On top of this mysql table structure changes based on version and added add-ons so if anyone else wants to help you with this, they will need a quite a lot of information. I renamed users once using a mysql hack but basically had to reverse engineer the whole database to see how it correlates together. Once more this was with JIRA 3.12.x, making it worthless now even doe some tables remain unchanged.

1 vote

Hi Mike,

    You could use REST API for that. You could make a call like this: https://<jira-host>/rest/api/2/user/permission/search?username&permissions=BROWSE&projectKey=PROJ&startAt=0&maxResults=1000

    This is the REST API documentation: https://docs.atlassian.com/jira/REST/latest/

    You can use multiple permissions in one call if you want.

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,345 views 14 20
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot