SQL Query for getting all projects where a user group has no access

Question

Hi,

i am still new in Jira and its database data structure and my SQL-knowledge is also more a beginners or it has already got rusty;

i need a sql query which gives me a list of all projects, excluding archived projects, where a certain user group (e.g. 'testgroup') has no access;

help would be appreciated;

thanks,

Alex

Alexander_y1bv2d · Answer

@Nic Brough -Adaptavist- thanks, for your answer, you are right; i forgot to mention that;

with "access" i meant the permission "Browse Projects" and only this permission;

Nic Brough -Adaptavist- · Answer

What, exactly, do you mean by "access"?  Is it just "Can read the project", or are you interested in other things (comments, edits, can progress through the workflow, etc)

Even "can see it" is not a simple SQL query.  You can't answer the question without the context of the permission scheme.

If you're looking for browse access only, then the pseudocode you will need to think through is:

* For each project* Read the permission scheme* Look at the "browse project" permission rules.  You're only interested in any line that says "Group = " or "Role ="
* For any rule that says Group=, you have an answer
* For any rule that says Role=* Read the project roles for the project, looking for the use of any group in there

* When you've cycled through all the projects, you've got a list of groups in use.  Subtract that from the complete list of groups you have

If you're interested in other actions, or you actually need to be looking at users, this becomes a lot more complex...

I think it might be better to question the requirement - what are you trying to do with this search for unused groups?  A bit of housekeeping?

Rilwan Ahmed · Answer

Yes, they have.
Groups can be in notification schemes, permission schemes, global permissions, dashboards, filters etc.

All the related SQLs  to find out where the groups are used is mentioned in https://confluence.atlassian.com/jirakb/how-to-identify-group-usage-in-jira-441221524.html