This article explains how to create a database profile with read-only access using the SQL for Confluence (Pro Edition) app.
Instructions
- Log into your database instance.
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’;
Log in as Confluence Administrator and click
> General configuration.- From the left sidebar, click SQL (Pro Edition) under the BOBSWIFT Configuration section.
- Navigate to the View and modify data source profiles tab and click Add profile.
- 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. For more examples about creating profiles, refer to this link. - Click Test connection. If the given details are accurate, a "Connection successful" message is shown as follows:
- 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.
- Navigate to the required Confluence page and click the
icon. - Enter {sql or search for the SQL for Confluence macro, and click Edit to open the macro settings.
- Select the read-only user profile created in previous steps under the SQL statement section, and enter the query to insert data as in the following example:
- 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 will be available to retrieve database information.
The output of the above query: