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?

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.

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).

1. Make sure the user has the Team Folder role Author or Owner for the dashboards you want them to be able to edit.
   How to set the permissions for a Team Folder

   1. In SquaredUp DS, navigate to the Team Folder you want to edit.
   2. Click on the view folder settings button at the top right of the page.

      

   3. Click on the edit this folder button.

      

   4. 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.

      

   5. Click save changes.

2. 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.
   How to give a SquaredUp DS user permission to sign tiles

   1. Log on to the SquaredUp server.
   2. 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
      Where to find the SquaredUp folder

      Name of the SquaredUp folder

      Location of the SquaredUp folder

      If you deployed SquaredUp DS via the Azure Marketplace:

      The default location for the SquaredUp folder is F:\SquaredUpv[Version Number].
      For v5 it is F:\SquaredUpv5 and for v4 F:\SquaredUpv4.

      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 and for v4 C:\inetpub\wwwroot\SquaredUpv4.

      The file should look similar to the following:
      Copy

      {
          "view": [],
          "author": [],
          "owner": []
      }

   3. Add the user (or group) to the author property.
      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 as Object Id.
      Copy

      {
      	"view": [],
      	"author": [
      	    {
      			"id": "2be16d89-db0d-4fcd-acc7-6ae843dc8a22",
      			"displayName": "Group Display Name"
      		},
      		{
      			"id": "56274654-5ba3-46b2-8fdc-1c7479524eb2",
      			"displayName": "[email protected]"
      		}
      	],
      	"owner": []
      }

   4. Save the file to the same location.
   5. 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.

3. 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.

   How to restore the signing of a tile

   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:

   1. Enter edit mode of the dashboard with the affected tile.
   2. Edit the tile's JSON.
      Find the _security property with the value Signing predicate redacted due to insufficient permissions.

      

   3. Modify the _security property to have a value of signing.

      

   4. Click the Apply changes button.
   5. Save the JSON.
   6. 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:

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.

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.