Skip to main content

Signing and Security

Catherine
  • Updated

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:

AnErrorOccurred.png

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.

InvalidChangesDiscard.png

 

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

  1. Log on to the SquaredUp server directly, or via RDP

  2. 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 (typically C:\inetpub\wwwroot\squaredupv4): User\Packages\QueryAuthoring\Useraccess-control.json

    The file should look similar to the following:

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

  3. Amend the file to include the username or group. Note that \ separators should be entered as \\ instead

        {
        "view": [],
        "author": [
	            "mydomain\\myuser"
            ],
        "owner": []
}
  4. Now re-save the file to the same location.
  5. To see the change take effect in SquaredUp either recycle the IIS application pool, or select reload from ☰ > system > Dashboards > Reload all content

Signing_reload.png

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:

  1. Enter edit mode.

  2. Edit the tile's JSON.

    Signing_redacted.png

  3. Modify the _security field, to have a value of signing.

    Signing_restored.png

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

 

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.

Related articles