To get the User details with Project name and Group name

How to query on JIRA DB to get this:

<colgroup> <col width="76"> <col width="86"> <col width="74"> <col width="73"></colgroup>
user_name Projectname ProjectKey user group

jira version: 4.3.2

1 answer

0 votes

There's not enough information here to form a useful query.

First, you've not said where your user lists are held - crowd, ldap, internal etc. That's only a minor issue with a simple answer (it's likely to be cwd_user for the basic list, and then cwd_groups and cwd_membership, but we can't be sure until we know the directory)

The main problem is that you have not told us how you are defining the link between a project and user. Just saying "user is in project" is not good enough. You need a rule like "can view issues" or "is in the role of user" or "can comment on issues" - or combinations of them.

All of the rules you could define are complex, and often have many routes through your data. So there's no answer for your question until you define the relationship between project and user.

users list is in crowd.

Ok. So you'll need to execute the queries to get the users and groups on the three CWD tables I already mentioned. They're quite simple to join - read cwd_membership for lines where it mentions group, join it to cwd_user for more user info, and cwd_group for the group name.

What about the relationship between user and project?

what do you mean by relationship between user and project?

yes, user can create/view/modify issues ?

but i want just the name of the user and which project he is part of

I mean exactly what I said before - you need to define a precise set of rules that explains "user is part of this project"


Just wondering what you really meany by "which project he is part of". Do you mean to say all the projects which the user has permission to view? (this is what Nic is asking). Once that is clear, query the permissions for a project, figure out the roles and groups that permission and then get the users who are part of it.

and also user is in role of the project.

Yes, all the projects which user has the permission to view and to create/modify issues.

Ok, so, you've got quite a complex chunk of SQL to construct. I'm not a DBA, and I don't really know how to do it, but the pseudo-code would be:

  • Read the project to find out what the permission scheme is
  • Read the permission scheme to get all the rules for "browse project"
  • For each rule you find in there, work out the current users who match (For example, for assignee and reporter, read jiraissue, for group, read cwd_membership and cwd_group, for custom fields, read customfield and customfieldvalue, cwd_membership and cwd_group, and so-on)
  • Read the permission scheme to get all the rules for "create issue", repeat the rule-expansion above
  • Read the permission scheme to get all the rules for "edit issue"

... and so-on

Generally, this is not something I'd try to do in SQL, it's complex and changeable. The reason I kept asking you for rules is to establish that you need to be very very clear about exactly what you mean, so that I could show you how complex this really it.

You will find it far easier to simply look at Jira.

It would be worth reviewing why you're asking for this in SQL - it probably is not the best way to get the information you are looking for. Could you explain why you are asking?

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Jan 08, 2019 in Jira

How to Jira for designers

I’m a designer on the Jira team. For a long time, I’ve fielded questions from other designers about how they should be using Jira Software with their design team. I’ve also heard feedback from other ...

1,097 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