How to query on JIRA DB to get this: user_nameProjectnameProjectKeyuser group jira version: 4.3.2

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?

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

and also user is in role of the project.

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

To get the User details with Project name and Group name

LALIT KHATRI

How to query on JIRA DB to get this:

user_name

Projectname

ProjectKey

user group

jira version: 4.3.2

Accepted answers

All comments

Nic Brough -Adaptavist-

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

users list is in crowd.

Nic Brough -Adaptavist-

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

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira