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

[Alexander]

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

[Nic Brough -Adaptavist-]

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?

[Alexander]

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

[Rilwan Ahmed]

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

[Alexander]

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

[Rilwan Ahmed]

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