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.
Code Block language sql GRANT type_of_permission ON database_name.table_name TO 'username'@'localhost’;
Log in as a 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 Refer to this link 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 a 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
...
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:
...
