Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,297,440
Community Members
 
Community Events
165
Community Groups

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

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

2 answers

0 votes

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?

@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;

Hi @Alexander ,

Try this SQL if you have small number of projects. This SQL gives the list of project where the group is used. You can then list of remaining projects.

This was used for PostgreSQL databases and may need slight syntax adjustments depending on the DBMS Jira's database is based on.

SELECT
pra.roletypeparameter AS "Group",
pr.name AS "Project Role",
p.pname AS "Project"
FROM
projectroleactor pra
LEFT JOIN projectrole pr ON pra.projectroleid = pr.id
LEFT JOIN project p ON pra.pid = p.id
WHERE
pra.roletype = 'atlassian-group-role-actor'
AND pra.roletypeparameter in ('helpdesk', 'administrators');

Note: Replace ('helpdesk', 'administrators') with a comma-separated list of the groups you want to check for usages.

More details in https://confluence.atlassian.com/jirakb/how-to-identify-group-usage-in-jira-441221524.html

@Rilwan Ahmed thanks for your help, but aren´t there also permission schemes where user groups have access on projects?

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

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you