To get the User details with Project name and Group name

LALIT KHATRI June 25, 2014

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
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 25, 2014

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.

LALIT KHATRI June 25, 2014

users list is in crowd.

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 25, 2014

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?

LALIT KHATRI June 25, 2014

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

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 25, 2014

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

Renjith Pillai
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 25, 2014

Lalit,

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.

LALIT KHATRI June 26, 2014

and also user is in role of the project.

LALIT KHATRI June 26, 2014

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

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 26, 2014

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