Use SQL macros securely - 10.x

Description

When using SQL for Confluence on sites with untrusted users, you may need to employ security measures to control use. This describes some of the techniques for doing this. In some cases, you may want to employ multiple techniques together depending on factors like database being accessed. For instance, macro security can be applied no matter what other technique you want to use. 

TechniqueDescriptionBenefits
Macro Security for ConfluenceContent using SQL macros can only be created or updated by trusted users while still allowing other users the ability to view the content. This is implemented by the Confluence administrator installing Macro Security for Confluence using UPM and configuring access.
  • Enables trusted users to provide important content.
  • Controlled at the user, group, and database level.
Database permissionsDatabase permissions for the user configured for the SQL data source can be restricted, for instance, view only authority. This is recommended when only a subset of access is needed, especially for browse only.
  • Controls what database operations are allowed.
  • Ease concerns for database or security administrator.
Allow only SQL Query macro
(See 10.x or 8.x versions)
Some databases (like PostgreSQL) enforce a JDBC remote access mode for read-only. The SQL Query macro uses this support. This can be implemented by having the Confluence administrator disable the other SQL macros in the UPM.

Restrict access to query only.

Allow only SQL File macro
(See 10.x or 8.x versions)

The SQL File macro only runs Confluence administrator controlled SQL. This can be implemented by having the Confluence administrator disable the other SQL macros in the UPM. 

Only pre-defined SQL can be run.

Role based security
(See 10.x or 8.x versions)
Use database role security to control what data is available.

Data is shown based on user ID and role.

Use parameter markers

Prevent SQL injection attacks by using parameter markers. This is only necessary when the SQL statements are partially constructed from user input - for example, using the Run Self-Service Reports for Confluence. See Wikipedia: SQL injection. Parameter markers are supported by SQL for Confluence.

Prevent SQL injection attacks when users are allowed to provide statement construction input.

Confluence database access

If Confluence database access is defined via an application server based data source, Confluence data can be accessed by the SQL macros using that data source unless other security techniques prevent access. This can be powerful in many circumstances but should be access controlled just like other databases. Direct access to a database circumvents application level security, so should always be considered. Even if you want to provide some level of access to the Confluence database, it is strongly recommended to create a separate data source for this access. Either, duplicate the application server data source definition that Confluence uses and provide a different name (preferred) or create a profile defined data source. To prevent access to the Confluence defined data source, either use Macro Security for Confluence to disallow access or define a profile defined data source with the same name as the application data source and override the values or redirect to some other data source.

Click any of the following links to read more about data source profiles: