• Community
  • Products
  • Jira
  • Questions
  • How do I query the JIRA DB to get the list of projects a user has a role on, based on individual and group membership?

How do I query the JIRA DB to get the list of projects a user has a role on, based on individual and group membership?

The following query allows me to search all projects on which a user has the "Users" role. But it does only include 'individual' rights. If the user belongs to a group that is in the "Users" role of a project, the project is not returned. Can anyone help me to include this part as well?

Thx

SELECT DISTINCT (

P.pname

)

FROM project P

JOIN projectroleactor PRA ON P.id = PRA.pid

WHERE PRA.roletypeparameter = {currentuser}

AND PRA.projectroleid =10000

order by P.pname asc

3 answers

1 accepted

This widget could not be displayed.

Solved the MySQL query duration issue by indicating the first select is a subquery:

 

SELECT DISTINCT `PID`
FROM `projectroleactor`
WHERE `PROJECTROLEID` =10000
AND `ROLETYPE` LIKE 'atlassian-group-role-actor'
AND `ROLETYPEPARAMETER` IN
(
select * from (SELECT DISTINCT `parent_name` FROM `cwd_membership` WHERE `child_name` LIKE 'username') as subquery
)

This widget could not be displayed.

Hi Gael,

This query should return the same thing as your original query, but will include all members of the group JIRA-users:

SELECT DISTINCT (P.pname)
FROM project P
JOIN projectroleactor PRA ON P.id = PRA.pid
WHERE PRA.roletypeparameter in (select child_name from cwd_membership where parent_name = 'jira-users')
AND PRA.projectroleid =10000
order by P.pname asc

I hope it helps.

Cheers

This widget could not be displayed.

Thanks Tiago, I digged myself into the database scheme and found a similar solution:

SELECT DISTINCT `PID`
FROM `projectroleactor`
WHERE `PROJECTROLEID` =10000
AND `ROLETYPE` LIKE 'atlassian-group-role-actor'
AND `ROLETYPEPARAMETER` IN
(
SELECT DISTINCT `parent_name` FROM `cwd_membership` WHERE `child_name` LIKE 'username'
)

My problem now is that in my MySQL console, the query never returns... It hangs forever, looks like it does not like the nested 'select' statement in the 'where' clause... Just need to find out why...

=>

That's MySQL handling poorly nested select with performance issue. Would need to transform that if possible with a 'join' statement instead.

Regards

Gael

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Tuesday in Jira

What modern development practices are at the heart of how your team delivers software?

Hey Community mates! Claire here from the Software Product Marketing team. We all know software development changes rapidly, and it's often tough to keep up. But from our research, we've found the h...

245 views 1 3
Join discussion

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