Signing and security for sensitive tiles
When a tile queries data from external data sources, security measures are needed to prevent a malicious user from having SquaredUp DS execute unauthorized queries and scripts.
The following tiles query data from external data sources and are considered sensitive tiles:
- SQL tiles query SQL databases
- Web API tiles query external Web APIs
- PowerShell tiles run PowerShell scripts
Signing for sensitive tiles
To make sensitive tiles that access external data sources more secure, SquaredUp DSuses signing. When a SquaredUp DS admin creates a sensitive tile, it is automatically signed, meaning the JSON config of the tile contains a cryptographically hashed signature.
Signing fulfills two functions:
Since signing makes sure that a query or script has been created by a SquaredUp DSadmin, the SquaredUp server trusts them to be safe to run and show the results to users viewing a dashboard, even to unauthorized users viewing an Open Access dashboard.
Since tiles are only signed when they are created by SquaredUp DS admins, signing prevents SquaredUp DS users from creating or editing queries or scripts for tiles that access external data. Even if a user can author dashboards due to their Team Folder permissions, they can't create or edit queries and scripts in sensitive tiles.
Security factors for sensitive tiles
There are three factors to consider regarding security for those tiles:
- Who can create and edit the query or script?
- What identity is used to run the query or script?
- Who can see the results?
Who can create and edit the query or script?
By default, only SquaredUp DSadmins can create and edit dashboards and perspectives, which means only them can create and edit queries and scripts. When an admin creates or edits a sensitive tile, it is automatically signed.
If you are using Team Folders, you can allow SquaredUp DS users to edit dashboards in a Team Folder by giving them the Author or Owner role for it. Being able to edit tiles in a dashboard means the user also has access to sensitive tiles:
- they can view the queries and scripts in edit mode
- they can attempt to create or edit queries or scripts in edit mode, but can't successfully do it due to signing. See What happens if a SquaredUp DS user tries to create or edit queries or scripts?
To make sensitive tiles that access external data sources more secure, SquaredUp DSuses signing. When a SquaredUp DS admin creates a sensitive tile, it is automatically signed, meaning the JSON config of the tile contains a cryptographically hashed signature.
Signing fulfills two functions:
Since signing makes sure that a query or script has been created by a SquaredUp DSadmin, the SquaredUp server trusts them to be safe to run and show the results to users viewing a dashboard, even to unauthorized users viewing an Open Access dashboard.
Since tiles are only signed when they are created by SquaredUp DS admins, signing prevents SquaredUp DS users from creating or editing queries or scripts for tiles that access external data. Even if a user can author dashboards due to their Team Folder permissions, they can't create or edit queries and scripts in sensitive tiles.
What happens if a SquaredUp DS user tries to create or edit queries or scripts?
What happens after a user tries to create or edit queries or scripts depends on your version of SquaredUp DS.
In version 4.4 and below:
When a SquaredUp DS user creates or edits a sensitive tile, the tile is permanently marked as untrusted. The tile no longer functions until a SquaredUp DS admin restores the signing of the tile. Before restoring the signing, users can't see the result of queries and scripts in the tile, even if an administrator further modifies it and publishes it. This is to prevent the accidental execution of untrusted queries.
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 of the dashboard with the affected tile.
- Edit the tile's JSON.
Find the_security
property with the valueSigning predicate redacted due to insufficient permissions
. - Modify the
_security
property to have a value ofsigning
. - Click the Apply changes button.
- Save the JSON.
- Publish the dashboard.
The query can now execute and the results are available to dashboard viewers.
In version 4.5 and above:
Any changes by a SquaredUp DS user to sensitive queries or scripts are not saved. Users will see one or more of the following errors:
- 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
After making disallowed edits to a sensitive query any change (to the query or the rest of the dashboard) cannot be saved. Any further changes to the rest of the dashboard made after the disallowed edits to the sensitive query will also be lost. The user has to click the invalid changes button and then discard to discard the disallowed changes.
Enabling a SquaredUp DS user to create and edit queries and scripts
The easiest way to enable a user to create and edit sensitive tiles is to turn them into a SquaredUp DS admin. In the rare case that you want the user to be able to create and edit sensitive tiles but don't want to make them a SquaredUp admin, you can give them permission to do so (and therefore permission to sign tiles).
- Make sure the user has the Team Folder role Author or Owner for the dashboards you want them to be able to edit.
- In SquaredUp DS, navigate to the Team Folder you want to edit.
- Click on the view folder settings button at the top right of the page.
- Click on the edit this folder button.
- To set the permissions for this Team Folder, add a new user and give them a role or edit the role of an existing user.
- Click save changes.
- Give the user permission to sign tiles.
Note: This feature should be used with caution. If you give a user permission to sign tiles, they can write, edit and execute any query and script (SQL, Web API and PowerShell). This can result in sensitive information disclosure or even data loss.- Log on to the SquaredUp server.
- Run Notepad as administrator (File, Run, type
notepad
, right-click and select Run as administrator), and then open the following file from the SquaredUp DS folder:\User\Packages\QueryAuthoring\User\access-control.json
The file should look similar to the following:Name of the SquaredUp folder
The default name of the SquaredUp folder is
SquaredUp
for v6 and above.For v5 it is
SquaredUpv5
.Location of the SquaredUp folder
If you deployed SquaredUp DS via the Azure or AWS Marketplace:
The default location for the SquaredUp folder is
F:\
.SquaredUpv[Version Number]
For v5 it isF:\SquaredUpv5
.If you installed SquaredUp DS using the installer:
A custom location may have been chosen during the installation.
The default location for the SquaredUp folder is
C:\inetpub\wwwroot\SquaredUp
For v5 it is
C:\inetpub\wwwroot\SquaredUpv5
.{ "view": [], "author": [], "owner": [] }
- Add the user (or group) to the
author
property.
To add a user, you need to enter the username or group. Note that \ separators should be entered as\\
instead
To add a user, you need to enter the ID and display name of the user or group. Note that the ID can be found in Azure AD from the relevant object's Overview page asObject Id
.{ "view": [], "author": [ "mydomain\\myuser" ], "owner": [] }
{ "view": [], "author": [ { "id": "2be16d89-db0d-4fcd-acc7-6ae843dc8a22", "displayName": "Group Display Name" }, { "id": "56274654-5ba3-46b2-8fdc-1c7479524eb2", "displayName": "[email protected]" } ], "owner": [] }
- Save the file to the same location.
- To see the change take effect in SquaredUp DS 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.
Only for version 4.4 and below: Giving a user permission to sign tiles does not affect dashboards that have already been authored by that user. If a user already authored sensitive tiles and caused them to be untrusted, the signing of those tiles must be restored. This can be done by either deleting and re-creating individual tiles or the entire dashboards, or by restoring the signing in the JSON of the affected tiles.
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 of the dashboard with the affected tile.
- Edit the tile's JSON.
Find the_security
property with the valueSigning predicate redacted due to insufficient permissions
. - Modify the
_security
property to have a value ofsigning
. - Click the Apply changes button.
- Save the JSON.
- Publish the dashboard.
The query can now execute and the results are available to dashboard viewers.
What identity is used to run the query or script?
It depends on the tile and their settings how queries and scripts are run:
Tile | Queries/Scripts are run as: |
SQL tiles | SquaredUp DS app pool identity (unless documented otherwise) |
Web API tiles | SquaredUp DS app pool identity (unless documented otherwise) |
PowerShell tiles | Run As account of the tile (by default, SquaredUp DS app pool identity) |
Example: If the SquaredUp DS app pool identity is MY SERVICE ACCOUNT
, queries will always be performed by MY SERVICE ACCOUNT
on behalf of the user, regardless of which SquaredUp DS admin created the query or which SquaredUp DS user is viewing the dashboard.
Limiting or scoping the data available to this identity is one method of securing external data sources.
Who can see the results?
Since signing makes sure that a query or script has been created by a SquaredUp DSadmin, the SquaredUp server trusts them to be safe to run and show the results to users viewing a dashboard, even to unauthorized users viewing an Open Access dashboard.
Known issues
In v4.5 and above dashboard edits after making disallowed changes to a sensitive query are lost
See What happens if a SquaredUp DS user tries to create or edit queries or scripts?
In v4.4 and below unsigned queries are untrusted until modified
See What happens if a SquaredUp DS user tries to create or edit queries or scripts?
Permission changes do not affect dashboards already authored
Only for version 4.4 and below: Giving a user permission to sign tiles does not affect dashboards that have already been authored by that user. If a user already authored sensitive tiles and caused them to be untrusted, the signing of those tiles must be restored. This can be done by either deleting and re-creating individual tiles or the entire dashboards, or by restoring the signing in the JSON of the affected tiles.
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 of the dashboard with the affected tile.
- Edit the tile's JSON.
Find the_security
property with the valueSigning predicate redacted due to insufficient permissions
. - Modify the
_security
property to have a value ofsigning
. - Click the Apply changes button.
- Save the JSON.
- Publish the dashboard.
The query can now execute and the results are available to dashboard viewers.
Enabling Open Access can disable previously working queries
If a non-administrator enables a dashboard for Open Access (see Sharing Dashboards with anyone - Open Access), any queries on the dashboard (even if they were authored by an administrator) will become untrusted and disabled in the process.
To amend this, an administrator has to restore the signing of the affected tiles.
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 of the dashboard with the affected tile.
- Edit the tile's JSON.
Find the_security
property with the valueSigning predicate redacted due to insufficient permissions
. - Modify the
_security
property to have a value ofsigning
. - Click the Apply changes button.
- Save the JSON.
- Publish the dashboard.
The query can now execute and the results are available to dashboard viewers.