It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Jira Access SQL Query

I work for a SOX compliant company and we perform quarterly entitlement and access reviews, including our core instance of Jira Server which, unfortunately for us, has been deemed a "SOX-Supporting" system.  We also used LDAP integration for external user management and to grant application access to Jira via AD group.

For some years now, I've been using a series of SQL queries to pull the data I need to prove out that access to the system is appropriate at all levels: application access, AD group membership, project permission scheme settings, and project role membership. 

It turns out that one of my queries that was written to pull project role data has been pulling historical records with termed employees that no longer have access to the system.  Here's the SQL we've been using: 

-- Below Query pulls all project roles assigned to projects
select pname, ROLETYPEPARAMETER, ROLETYPE, NAME, [RWT_JIRA_PROD].[dbo].[projectrole].DESCRIPTION
from [RWT_JIRA_PROD].[dbo].[projectroleactor]
INNER join [RWT_JIRA_PROD].[dbo].[projectrole] on (projectrole.id=projectroleactor.projectroleid)
INNER join [RWT_JIRA_PROD].[dbo].[project] on (project.id=projectroleactor.pid)  

I've got a DBA looking into the "false positives" and he came back with a question for the community about how to join 2 tables: projectroleactor and cwd_user.  Here's the SQL he's trying but can't find a join point for: 

SELECT pname, ROLETYPEPARAMETER, ROLETYPE, NAME, [RWT_JIRA_PROD].[dbo].[projectrole].DESCRIPTION
from [RWT_JIRA_PROD].[dbo].[projectroleactor]
INNER join [RWT_JIRA_PROD].[dbo].[projectrole] on (projectrole.id=projectroleactor.projectroleid)
INNER join [RWT_JIRA_PROD].[dbo].[project] on (project.id=projectroleactor.pid)
INNER JOIN [RWT_JIRA_PROD].[dbo].cwd_user cu ON ROLETYPEPARAMETER = cu.user_name

Any ideas on how to fix the query?

Bonus question: Anybody know of an add-on that will do this sort of access reporting for us?  We're not using crowd or any other user management tool at this point beyond LDAP integration but I think I could sell one of these if they meet our reporting needs.  We spend WAY too much time on these quarterly reviews

1 answer

1 accepted

1 vote
Answer accepted
Andy_Heinzer Atlassian Team Apr 02, 2020

Hi Tony,

I see that you are digging into the SQL underbelly of data Jira has collected and you appear to have run into a problem in the process.

I can see the problem here.  It stems from the way Jira handles user accounts renames.  Technically you don't want to try to match the cwd_user.user_name up against the other field in projectrole.projectroleactor.  But I can understand why this worked in older versions.

Instead there is another table you should use here, namely app_user.  The app_user table only has two values: lower_user_name and user_key.  When an account is first created in Jira, that user_key value is set and never expected to change, even if the account itself in Jira is renamed (which is possible since Jira 6.0). 

Historically, Jira has always chosen the first username of that account to be the user_key value.  However starting with Jira 8.3 we made a change in user_key values when accounts are created the first time.  This was done to try to make Jira Server more GDPR compliant.  More details in GDPR changes in Jira Server 8.3.

Try changing your 2nd SQL query to this:

SELECT pname, ROLETYPEPARAMETER, ROLETYPE, NAME, [RWT_JIRA_PROD].[dbo].[projectrole].DESCRIPTION, [RWT_JIRA_PROD].[dbo].[app_user].lower_user_name
from [RWT_JIRA_PROD].[dbo].[projectroleactor]
INNER join [RWT_JIRA_PROD].[dbo].[projectrole] on (projectrole.id=projectroleactor.projectroleid)
INNER join [RWT_JIRA_PROD].[dbo].[project] on (project.id=projectroleactor.pid)
INNER JOIN [RWT_JIRA_PROD].[dbo].app_user au ON ROLETYPEPARAMETER = au.user_key

 

This will match up the projectroleactor value to the user_key value in the app_user table, and it also adds an additional returned column of app_user.lower_user_name so that you can at least see the current username of that account being returned here.  Since accounts in Jira can be renamed, and because of the recent GDPR changes, the user_key is not always going to match the current username of that account in Jira.

I hope this helps to explain the behavior and how to work past it.

As for other possible solutions here, I am afraid I don't know of any currently for making Jira Server SOX compliant.  But I'd be interested to see if perhaps someone else might have something add to this thread on the topic.

Let me know if you have any follow-up concerns here about the SQL.

Andy

@Andy_Heinzer 

Thanks!  I appreciate the detail in your reply and, believe me, if I didn't have to dig into the SQL I definitely wouldn't. 

We're trying your fix now and will let you know if I hit any snags. 

@Andy_Heinzer 

Thanks again for your help.  The app_user table doesn't appear to have an active user flag, which we need.  Is there a way to use this table with one of the others to determine which are the active users?

Thanks!

Andy_Heinzer Atlassian Team Apr 03, 2020

Correct the app_user table won't have that info.  But this is something we can lookup by joining a few other tables to the query. We can look at Get list of licensed users in Jira server for some example SQL queries related to that aspect.

Perhaps try this:

SELECT pname, ROLETYPEPARAMETER, ROLETYPE, NAME, [RWT_JIRA_PROD].[dbo].[projectrole].DESCRIPTION, [RWT_JIRA_PROD].[dbo].[app_user].lower_user_name
from [RWT_JIRA_PROD].[dbo].[projectroleactor]
INNER JOIN [RWT_JIRA_PROD].[dbo].[projectrole] on (projectrole.id=projectroleactor.projectroleid)
INNER JOIN [RWT_JIRA_PROD].[dbo].[project] on (project.id=projectroleactor.pid)
INNER JOIN [RWT_JIRA_PROD].[dbo].app_user au ON ROLETYPEPARAMETER = au.user_key
INNER JOIN [RWT_JIRA_PROD].[dbo].cwd_user cu ON cu.lower_user_name = au.lower_user_name
INNER JOIN [RWT_JIRA_PROD].[dbo].cwd_membership cm ON cu.id = cm.child_id AND cu.directory_id = cm.directory_id
INNER JOIN [RWT_JIRA_PROD].[dbo].cwd_directory cd ON cm.directory_id = cd.id
WHERE cd.active = '1' AND cu.active = '1';

This adds 3 additional tables in order to determine which accounts are actually active.  When determining active accounts, you have to look both at the cwd_user.active field AND the cwd_directory.active field as both are required for a user to be able to login to Jira.

Let me know how it goes.

Andy

@Andy_Heinzer 

Thanks for hanging in there with me.  We're trying this today, will let you know how it goes.

Like Andy_Heinzer likes this

@Andy_Heinzer 

This worked!  It returned some duplicate records so I applied DISTINCT to the select statement.  We also had to remove the Description field as it is still in nText format but this was not a big loss.  

Thanks again for your help!

Like Andy_Heinzer likes this

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
VERSION
8.3.3
TAGS
Community showcase
Posted in Jira Core

How to manage many similar workflows?

I have multiple projects that use variations of the same base workflow. The variations depend on the requirements of the project or issue type. The variations mostly come in the form of new statuses ...

2,709 views 11 5
Join discussion

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you