Jira/Fivetran Custom Permissions

[LookerExperts]

Knowledge Drop

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.