This QuickStart introduces you to Sigma embedding using Row-Level Security (RLS) to personalize / limit the data exposed to a user based on values passed to Sigma at runtime. This QuickStart assumes you have already taken the QuickStart Embedding 1: Prerequisites so that you have a sample environment to complete the tasks in this QuickStart.

We also recommend you table the the QuickStart Embedding 3: Application Embedding as we will build on that content.

Some steps may not be shown in detail as we assume you have taken these other two QuickStarts or are familiar with Sigma workflows.

RLS is a method for restricting data access based on user identity or characteristics. Sigma supports RLS in a few different ways but the vast majority of Sigma customers use RLS with User Attributes at the Dataset level. This QuickStart will cover how to implement RLS Dataset with User Attributes.

Target Audience

Semi-technical users who will be aiding in the planning or implementation of Sigma. Limited SQL and technical data skills are needed to do this QuickStart. It does assume some common computer skills like installing software, using Terminal, navigating folders and edit/copy/paste operations.

Prerequisites

• A computer with a current browser. It does not matter which browser you want to use.
• Access to your Sigma environment. A Sigma trial environment is acceptable and preferred.
• A working web server based on Node.js as demonstrated in the QuickStart Embedding 1: Prerequisites
• Some content to embed. You can embed a Workbook, Table or Visualization.
• Downloadable project files discussed later in this document.

Free Trial

• Node.js and required Packages (all free):
  • Express
  • Node-supervisor
  • crypto (is now included with Node.js installation)

What You'll Learn

The exercises in this QuickStart will discuss and walk you through the steps to implement Dataset RLS with User Attributes (UA for short).

What You'll Build

We will embed Sigma content inside a Node.js web application, passing runtime parameters to configure the embed and demonstrate Row-Level-Security.

Sigma uses this method to restrict data access based on user identity combined with User Attributes (UA) passed by the Parent application at runtime.

Datasets are used to implement all RLS in embedded Sigma objects.

The UA must be defined ahead of time (one time) in the Sigma portal. You can define any UA and as many as required to enforce the required data security. You can set a default value to a UA at this time.

The Parent application must pass an embed mode argument of userbacked.

To implement this feature, you must have minimum Can Edit access on the individual dataset.

The workflow (as shown below) is very straightforward and yet flexible to allow only the data that is appropriate for the user to be shown, based on one or more UAs. How you decide to filter the data is up to your organization's roles/rules.

Let's build an example based on the previous QuickStart, Embedding 4: Application Row Level Security. It is ok to build your own if you are familiar with Sigma.

Download the Lab file called sigma_application_embed_RLS.zip.

Unzip the file and use Terminal to launch the Node web server.

Steps:

Open Sigma.

Navigate to Administration / User Attributes and add a new attribute called Region. You can give it a description but no need for any default value. Click Create.

Open the Dataset called Application Embedding.

Click on the three vertical dots next to the Dataset name at the top of the page and select Duplicate Dataset.

Rename the new Dataset to Application Embedding RLS.

Click on the Worksheet tab of the new dataset and ensure that they are in Edit mode.

Add an new Column and rename it to ua_Region.

Use this formula for column:

Contains(CurrentUserAttributeText("Region"), [Store Region])

On the left sidebar, add a filter. Filter against the column ua_Region = True.

It is ok that you don't see data here.

Publish the new Dataset.

At Runtime (page refresh in this case):

• The new column `ua_region` will be evaluated against the Store Region column for each row
• The filter will only show rows where there is a match for ua_Region and Store Region

Open the Workbook Application Embedding and add a new Page called Application RLS

Add a new table to the page, based on the Dataset you just created "Application Embedding RLS" and Publish the changes.

Open server.js from the downloaded files.

You are now ready to start the Node.js Express web server. Use Terminal and navigate to the Public folder where you just modified the two files.

In Terminal run the command:

supervisor server.js

Browse to http://localhost:3000. You should only see rows from the East Region.

Back in your server.js file, change the ua_Region to East,West. Save the file.

Sending comma separated values via embed UA param requires a step for updating the column formula in the underlying Sigma RLS Dataset for the ua_Region column. The equality formula needs to be replaced with a Contains(), for ex: Contains(CurrentUserAttributeText("Darien_Region"), [Store Region])

and refresh the browser page. You should see the Region has been updated to reflect the new values.

You can use the Column Details feature to see that there East and West are present in the data now.

Once we successfully configured User Attributes, we can use them in a Custom SQL dataset to provide security. 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.

For instance, assume that data for each customer is present in a separate schema. We can leverage Custom SQL to switch the schema based on the user.

Using the example we just did in the last exercise, you can create a Dataset based on SQL query instead of Table and leverage a SQL Where clause to enforce the UA passed at runtime.

We will reuse the embedding framework used in the last section except that we will create a new Dataset based on SQL Query and use the Where clause to accept the UA passed at runtime.

Steps:

Open Sigma and navigate to Administration / Attributes and open the Region attribute.

Click Edit

This time set the default Value to East.

Click Save

Navigate to the `Application RLS` Workbook and `edit` it.

Create a new Page and rename it to RLS SQL Query.

Click + Add New and Table.

For Source, select Write SQL.

In the Connections drop down, select Sigma Sample Database.

In the large open whitespace paste the following code:

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

//Contains(Concat(",", CurrentUserAttributeText("region"), ","), Concat(",", West, ","))

This SQL code will get all columns from the table we have used previously but limit the return based on the "Region" UA and return only 10 rows.

Set to value for ua_region in server.js to East.

Click Run. You should only see rows from Store Region=East (since that is the UA Default). Click Done and Publish.

Refresh your browser to see the embed (make sure you are looking at the right Workbook Page RLS SQL Query):

Change server.js to West and save.

Check your embed in the browser. You should now see Region = West.

Using our existing Sigma Application Embed we passed a runtime parameter to configure the embed and demonstrate Row-Level-Security at the Workbook and Dataset level.

Additional Resource Links

Be sure to check out all the latest developments at Sigma's First Friday Feature page!

Help Center Home
Sigma Community
Sigma Blog