How to create a database profile with read-only access using the SQL for Confluence app

This article explains how to create a database profile with read-only access using the SQL for Confluence (Pro Edition) app.

Instructions

1. Log into your database instance.

2. Run the given query in your database to grant the SELECT permission to a specific user. This ensures that the user now has read-only access.

   GRANT type_of_permission ON database_name.table_name TO 'username'@'localhost’;

3. Log in as a Confluence Administrator and click  > General configuration.

4. From the left sidebar, click SQL (Pro Edition) under the BOBSWIFT Configuration section.
5. Navigate to the View and modify data source profiles tab and click Add profile.
   
6. Enter the Username and Password details for the user that you created with the read-only access in the previous steps.
   
   Fill other details on the screen. Refer to this link for more examples about creating profiles.
7. Click Test connection. If the given details are accurate, a "Connection successful" message is shown as follows:
   
8. Click Next and then Save profile to save this new database profile. The new profile is available under the View and modify data source profiles tab.
   
9. Navigate to the required Confluence page and click the icon.
10. Enter {sql or search for the SQL for Confluence macro, and click Edit to open the macro settings.
    
    
11. Select the read-only user profile created in previous steps under the SQL statement section, and enter a query to insert data as in the following example:
    
    
12. Click Save settings, and then, Save.

As the user has only read-only access to the database, the following error message is displayed:

Read-only access users can only retrieve database information. In the same example, replace the INSERT query above with a SELECT query as shown:

The output of the above query is shown as: