Implement row-level security in embeds

Row-level security (RLS) is a method for restricting data access based on user identity or characteristics.

Sigma offers two functions that provide information about the current user.

CurrentUserEmail returns the email of the user viewing the data.

CurrentUserInTeam returns True, if the current user is a member of any of the given teams. This is covered in this section of the documentation.

Additionally, customers can create new custom User Attributes at the Dataset level or use a custom SQL query as the data source, filtered for CurrentUserAttributeText.

We recommend that you review the QuickStart: Embedding 4: Application Row Level Security, which covers each step in detail.

Example use case

Let's assume that we have a table that has a column called "Region" and we want to restrict users to certain regions while allowing them to use all the other functionality in the Workbook.

We will create a new User Attribute called "Region", and the embed API will be adjusted to pass a value for the UA at runtime. This method will work for both the Dataset and Custom SQL methods.

Step 1: User Attribute - Region

Create a new UA for "Region":

Step 2: Create the dataset

We will use the Sigma Sample Database and the PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA table in this example.

🚧

After creating, configuring and saving a Workbook (based on this Dataset), remember to share it with your test teams and adjust the embed API for the new Embed Path. We will use it to test against in the last step.

We will add a new column to this Dataset that will be used to evaluate the "Region" UA that is passed by the embed API.

We will set a Filter on this new column to only show rows that evaluate to True.

📘

Note that when this filter is enabled in the Dataset, no rows are shown as the UA did not have a default value set when we created it.

Step 3: Workbook

Create a Workbook based on this new Dataset (you can click the Explore button). Save the Workbook, generate a new Embed Path and share the Workbook with the desired Teams. For example, we created a few new Teams so can test passing different values:

Step 4: Configure the API

We need to pass the new UA ("Region") to Sigma via the embed API, so that when the embedded table appears, RLS is set based on the UA's passed value.

For example, we modify the embed API we demonstrated earlier to include the new Region parameters and set it's value to "West", we would expect only rows with the Region = West to be included in the table.

We also adjusted the values related to the user and Team based on the region we are passing. While these values are hard-coded here, they would normally be set dynamically at runtime by the parent application.

NOTE: For example, this parameters would be added (or adjusted) in "section 6" of the sample embed API code for ease of viewing.

    searchParams += `&:[email protected]`;
    searchParams += `&:external_user_id=sales_team_west_123`;
    searchParams += `&:external_user_team=Sales_Team_West`;
    searchParams += `&:account_type=Viewer`;
    
    // NOTE: UA values are case sensitive. 
  	searchParams += '&:ua_Region=West';

Results in browser - UA = West

If we look at the Column Details for the Region column, we can see that only rows for the West region are shown:

Passing multiple values

We can adjust the Embed API to pass more than one value, in a comma-delimited list.

Adjust the sample Embed API code as follows, to pass two values for Region:

searchParams += '&:ua_Region=West,East';

Save the change and refresh the embed browser page. Column details (for the Region column) now shows West and East rows:

Custom SQL

Some customers may prefer to write SQL themselves and Sigma supports that, and allows the use of UA's within the queries

This method can be used anywhere in the SQL statement for any user attribute. For example, it can be used to switch the database name, table name, in the select clause or where clause.

Building on the Region User Attribute created in the previous steps, we can use it in a Custom SQL dataset to provide security.

The process for working with Custom SQL is documented here.

Create a Dataset with Custom SQL

We will use the following query to validate this method:

SELECT * FROM RETAIL.PLUGS_ELECTRONICS.PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA
WHERE STORE_REGION = '{{#raw system::CurrentUserAttributeText::Region}}'

📘

This syntax ('{{#raw system::CurrentUserAttributeText::Region}}') can be used anywhere in the SQL statement for any user attribute e.g. it can be used to switch the database name, table name, in the select clause or where clause.

As before, when setting up this Dataset with a User Attribute with no default value set, the return will be No data. This is expected:

Setting the sample Embed API code to return only rows where Region = West:

searchParams += '&:ua_Region=West';

Only West is shown:

🚧

The actual syntax used in server.js or the customer SQL may vary when attempting to pass more than one value for region in a comma delimited list. This will be dependent on the data source (ie: Snowflake, BigQuery etcetera) and how it interprets the request syntax.

Workbook Controls in Embeds

If your workbook contains workbook controls, you may want to consider how they effect your embedded analytics.

Hidden Controls

Controls can be hidden from viewers. To hide one more controls, create a new hidden page in the workbook. Housing your controls here will hide them from anyone without Can Edit permission on the workbook (including your embed viewers). Learn more.

Controls in a Workbook URL

When you create an embed, you may choose to include control values in the embed URL yourself.

Learn about controls in the URL.