This section and all its subsections contain critical information about security, performance, engine constraints, etc that must be read before any Jira administrator or trusted user starts working with the app.

Playing roles and the main workflow

There are three main roles:

Administrators

Your main responsibility as a Jira administrator in regard to this app is selecting the TRUSTED USERS that can WRITE SQL+JQL queries from the built-in console.

You have to configure the Permissions for that.

Trusted Users

IMPORTANT

The app is very powerful, but some features are restricted out-of-the-box and they have to granted by Jira administrators to trusted users only. Please read more below!

Trusted users are granted by the Jra administrators to access the console to write, run and save queries that can be executed by the rest of the Jira users via their query IDs.

They (not necessarily the same set of users) can be granted to remotely access via JDBC. For example, to create BIRT Reports with the report designer or integrate Jira data with third-party Java applications.

There are two main reasons for trusted users:

One is Jira reliability to avoid any user to write and run heavy queries with no control
Second is Jira security to avoid any user to inject XSS code.

XSS code injection

The app supports the injection of JavaScript in Jira pages via SQL output to create small and interactive GUIs with HTML. This is a very powerful feature indeed, but it is also risked because a malicious Jira user could run code on behalf of other users.

SQL is a programming language. You can do a lot of powerful things with this app that will blow your mind. Much of this power comes from the ability to run Javascript in the queries, so trusted users have to escape texts to avoid malicious Jira users to run scripts

It is strongly recommended that trusted users sanitize the Jira users' texts displayed on the app like summaries, descriptions, comments, user properties, etc. by using the built-in functions to transform the original text into strings able to be displayed on HTML page safely from your browser:

escape() : It simple escapes text, so you will see the original content.
htmltext() : extract text from HTML. I.e: "<p>Hello, World</p>" will be transformed into the "Hello, World!" string.
wikiHtml() : renders wiki markup as HTML.

I.e:

select key(issue) as "Issue", htmltext(summary(issue)) as "Summary" from JQL where query = ' reporter = currentUser() '

End users

End-uses are the rest of the Jira users. They cannot write queries from anywhere. They are only able to execute SQL+JQL queries (saved by a trusted user before) via:

built-in gadgets to add the results to Jira Dashboards and Confluence pages,
web data sources from third-party tools like MS Excel and MS Power BI
built-in sql function from the issue Navigator
BIRT reports once they have been uploaded to Jira
shareable URLs (read-only tables)
custom fields
workflow validators & conditions

The SQL+JQL queries have the ability to run in the context of the calling user. This is a core feature and it cannot be bypassed regardless of the way they are invoked. It means that the same query returns a different recordset (output) according to the calling user's permissions on Jira to guarantee data privacy. So trusted users have to care about writing queries grammatically correct only and let the SQL+JQL Driver make the magic. SQL+JQL queries can be run by the rest of the end-users safely with no data leaks.

Next steps:

SQL+JQL is standard SQL essentially. However, some constraints have been implemented in the SQL engine to make it ready for a collaborative environment like Jira where the resources (CPU, RAM, Threads,..) are limited and shared with other third-party apps and Jira itself. Read more about the SQL Engine constraints

SQL+JQL Driver for Jira