SQL for list of users JIRA project

[avinash bhatt]

Hi

Please help....can i get a sql for to retrieve list of users in a JIRA project

[Pablo Beltran]

The SQL for JIRA plugin can answers to this question in an straightforward way:

The query below shows all the actors (users playing a role) in a project

SELECT * FROM PROJECTROLEACTORUSERS

Therefore, you might want to run:

SELECT * FROM PROJECTROLEACTORUSERS WHERE ROLENAME='jira-developers'

Furthermore, SQL for JIRA is fully integrated with JIRA security and permissions, so each users would see his roles only. Of course, JIRA administrators would see the roles for everybody on each project.

[avinash bhatt]

why - that is an interesting question.  Our current instance of JIRA is used by few departments and as we are planning to move over to cloud, we have been asked by folks who control the money, to identify user, project so that we could get funding from all stake holders

[Nic Brough -Adaptavist-]

Depends on your user directory, but there's no easy SQL to get it even in the internal directory.

First, what do you define as a "user of a project"?  Someone who can see it?  Or someone who can create issues in it?  Or they can be assigned issues?  And so-on.

Then you need to read your permission scheme to find out the rules set to determine that "is a user".  For example, if your rule is "can create issues, or can be assigned issues", then you need the rules set for "Create" and "Assignable user".

Those rules could be quite simple - "group X" or "user Y", but it's quite likely that they are more flexible than that, so they'll say "role Z" or "reporter" or or or or...

So you then need to interpret those rules to get back to a set of users.  If it's "group X", then you can simply read cwd_membership for the user ids in the group.  But if it's "role X", then you'll need to read the project role for the users in that role and the groups, then go into the groups to get the users.

In short, you have a big task in front of you, and we can't even begin to help you with raw SQL until you have a hard rule for "is a user in a project"

[avinash bhatt]

Thanks for your response Nic

Any user who has permissions on the project (as Admin or anyother) is how i would classify a user.

[Nic Brough -Adaptavist-]

Ok, that's good because it's clear, but potentially complex because you'd need to read the entire permission scheme for every rule. I'd dodge that and just look at the "browse" permission in the scheme if you need a simple view - it's technically wrong, but it's rare that you'd have a user who can do things in a project but not actually see the issue! So, you'll need to read schemepermission to get the list of groups, roles, reporter, assignee, customfield etc for "Browse project", then read the appropriate tables for each of those - reporter and assignee are on jiraissue, the groups are in cwd_membership, the roles are in projectrole, join that to projectroleactor to get the lists of groups and users in the roles etc. Maybe it's easier to ask *why* you're trying to do this? And maybe why you're choosing the hard way?