---
title: "How to use SQL macros securely"
canonical: "https://support.appfire.com/space/SUPPORT/89137721/How%20to%20use%20SQL%20macros%20securely"
format: markdown
---
## <span style="color: #000000">Description</span>

<span style="color: #000000">When using </span>[<span style="color: #000000">SQL for Confluence</span>](https://bobswift.atlassian.net/wiki/spaces/SQL)<span style="color: #000000"> 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. </span>

| <span style="color: #000000">**Technique**</span> | <span style="color: #000000">**Description**</span> | <span style="color: #000000">**Benefits**</span> |
| --- | --- | --- |
| [Macro Security for Confluence](https://appfire.atlassian.net/wiki/spaces/CMSP/overview) | <span style="color: #000000">Content 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</span> [Macro Security for Confluence](https://appfire.atlassian.net/wiki/spaces/CMSP) <span style="color: #000000">using UPM and configuring access.</span> | - <span style="color: #000000">Enables trusted users to provide important content.</span>
- <span style="color: #000000">Controlled at the user, group, and database level.</span> |
| <span style="color: #000000">Database permissions</span> | <span style="color: #000000">Database 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.</span> | - <span style="color: #000000">Controls what database operations are allowed.</span>
- <span style="color: #000000">Ease concerns for database or security administrator.</span> |
| <span style="color: #000000">Allow only</span> *<span style="color: #000000">SQL Query</span>*<span style="color: #000000"> macro</span>  
(See [10.x](https://appfire.atlassian.net/wiki/spaces/SQL/pages/74321526/SQL-query+-+10.x) or [8.x](https://appfire.atlassian.net/wiki/spaces/SQL/pages/74320969/SQL-query+Macro+-+8.x) versions) | <span style="color: #000000">Some databases (like PostgreSQL) enforce a JDBC remote access mode for read-only. The </span>*<span style="color: #000000">SQL Query</span>*<span style="color: #000000"> macro uses this support. This can be implemented by having the Confluence administrator disable the other </span>*<span style="color: #000000">SQL</span>*<span style="color: #000000"> macros in the UPM.</span> | <span style="color: #000000">Restrict access to query only.</span> |
| <span style="color: #000000">Allow only</span> *<span style="color: #000000">SQL File</span>*<span style="color: #000000"> macro</span>  
(See [10.x](https://appfire.atlassian.net/wiki/spaces/SQL/pages/74321371/SQL-file+-+10.x) or [8.x](https://appfire.atlassian.net/wiki/spaces/SQL/pages/74320997/SQL-file+Macro+-+8.x) versions) | <span style="color: #000000">The </span>*<span style="color: #000000">SQL File</span>*<span style="color: #000000"> macro only runs Confluence administrator controlled SQL. This can be implemented by having the Confluence administrator disable the other </span>*<span style="color: #000000">SQL</span>*<span style="color: #000000"> macros in the UPM. </span><br>> ℹ️ <span style="color: #000000">See</span> [Run SQL queries securely, without page edit restrictions](https://appfire.atlassian.net/wiki/spaces/SQL/pages/74321741/How+to+securely+run+SQL+queries+without+page+edit+restrictions+-+10.x) <span style="color: #000000">for 10.x version or </span>[<span style="color: #000000">How to securely run SQL queries without page edit restrictions</span>](https://bobswift.atlassian.net/wiki/spaces/SUPPORTS/pages/976356114/How+to+securely+run+SQL+queries+without+page+edit+restrictions)<span style="color: #000000"> for 8.x version.</span> | <span style="color: #000000">Only pre-defined SQL can be run.</span> |
| <span style="color: #000000">Role based security</span>  
(See [10.x](https://bobswift.atlassian.net/wiki/spaces/SUPPORTS/pages/976323365/How+to+implement+role+based+security+for+SQL+queries+-+10.x) or [8.x ](https://bobswift.atlassian.net/wiki/spaces/SUPPORTS/pages/976356086/How+to+implement+role+based+security+for+SQL+queries)versions) | <span style="color: #000000">Use database role security to control what data is available.</span> | <span style="color: #000000">Data is shown based on user ID and role.</span> |
| <span style="color: #000000">Use parameter markers</span> | <span style="color: #000000">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</span> [Run Self-Service Reports for Confluence](https://appfire.atlassian.net/wiki/spaces/RUN)<span style="color: #000000">. See</span> [Wikipedia: SQL injection](http://en.wikipedia.org/wiki/SQL_injection)<span style="color: #000000">. Parameter markers are supported by </span>*<span style="color: #000000">SQL for Confluence</span>*<span style="color: #000000">.</span> | <span style="color: #000000">Prevent SQL injection attacks when users are allowed to provide statement construction input.</span> |

> ✅ **Confluence database access**
> ✅ 
> ✅ <span style="color: #000000">If Confluence database access is defined via an application server based data source, Confluence data can be accessed by the </span>*<span style="color: #000000">SQL</span>*<span style="color: #000000"> 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 </span>**<span style="color: #000000">strongly recommended</span>**<span style="color: #000000"> 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</span> [profile defined data source](https://appfire.atlassian.net/wiki/spaces/SQL/pages/74321547/Configure+Data+Source+Profiles+-+10.x)<span style="color: #000000">. To prevent access to the Confluence defined data source, either use</span> [Macro Security for Confluence](https://appfire.atlassian.net/wiki/spaces/CMSP) <span style="color: #000000">to disallow access or define a</span> [profile defined data source](https://appfire.atlassian.net/wiki/spaces/SQL/pages/74321547/Configure+Data+Source+Profiles+-+10.x) <span style="color: #000000">with the same name as the application data source and override the values or redirect to some other data source.</span>

> ✅ <span style="color: #000000">Click any of the following links to read more about data source profiles:</span>
> ✅ 
> ✅ - [10.x version](https://appfire.atlassian.net/wiki/spaces/SQL/pages/74321547/Configure+Data+Source+Profiles+-+10.x)
> ✅ - [8.x version](https://appfire.atlassian.net/wiki/x/6xNuB)