This article explains how to create a database profile with read-only access to be used in the sql macros for the app SQL for Confluence (Pro Edition).
Instructions
Follow the below steps:
- Log into the MySQL database.
Run the below query in your database to grant SELECT permission to a specific user.
GRANT type_of_permission ON database_name.table_name TO 'username'@'localhost’;
Refer to the below example:
GRANT SELECT ON Confluence_database.* TO 'confluence_user'@'localhost';
Log in as a Confluence Administrator and click
> General configuration.- From the left side panel, click SQL (Pro Edition) under the BOBSWIFT Configuration section.
- Switch to tab View and modify data source profiles and click +Add profile
- To fill the required details in the below screen, refer this link.
In the fields Username and Password, add the details of the user that you created in the database with read-only access.
- After entering all the details, click Test connection. If the given details are accurate, it displays a message Connection successful as shown in the below screenshot.
- Click Next
- Click Save profile to save this new database profile.
- The new profile is available here.
- Navigate to the required page and click the Edit icon (
). - Add SQL macro and click Edit to open the macro settings.
- Select the read-only user as Data source profile under the SQL statement section and enter the SQL statement to insert data as shown in the below screenshot:
- Click Save settings.
- Click Save.
- It displays the below message as the user has only read-only access to the database.
- Read-only access will be available to retrieve database information.
The output of the above query: