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

Question

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?

Daniel Santos_157bm4 · Answer

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.

Jonas Andersson · Answer

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.