vault.jpgThis post is seventh (and final) in a series on using Confluence as a Business Intelligence tool.
In this series we’ve explained how to connect Confluence to a database to produce useful and dynamic reports for your business. When you do this, you’re giving your users new capabilities and access to data that they might not have had before. Before you start, you need to be aware of the security implications, consider their impact on your organization, and take steps to address the risks you discover.
There are several methods for restricting access to information.
Macro Security Plugin
The Macro Security Plugin adds security controls to other plugins and macros, including the {SQL}, {beanshell}, {csv} and {excel} macros.
In addition to standard Confluence security controls that determine who can view particular pages, the Macro Security Plugin can also:

  • Control users/groups that can edit pages that contain the {SQL} macro (preventing unauthorized users from adding new queries)
  • Restrict users/group that can access each datasource (which is a link to a Database)

It’s easy to configure once you get your head around how the plugin works. For each macro or datasource, you specify a list of users and groups who are able to access the macro or datasource. Any pages that use the macro or datasource in question must have edit permissions that match your specified list or the macro will not be allowed to execute.
For example, let’s say you wanted to give only the group “DBAccess” the ability to use to the {sql} macro. Once configured, the Macro Security Plugin would only allow {sql} to execute on pages where Edit permission was granted to the “DBAccess” group only. Non-members of DBAccess could still view the page normally. If Edit permissions were to “DBAccess” and other groups or users, the macro would not be allowed to execute.
You can think of it as the Macro Security plugin expanding Confluence’s existing permissions. Please have a look at the Macro Security Plugin’s documentation for more.
Datasource Security
Confluence connects to a database via a datasource, which describes where to find the database and includes a username and password for connecting to the database. When Confluence connects to a database, it uses the supplied username and password for the connection, rather than the username of the current Confluence user. This means that all Confluence users share the same database access.
To restrict access to specific tables, configure the datasource to use a Database username that has restricted access permissions. If the Database username is prevented from accessing a table, then Confluence will also be unable to access it.
This can be combined with the Macro Security Plugin to control which users have access to which datasources. A page could be created for Management that accesses the database via a datasource with a Database username that has wide-ranging access. A separate page could be created for frontline staff that uses a datasource with a Database username that has more restricted access. Then, either restrict the pages via normal Confluence security controls, or use the Macro Security Plugin to associate specific users/groups to each datasource.
Database Views
Databases also have the ability to define Views to data. These views can typically be given different security permissions than the underlying data.
For example, imagine a database with:

  • A table called ‘Staff’ that contains Names and Salaries
  • Access to the Staff table is given only to HR
  • A view is created called ‘Stafflist’ which shows only the names of staff (not their salaries)
  • Access to the Stafflist view is given to everybody

By using a datasource with a Database username that has access to the Stafflist view, but no access to the underlying Staff table, means that Confluence can show the public data, whilst not having access to the confidential information.
Summary Database
Extracting summary data into an intermediate “datamart” database can reduce the risk of exposure of sensitive data. Essentially, you summarize and store the data in a separate database, and connect a Confluence datasource to that database instead of the database with your sensitive data in it. This technique is described in the section on Optimizing section.

Confluence for Business Intelligence Part 7 &#8211...