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 Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Thursday in Jira

Meet the AUG leaders of Northern Virginia

@Rachel Wright (Jira Genie), @Billy Poggi (AUG NOVA, DC), and @Dana Jansen (Confluence Queen) are just some of the folks that lead one of the world's most active Atlassian User Group (AUG)....

125 views 4 9
Read article

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