Signing and security
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
Execution of any query always makes use of the SquaredUp application pool identity, (see How to check and modify the application pool identity) unless documented otherwise.
Consider the following scenario:
- Application pool identity
MY SERVICE ACCOUNT
- Dashboard with a SQL tile
- Source database
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.
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 (see 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 (see Team Folders) receive rights to create and edit queries (which by default is only administrators)
This feature should be used with 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, see Executing Queries).
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
User\Packages\QueryAuthoring\User\access-control.jsonWhere to find the SquaredUpv[Version Number] folder
If you deployed SquaredUp via the Azure Marketplace:
The default location for the
SquaredUpv[Version Number]folder is
For example, for SquaredUp v5 the default location is
F:\SquaredUpv5and for v4
If you installed SquaredUp using the installer:
The default location for the
SquaredUpv[Version Number]folder is
C:\inetpub\wwwroot\SquaredUpv[Version Number], but a custom location may have been chosen during the installation.
For example, for SquaredUp v5 the default folder location is
C:\inetpub\wwwroot\SquaredUpv5and for v4
The file should look similar to the following:Copy
Amend the file to include 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
"displayName": "Group Display Name"
Note: this is the same format you'd find an entry in access-control.json for any other team folder.
- 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.
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.
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.
_securityfield, to have a value of
- 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 (see In v4.4 and below unsigned queries are untrusted until modified)
- 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 (see 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 (see In v4.4 and below unsigned queries are untrusted until modified) and publish the edited dashboard. This will restore the queries to working order.