This article explains how to create a database profile with read_-only access for app using the SQL for Confluence (Pro Edition) app.
Instructions
Follow the below steps:
- Log into the MySQL your database instance.
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';
Log in as a Confluence Administrator and
clickclick
> General> General configuration.
- From the left side panelsidebar, click SQL (Pro Edition) under the BOBSWIFT Configuration section under the BOBSWIFT Configuration section.
- Switch Navigate to tab the View and modify data source profilesand click the button + tab and click Add profile.
To fill the required details in the below screen, refer to this link.
In the fields Username and Password, add the details of - Enter the Username and Password details for the user that you created in the database with readthe read-only access in the previous steps.
After entering all the details, click
Fill other details on the screen. Refer to this link for more examples about creating profiles. - Click Test connection. If the given details are accurate, it displays a message "Connection successful as shown in the below screenshot.
" message is shown as follows: - Click NextClick and then Save profile to save this new database profile.
Verify that the The new profile is available hereunder the View and modify data source profiles tab. - Navigate to the required Confluence page and click the Edit icon ().
- Add SQL macro Enter {sql or search for the SQL for Confluence macro, and click Edit to open the macro settings.
- 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:
- 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:
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: