Overview
Dashboards and perspectives in SquaredUp can query data from external data sources:
There are two important concepts to consider for the security of these sources:
- Who is executing the query
- Who can see the results
Users who do not have permissions to edit queries may see the following messages:
An error occurred
Current user does not have permissions to modify sensitive tiles
You do not have permission to author or modify sensitive queries
You do not have sufficient permissions to access this resource
Executing Queries
Execution of any query always makes use of the SquaredUp application pool identity, unless documented otherwise.
Consider the following scenario:
- Application pool identity
MY SERVICE ACCOUNT
- Dashboard with a SQL tile
- Source database
OPSDATABASE
- Query
SELECT 123;
In this particular instance, regardless of which user created, edited or is viewing the dashboard, the query will always be performed by MY SERVICE ACCOUNT
on behalf of the user, since this is the application pool identity.
This means that the data visible to SquaredUp is affected by whatever permissions MY SERVICE ACCOUNT
has on the SQL server.
Limiting or scoping the data available to this identity is one method of securing external data sources.
Results
Queries can run and display results to users, even if they themselves lack permission to access the source (such as a SQL database).
For example, anonymous users can be allowed to see SQL query results in dashboards via Open Access, even if they cannot personally access the SQL database.
In order to allow any users to see the results without also permitting them to edit or create arbitrary queries, SquaredUp employs signing.
Creating and Editing Queries
Signing is entirely transparent and can normally be ignored, but there are rules to how dashboards can be created:
- Only administrators can create and edit new queries. For example, only an administrator can create a new SQL tile with a query
- Any user can view a dashboard containing queries (including the results), but they cannot execute edits. If a non-administrator edits a query or authors a new one, the query will be saved but it will not ever be allowed to execute. This prevents other users from performing malicious queries or disclosing data they should not be allowed to see.
If a non-administrator attempts to create or edit a query, they will be presented with errors similar to the following:
After making disallowed edits to a sensitive query any change (to the query or the rest of the dashboard) cannot be saved. The user should click the invalid changes button and then discard to discard the disallowed changes. This is the only option available.
Any further changes to the rest of the dashboard made after the disallowed edits to the sensitive query will also be lost.
Delegating permissions for constructing queries
In version 4.0.8 and above, it is possible to delegate permissions for constructing queries. Doing so requires modifying a configuration file on disk.
All users who are authors in the special Query authoring Team Folder receive rights to create and edit queries (which by default is only administrators)
This feature should be used with extreme caution. The result is that the user or group can write, edit and execute any query - against SQL databases, web APIs etc. - possibly resulting in sensitive information disclosure or even data loss (depending on what rights the application pool identity has).
How to change or add to the users in this list
- Log on to the SquaredUp server directly, or via RDP
-
Run Notepad as administrator (File, Run, type
notepad
, right-click and select Run as administrator), and then open the following file from the SquaredUpv4 folder (typicallyC:\inetpub\wwwroot\squaredupv4
):User\Packages\QueryAuthoring\Useraccess-control.json
The file should look similar to the following:
{ "view": [], "author": [], "owner": [] }
-
Amend the file to include the username or group. Note that \ separators should be entered as
\\
instead{ "view": [], "author": [ "mydomain\\myuser" ], "owner": [] }
- Now re-save the file to the same location.
- To see the change take effect in SquaredUp either recycle the IIS application pool, or select reload from ☰ > system > Dashboards > Reload all content
The users listed as author in the file will now have permissions to create and edit queries.
Known issues
Dashboard edits after making disallowed changes to a sensitive query are lost
After making disallowed edits to a sensitive query it is no longer possible to save the dashboard. Any subsequent changes to the rest of the dashboard cannot be saved. Clicking invalid changes > discard, or navigating away from the page, result in changes being lost.
In v4.4 and below unsigned queries are untrusted until modified
Signing predicate redacted due to insufficient permissions
In v4.4 and below, when a non-administrator creates a tile containing a query, the query is permanently marked as untrusted. This means that even if an administrator further modifies it and publishes it, non-administrators cannot see the results of the queries. This is to prevent the accidental execution of untrusted queries.
In v4.5 and above any disallowed changes to sensitive queries are not saved so the procedure below is not required.
To restore the dashboard to working order for all users, an administrator can modify the JSON of the dashboard to re-enter signing
into the _security
field of the relevant tile(s) and publish the result:
- Enter edit mode.
-
Edit the tile's JSON.
-
Modify the
_security
field, to have a value ofsigning
. - Click the Apply changes button.
- Save the JSON.
- Publish the dashboard.
The query can now execute, and be available to other dashboard viewers.
Permission changes do not affect dashboards already authored
If a user's permissions are changed (e.g. they are given the ability to create and edit queries), any dashboards they have already authored must be manually edited to enable signing. Either by:
- Modifying the JSON of the affected tiles
- Deleting and re-creating individual sections/tiles
- Deleting and re-creating the entire dashboard
Enabling Open Access can disable previously working queries
If a non-administrator enables a dashboard for Open Access, any queries on the dashboard (even if they were authored by an administrator) will become untrusted (disabled) in the process.
To amend this, an administrator can follow the instructions above and publish the edited dashboard. This will restore the queries to working order.