Run SQL queries securely, without page edit restrictions - 11.x

Description

The SQL macros provide powerful capabilities to bring important data into the collaborative environment Confluence offers. However, on sites that have untrusted users, the macros must be restricted using Macro Security for Confluence. This discusses new ways to provide similar capabilities while minimizing the inconveniences introduced by the security controls. 

Two solutions are discussed in this article; one not requiring macro security and the other using Macro Security for Confluence. 

Steps - without macro security

1. Install SQL for Confluence using UPM. App version 5.6 or higher is required.
2. Immediately disable the SQL, SQL Query, SQL Legacy, and SQL Query Legacy macros using standard UPM features. Leave SQL File and SQL File Legacy macros enabled. 

3. Add an example SQL script to the <confluence-home>/script directory on the Confluence server. Only scripts in this directory or subdirectories are accessible.

4. On a page, add the SQL File macro and specify the SQL script file used in step 3.
5. Preview to see the results of the query.

Steps - with macro security

1. Install SQL for Confluence using UPM. App version 5.6 or higher is required.

2. Update the properties file used to load the macro security configuration by adding something like the following:

   sql-file=*ANY
   sql-file.datasource.*=*ANY
   sql-file.file.example.sql=*ANY

3. In UPM, find Macro Security for Confluence and click Configure.
4. Load the updated properties file from step 2.

5. Add an example SQL script to the <confluence-home>/script directory on the Confluence server. Only scripts in this directory or subdirectories are accessible.

6. On a page, add the SQL File macro and specify the SQL script file used in step 5.
7. Preview to see the results of the query. Note that no page edit restrictions are required.