View the original community article here
Last tested: Nov 27, 2019
Jira has multiple ways in which you can provision users access to see projects and issues. Permission can be provisioned at the user level, group level, project role level, or custom field level. All of this information is stored in multiple tables and include different join relationships depending on which type of permission has been assigned.
The following code can be used in a sql_always_where parameter in explores to limit each user from seeing issues/projects that they do not have access to seeing in JIra.
SQL_ALWAYS_WHERE:
${issue.id} in
( --reporter - issue level; field: issue.assignee
SELECT i.id as issue_id
FROM jira.issue i
INNER JOIN jira.project p on i.project = p.id
INNER JOIN jira.permission_holder ph on p.permission_scheme_id = ph.permission_scheme_id
INNER JOIN jira.user u ON i.reporter = u.id
WHERE ph.permission_id= 'BROWSE_ROJECTS' AND ph.type='reporter' AND u.email = '{{ _user_attributes['email'] }}'
group by 1
UNION
--current assignee - issue level; field: issue.assignee
SELECT i.id as issue_id
FROM jira.issue i
INNER JOIN jira.project p on i.project = p.id
INNER JOIN jira.permission_holder ph on p.permission_scheme_id = ph.permission_scheme_id
INNER JOIN jira.user u ON i.assignee = u.id
WHERE ph.permission_id= 'BROWSE_PROJECTS' AND ph.type='assignee' AND u.email = '{{ _user_attributes['email'] }}'
group by 1
UNION
--user Custom Field: issue_additional_owners - this is at the issue level, anyone who has access to issue has access to see project.
SELECT i.id as issue_id
FROM jira.issue i
INNER JOIN jira.project p on i.project = p.id
INNER JOIN jira.permission_holder ph on p.permission_scheme_id = ph.permission_scheme_id
INNER JOIN jira.issue_additional_owners iao ON i.id = iao.issue_id
INNER JOIN jira.user u ON iao.user_id = u.id
WHERE ph.permission_id= 'BROWSE_PROJECTS' AND ph.type='userCustomField' AND ph.USER_CUSTOM_FIELD_NAME='issue_additional_owners' AND u.email = '{{ _user_attributes['email'] }}'
group by 1
UNION
--user Custom Field: issue_stakeholders_ -this is at the issue level, anyone who has access to issue has access to see project.
SELECT i.id as issue_id
FROM jira.issue i
INNER JOIN jira.project p on i.project = p.id
INNER JOIN jira.permission_holder ph on p.permission_scheme_id = ph.permission_scheme_id
INNER JOIN jira.issue_stakeholders_ ish ON i.id = ish.issue_id
INNER JOIN jira.user u ON ish.user_id = u.id
WHERE ph.permission_id= 'BROWSE_PROJECTS' AND ph.type='userCustomField' AND ph.USER_CUSTOM_FIELD_NAME='issue_stakeholders_' AND u.email = '{{ _user_attributes['email'] }}'
group by 1)
OR
${project.id} in
(
--User level
SELECT p.id as project_id
FROM jira.permission_holder ph
INNER JOIN jira.project p on ph.permission_scheme_id = p.permission_scheme_id
INNER JOIN jira.user u on ph.user_id = u.id
WHERE permission_id='BROWSE_PROJECTS' AND ph.type = 'user' AND u.id = '{{ _user_attributes['email'] }}'
GROUP BY 1
UNION
--Group level
SELECT p.id as project_id
FROM jira.permission_holder ph
INNER JOIN jira.project p on ph.permission_scheme_id = p.permission_scheme_id
INNER JOIN jira.user_group ug on ph.group_name = ug.group_name
INNER JOIN jira.user u on ug.user_id = u.id
WHERE ph.permission_id='BROWSE_PROJECTS' AND ph.type = 'group' AND u.email = '{{ _user_attributes['email'] }}'
GROUP BY 1
UNION
--project_role level
SELECT p.id as project_id
FROM jira.project p
INNER JOIN jira.permission_holder ph on p.permission_scheme_id = ph.permission_scheme_id
INNER JOIN jira.PROJECT_ROLE_ACTOR pra ON p.id = pra.project_id AND ph.project_role_id = pra.project_role_id
INNER JOIN jira.user u on pra.user_id = u.id
WHERE ph.permission_id='BROWSE_PROJECTS' AND ph.type='projectRole' AND u.email = '{{ _user_attributes['email'] }}'
UNION
--project lead level
SELECT p.id as project_id
FROM jira.project p
INNER JOIN jira.permission_holder ph on p.permission_scheme_id = ph.permission_scheme_id
INNER JOIN jira.user u on p.lead_id = u.id
WHERE ph.permission_id= 'BROWSE_PROJECTS' AND ph.type='projectLead' AND u.email = '{{ _user_attributes['email'] }}'
)
This content is subject to limited support.