How to bulk restrict Filters and Dashboards shared with anyone on the web or logged-in users in Jira
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Fisheye および Crucible は除く
本記事で説明している手順は、現時点でのものとなります。そのため、一部のお客様で特定の状況下で動作したという報告がありますが、正式にサポートされているわけではなく、お客様の特定のシナリオで動作することを保証するものではありません。
本番環境での実施の前に一通り非本番環境で検証し、成功しなかった場合にはサポートされている代替案にフォール バックしてください。
データベースの変更を行う場合は必ず事前にバックアップを取得してください。可能な場合は、まずステージング サーバーで SQL コマンドの変更、挿入、更新、または削除を行うようにします。
要約
If Jira is or was ever configured to have anonymous access, Filters and Dashboards may have been shared by their creators with "anyone on the web", meaning unauthenticated users may have access to it (specially if they have the exact Filter or Dashboard URL).
Even if Projects Permission Schemes restrict the Browse Project Permission, unauthenticated users won't be able to see any Issue data but would still be able to read the Filters JQL query — and that may have some information Admins didn't mean to disclose.
This article offers a Database manipulation alternative to bulk restrict Filter and Dashboards, though:
- It requires a rolling restart after the DB commands
- It requires a Full Reindex after the restarts
- It also works for restricting shares with "Any logged-in user"
環境
Any version of Jira Software or Jira Service Management, Data Center or Server.
ソリューション
1. Preventing new shares
You may want to disable the "Share with anyone on the web" option through the Admin UI: Control anonymous user access.
Also, if you want to disable the "share with logged-in users" and hide the option to share by default on User profiles, you may try the workarounds described in these two issues:
JRASERVER-65962 - 課題詳細を取得中... ステータス
<!-- Custom CSS to hide the "Logged in" option when sharing Filters and Dashboards (JRASERVER-65962) -->
<style>
#share_type_selector_viewers > option[value="loggedin"] {
display: none;
}
</style>JRASERVER-69095 - 課題詳細を取得中... ステータス
<!-- Custom CSS to hide the "Shared" option on Users profile (JRASERVER-69095) -->
<style>
#update-user-preferences #update-user-preferences-sharing option:nth-child(1) {
display: none;
}
</style>Please heed to the disclaimers on How to customize Jira with JavaScript and CSS so you know the caveats of implementing such customizations, though.
2. Restricting already existing shares
The solution consists of:
- Identifying the Filters and Dashboards we need to update the permissions
- Remove or replace the "anyone on the web" or "logged-in users" permissions
- Advised approach: ask each filter and dashboard creator to fix it themselves
- Less advised: fix the Filters permissions through REST API requests (doesn't work for Dashboards)
- Last alternative: fix permissions through DB deletions and updates
Database legend:
| Jira 内 | In the Database |
|---|---|
| フィルター | SearchRequest |
| ダッシュボード | PortalPage |
| Web 上のすべてのユーザー | グローバル |
| Logged-in users | loggedin |
2.1. Identifying the Filters and Dashboards
The query below can be used to list all Filters and Dashboards shared with either "anyone on the web" or "logged-in users":
select
spglobal.id as "Global permission Id",
spedit.id as "Edit permission Id",
spedit.sharetype as "Edit Share Type",
case
when spedit.sharetype = 'user' then auser.lower_user_name
when spedit.sharetype = 'group' then spedit.param1
when spedit.sharetype = 'project' then concat(p.pkey, concat(' (', concat(prole.name, ')')))
end as "Editor User or Group or Project (and Role)",
case
when spglobal.entitytype = 'SearchRequest' then 'Filter'
when spglobal.entitytype = 'PortalPage' then 'Dashboard'
end as "Entity Type",
case
when spglobal.entitytype = 'SearchRequest' then f.id
when spglobal.entitytype = 'PortalPage' then d.id
end as "Entity Id",
case
when spglobal.entitytype = 'SearchRequest' then f.filtername
when spglobal.entitytype = 'PortalPage' then d.pagename
end as "Entity Name",
creator.lower_user_name as "Entity Creator"
from sharepermissions spglobal
left join sharepermissions spedit on spedit.entityid = spglobal.entityid and spedit.entitytype = spglobal.entitytype and spedit.rights = 3
left join portalpage d on d.id = spglobal.entityid and spglobal.entitytype = 'PortalPage'
left join searchrequest f on f.id = spglobal.entityid and spglobal.entitytype = 'SearchRequest'
left join app_user auser on spedit.sharetype = 'user' and auser.user_key = spedit.param1
left join project p on spedit.sharetype = 'project' and concat(p.id, '') = spedit.param1
left join projectrole prole on spedit.sharetype = 'project' and concat(prole.id, '') = spedit.param2
left join app_user creator on creator.user_key = f.username or creator.user_key = d.username
where spglobal.sharetype in ('global', 'loggedin')
order by "Entity Type", "Entity Id", spglobal.id, spedit.id;You can change line 31 to match only the global or loggedin permissions as you want.
2.2.a. Fix permissions through the UI
With the output from the query above (section 1) you can reach out to the Entity Creators and have them edit their respective Filters and Dashboards.
You can continuously run the same query to monitor the progress of the restrictions — the number of Filters and Dashboards should reduce as they're restricted.
2.2.b. REST API
The REST API approach allows the Admin to fix the permissions of Filters without relying on each Filter creator or owner to it themselves.
For Dashboards, it's still a manual process through the UI.
This DB query will output the Filters and the respective DELETE and POST requests you need to call:
select distinct
spdel.id as "Permission Id",
spdel.entitytype as "Entity Type",
spdel.entityid as "Entity Id",
f.filtername as "Entity Name",
spdel.sharetype as "Share Type",
creator.lower_user_name as "Entity Creator",
concat('https://JIRA-BASE-URL', concat('/rest/api/2/filter/', concat(spdel.entityid, concat('/permission/', spdel.id)))) as "DELETE Request",
case when spupd.id is null
then concat('https://JIRA-BASE-URL/rest/api/2/filter/', concat(spdel.entityid, '/permission'))
end
as "POST Request",
case when spupd.id is null
then '{"type": "group", "groupname": "jira-software-users", "view": true, "edit": false}'
end
as "POST Data"
from
sharepermissions spdel
left join sharepermissions spupd
on spupd.entitytype = spdel.entitytype
and spupd.entityid = spdel.entityid
and spupd.rights = 3
and spupd.sharetype = 'group' and spupd.param1 = 'jira-software-users'
left join searchrequest f on f.id = spdel.entityid and spdel.entitytype = 'SearchRequest'
left join app_user creator on creator.user_key = f.username
where
spdel.sharetype in ('global', 'loggedin')
and spdel.entitytype = 'SearchRequest'
order by "DELETE Request" desc;Changes you need to make on the query:
- Permissions to change from: you should change line 27 if you only want to limit the global or loggedin permissions.
- Permissions to add: we're replacing both the global and loggedin by the group jira-software-users. You should change the group name everywhere in the query if you want a different one.
- Jira base URL: Replace JIRA-BASE-URL by the instance's URL on lines 8 and 10
The output should look something like this (truncated for better readability):
P... | E... | E... | E... | S... | C... | DELETE Request | POST Request | POST Data
--------+------+-------+------+----------+------+-----------------------------------------------------------------+----------------------------------------------------------+------------------------------------------------------------------------------------
289540 | *** | 96347 | *** | loggedin | *** | https://JIRA-BASE-URL/rest/api/2/filter/96347/permission/289540 | https://JIRA-BASE-URL/rest/api/2/filter/96347/permission | {"type": "group", "groupname": "jira-software-users", "view": true, "edit": false}
158473 | *** | 96330 | *** | loggedin | *** | https://JIRA-BASE-URL/rest/api/2/filter/96330/permission/158473 | |
(2 rows)Notice how for the Filter #96347 we have both DELETE and POST but for Filter #96330 only the DELETE. This is because filter #96330 is already shared with jira-software-users with editor permission, so we don't need the redundant view permission for them.
With this output you may now run all the DELETE requests followed by all POST requests. The Post data is the same for all of them, just the URL changes.
You may find this article on How to send bulk API requests using Postman useful for this.
2.2.c. Database manipulation
This is the least advised approach but some Admins may accept the tradeoff given the urgency to restrict visibility of too many Filters or Dashboards.
You should backup your database (or at least the sharepermissions table) before proceeding further. You'll need to restore the table if the procedure doesn't work.
Also, you should dry-run this on a non-Prod instance before executing in Production. Refer to the disclaimers at the top of the article.
This query will give you the UPDATE or DELETE statements you should run to fix the permissions
select distinct
sp.id as "Permission Id",
sp.entitytype as "Entity Type",
sp.entityid as "Entity Id",
case
when sp.entitytype = 'SearchRequest' then f.filtername
when sp.entitytype = 'PortalPage' then d.pagename
end
as "Entity Name",
sp.sharetype as "Share Type",
creator.lower_user_name as "Entity Creator",
case when spex.id is null
then concat('delete from sharepermissions where id = ', concat(sp.id, concat(' and sharetype = ''', concat(sp.sharetype, concat(''' and entityid = ', concat(sp.entityid, ';'))))))
else concat('update sharepermissions set sharetype = ''group'', param1 = ''jira-software-users'', param2 = null where id = ', concat(sp.id, concat(' and entityid = ', concat(sp.entityid, concat(' and sharetype = ''', concat(sp.sharetype, ''';'))))))
end
as "Database statement"
from
sharepermissions sp
left join sharepermissions spex
on spex.entitytype = sp.entitytype
and spex.entityid = sp.entityid
and spex.rights = 3
and spex.sharetype = 'group' and spex.param1 = 'jira-software-users'
left join portalpage d on d.id = sp.entityid and sp.entitytype = 'PortalPage' and d.pagename <> 'System Dashboard'
left join searchrequest f on f.id = sp.entityid and sp.entitytype = 'SearchRequest'
left join app_user creator on creator.user_key = f.username or creator.user_key = d.username
where
sp.sharetype in ('global', 'loggedin')
and (f.filtername is not null OR d.pagename is not null)
order by "Database statement" desc;Changes you need to make on the query:
- Permissions to change from: you should change line 28 if you only want to limit the global or loggedin permissions.
- Permissions to add: we're replacing both the global and loggedin by the group jira-software-users. You should change the group name everywhere in the query if you want a different one.
出力サンプル
update sharepermissions set sharetype = 'group', param1 = 'jira-software-users', param2 = null where id = 333575 and entityid = 158243 and sharetype = 'loggedin';
delete from sharepermissions where id = 99967 and sharetype = 'loggedin' and entityid = 61903;The Filters and Dashboards that aren't shared with the group already (probably as editors), will have just an UPDATE: changing the wide share to just the group.
The Filters and Dashboards that already shared to the group (probably as editors), will have a DELETE: removing the wide view permission.
This is because the Editor permission also bundles the view permission implicitly — we don't need a editor and viewer permission to the same group — Jira will even prompt you about this redundancy and keep the Editors only.
Related articles and issues
- Jira: 全員と共有されているすべてのフィルターの一覧を取得する
- Anonymous users able to see shared filters, dashboards, or project issues in Jira server
- How to control anonymous user access in a public Jira instance