How to get a list of users in Jira with OAuth tokens issued for external applications
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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.
*Except Fisheye and Crucible
Summary
Administrators have the ability to integrate Jira with external applications through OAuth, which includes other Atlassian tools through the Link to other applications.
When an OAuth token is created, only the user that issued that token can Allowing OAuth access 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.
Solution
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: Anonymizing users, you may need to run both queries below to get a full list.
SQL Query for users before the GDPR change on 8.7
PostgreSQL
1
2
3
4
5
6
7
8
9
10
11
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
PostgreSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
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
;See Also
Was this helpful?