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

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.

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