Page Comparison

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

Instructions

...

1. Log into the MySQL your database instance.

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

   Code Block
   language sql
   GRANT type_of_permission ON database_name.table_name TO 'username'@'localhost’;

   Refer to the below example:

   Code Block
   language sql
   GRANT SELECT ON Confluence_database.* TO 'confluence_user'@'localhost';

   
   

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

4. From the left side panelsidebar, click SQL (Pro Edition) under the BOBSWIFT Configuration section under the BOBSWIFT Configuration section.
5. Switch Navigate to tab the View and modify data source profilesand tab and click +Add profile.
   Image RemovedTo fill the required details in the below screen, refer this link.
    In the fields Username and Password, add the details of Image Added
6. Enter the Username and Password details for the user that you created in the database with readthe read-only access in the previous steps.
   Image RemovedAfter entering all the details, click Image Added
   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, it displays a message "Connection successful as shown in the below screenshot.
   Image Removed" message is shown as follows:
   Image Added
8. Click NextClick  and then Save profile to save this new database profile.
   Image Removed The new profile is available hereunder the View and modify data source profiles tab.
   Image RemovedImage Added
9. Navigate to the required Confluence page and click the Edit Image Addedicon (Image Removed).
10. Add SQL macro Enter {sql or search for the SQL for Confluence macro, and click Edit to open the macro settings.
    Image RemovedImage Added
    
11. Select the read-only user as Data source profileuser profile created in previous steps under the SQL statement section and enter the SQL statement section, and enter a query to insert data as shown in the below screenshotfollowing example:
    Image Modified
    
12. Click Save settings.Click , and then, Save. It displays the below message as the

As the user has only read-only access to the database

...

, the following error message is displayed:
Image Added

Read-only access

...

users can only retrieve database information.

...

In the same example, replace the INSERT query above with a SELECT query as shown:
Image Added
The output of the above query is shown as:

...

Image Added