How to select Issues from Database based on Permissions

Question

Hi

I am looking to have a possibility to access the Jira Issues straight from Database for data mining but based on the permissions set on projects.

I would like to be sure that during that process the issues with the Private or Special Permissions will not be selects.

Regards,

Areg

Nic Brough -Adaptavist- · Answer

Ugh.  Well, you'll need all the tables with "permissions" in the name.  Then the CWD tables if you're using Jira's internal users for users and their groups.  Jiraissue obviously.  roles, I'm not actually sure about.

https://developer.atlassian.com/display/JIRADEV/Database+Schemashould help you find the rest

Areg Vrtanesyan _Work_ · Answer

I know that it will be easier to do that throught the REST interface but the product that we are using to collect the data from different systems can access to Database only and not external interfaces.

It will be very useful if you can point to the specific tables to look for data as I am already couple of days digging for that with very small effort :(

Nic Brough -Adaptavist- · Answer

That can get very complex - you'll need to duplicate all the processes in Jira that handle the visibility of an issue by user.

For the most simple system, you'll need to look up the user's groups, then their roles in each project, then the permission schemes for each and every project and then you'll at least be able to see if they have "browse" in a project.  From memory, there's 9 tables to be joined and/or queried.

In a more complex system, you'll also need to start reading for the issue security scheme, which could lead you to another 6 or 7 tables.

You will probably find it a LOT easier to do this by using Jira itself, or pulling queries over REST, because a) Jira will do the code for you and b) you'll never have to worry that your interpretation of the rules is different from Jira's