Where are all JIRA accounts stored in database?

I'm trying to pull all Jira accounts from database. Where can I find exactly the same account list as through the User Management in Jira?

Distinct from cwd_user shows twice as much user accounts as User Management.

Distinct from external_entities shows ~30% more user accounts than User Management.

1 answer

1 accepted

1 vote
Accepted answer

It's cwd_user.

I suspect your "distinct" might be confusing things, although you have not told us what you're doing with it - users can be duplicated across directories with the same name, which could well explain what you're seeing.

As an example, I set up a client's JIRA recently.  I'm nic in the internal directory.  I'm nic in their main site's internal directory, I'm nic-s in another site's directory and I'm nic in the Adaptavist directory.  Doing a "distinct" based on the login id will give you 2 answers, which is wrong.

 

Nic, I have used distinct exactly to avoid getting many records for users that come from multiple user directories :

select distinct lower_user_name
from cwd_user

Still the query shows many accounts that are not visible in User Management.

 

OK, I have found what's wrong. cwd_directory shows 7 directories. Those are the same directories that I can see in Jira in User Management. However when I selected:

select distinct directory_id
from cwd_user

I got 8 directories. It seems that on the beginning of the instance someone created a user directory and removed it right away. When I excluded this directory_id from search I got exactly the same number of users as in User Management in Jira.

That makes sense - although the "distinct" is wrong because it's hiding user accounts, the users JIRA sees are masked - in my case, the Adaptavist nic is the one of the three that is used because that directory is higher than the others.

An extra directory would explain your results.

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Monday in Confluence

Organizing your space just got easier - Page Tree Drag & Drop is here

Hi Community! I’m Elaine, Confluence Product Manager. You may have read my earlier post about page tree in space navigation sidebar. I'm excited to share another improvement that helps you organize ...

151 views 3 4
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you