Skip to main content

Relational Security

Security for relational queries in Una allows non-privileged users to build and execute simple relational queries subject to table and row restrictions.

Privileged Users​

Users that are part of a security group which has the Relational Access role assigned are considered privileged users from a relational security perspective. Privileged users can execute arbitrary SQL code in their tenant without restrictions, including dangerous commands like drop table. Privileged users can also potentially access fact table data in models directly, bypassing any dimension security settings.

Regular Users​

In Settings -> Security Groups, clicking on the more button brings up a Table Permissions item. Clicking on it opens a popup where an administrator is able to manage a list of tables users of that group should have access to. For each table, an optional read filter can be specified as a SQL expression (i.e. [Field1] in (5, 7)). A write checkbox will allow users in that group to save data to the table (through a Table/Form/PivotTable widget) and allow an optional write filter. Note: An empty read filter defaults to everything; an empty write filter defaults to the read filter.

Regular users may write only write SELECT statements that retrieve data from the list of tables they have access to. If a user is part of multiple groups, they will access to all the tables in all their groups. If the same table is authorized multiple times through different groups, the resulting filter for the table is an OR combination of the conditions from all groups.

Filters are applied automatically by the backend.

Ownership and ExecuteAsOwner flag​

Each filesystem object has an owner. The owner is the user who last made changes to the object. In addition, each file system object can be flagged as ExecuteAsOwner. In a dashboard, ExecuteAsOwner means that relational queries in View mode will run with the privileges of the dashboard owned. Only relational queries are impacted -- model/dimensional queries will run with the privileges of the user opening the dashboard. If the user switches to Edit mode they will get a warning and relational queries will start running with editing user privileges instead.

In a Report Book, ExecuteAsOwner means that any relational parameters will execute as the owner of the report book. Individual dashboards that are part of the report book will obey their own ExecuteAsOwner flag settings.

When editing a dashboard or report book, use the Settings icon and then the Security section to set the ExecuteAsOwner flag. Only the owner of a file can set the flag.

What is subject to relational security​

  • SQL queries in Table/Form/PivotTable objects
  • SQL queries in edit controls
  • SQL queries in dashboard parameters/report book filters
  • SQL queries in variables
  • SQL model queries in Matrix/Chart objects
  • After Save triggers (exec will require the dashboard to be owned by a privileged user)
  • Dashboard exports as PDF/Excel
  • OData queries

What is exempt​

  • AI helper in SQL query editors is not security aware (future phase)

Enabling​

To enable the new relational security features in a tenant set the relationalSecurity feature flag. If you want to test the new model before enforcing it, set the relationalSecurityLogOnly feature. It will log any queries that would fail/be restricted when enforcement is on. Logs are currently visible in App Insights only and will require a Dev Ops admin to access them.

Note: if both features are enabled, the former takes precedence!