Jira Access SQL Query

[Tony Rossiter]

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

[Andy Heinzer · Accepted solution]

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

[Tony Rossiter]

@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. 

[Tony Rossiter]

@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]

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

[Tony Rossiter]

@Andy Heinzer 

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

[Tony Rossiter]

@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!