How to get a list of users in Jira with OAuth tokens issued for external applications

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問


プラットフォームについて: 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 は除く

要約

Administrators have the ability to integrate Jira with external applications through OAuth, which includes other Atlassian tools through the application links.

When an OAuth token is created, only the user that issued that token can see the list of tokens associated to the account.

There are times the Jira administrator may need to get a report of OAuth tokens issued for external applications for all users in the instance.
This document provides a SQL query to get the list of OAuth tokens from the database.

ソリューション

Run the following SQL queries in the database to get the list of users with an OAuth token issued to external applications.
Because of changes related to GDPR introduced on Jira 8.7, you may need to run both queries below to get a full list.


SQL Query for users before the GDPR change on 8.7

select ot.username as "username"
     , ot.token as "OAuth token"
	 , ot.created as "OAuth token created"
	 , ot.session_last_renewal_time as "OAuth session renewal"
	 , oc.consumername as "OAuth application name"
from oauthsptoken ot
join oauthspconsumer oc on oc.consumer_key=ot.consumer_key
where ot.token_type='ACCESS'
and ot.username not like 'JIRAUSER%'
order by ot.username
;


SQL Query for users after the GDPR change on 8.7

select au.lower_user_name as "username"
     , ot.token as "OAuth token"
	 , ot.created as "OAuth token created"
	 , ot.session_last_renewal_time as "OAuth session renewal"
	 , oc.consumername as "OAuth application name"
from oauthsptoken ot
join oauthspconsumer oc on oc.consumer_key=ot.consumer_key
join app_user au on au.user_key=ot.username
join cwd_user cu on cu.id=au.id
where ot.token_type='ACCESS'
and ot.username like 'JIRAUSER%'
order by au.lower_user_name
;

参考情報

JRASERVER-72198 - 課題詳細を取得中... ステータス

最終更新日 2021 年 5 月 11 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.