Workbook Row-Level Security

IMPORTANT: This document is specific to Sigma's new Workbooks feature. If you instead want to learn about dashboard row-level security, please visit Dashboard Row-Level Security.

Row-level security (RLS) is a method for restricting data access based on user identity.  Secure filters are used to enforce RSL in workbooks.

Summary of Content

Requirements
Understanding Secure Filters
Create a Secure Filter
      [Step 1] Create a Hidden Page
      [Step 2] Create a table element with the data that you would like to filter and share
      [Step 3] Create a column with user-identifying filter criteria
            Example
      [Step 4] Set up your visible workbook page(s)
Related Resources

Requirements

Understanding Secure Filters

To be considered secure, a filter must be completely hidden from all non-Admin users without “Can Edit” access on the workbook. 

To achieve this completely hidden state, you will need to store the data element you are filtering and any controls targeting your element’s off-limits data on a hidden workbook page.

This is because data element filters are always accessible from their parent data element via the element’s expandable focus view. If the parent element (or any controls manipulating the element) is exposed to the user, so are its filters and any data hidden by those filters.

Pro-tip: This possible relationship between controls and data element filters should be considered when you choose to use a control element to pass values to multiple element filters at once. If you want a control manipulating multiple elements to be accessible to your workbook’s consumers, it should not directly interact with any values you are using to enforce RSL. 

Create a Secure Filter

Creating a secure filter for RSL has four high-level steps:

  1. Create a hidden page
  2. Create a table element with the data that you would like to filter and share
  3. Create a column with user-identifying filter criteria
  4. Set up your visible workbook page

[Step 1] Create a Hidden Page

  1. Click the + button in the bottom left corner of your workbook to create a new page.
    Screen_Shot_2021-07-21_at_9.42.26_AM.png
  2. Click the caret icon on the page’s tab to open its menu.
    Screen_Shot_2021-07-21_at_9.44.16_AM.png
  3. Click Hide.
    Screen_Shot_2021-07-21_at_9.44.31_AM.png
  4. [Recommended] Give your new hidden page an identifiable name.
    Screen_Shot_2021-07-21_at_9.49.31_AM.png

[Step 2] Create a table element with the data that you would like to filter and share

Tables can be created from the PAGE ELEMENTS section of your workbook's editor panel.

Don't forget to store your element on the hidden page.

[Step 3] Create a column with user-identifying filter criteria

Creating the right filter logic is the key to RSL.

Sigma offers two functions that provide information about the current user. CurrentUserEmail returns the email of the user viewing the Workbook. CurrentUserInTeam returns True if the current user is a member of any of the given teams.

Both functions can be used as a test to filter data.

Example

Let’s say you have a table with sales results, and you want each salesperson to see only their results.

First, you need to pull in the emails associated with each line of data in the Sales Results. You can then create a new column and set up the formula that tests for what data the worksheet viewer should see.

[Salesperson Email] = CurrentUserEmail()

This formula looks at emails in the column [Salesperson Email] and checks if it matches the email of the user currently viewing the worksheet. It will return True for all rows where the current user’s email matches the salesperson email. You now have a test for what data should be shown to which user.

Sigma can also create team level row security using CurrentUserInTeam. This function checks to see if the current user viewing the worksheet is part of any of the specified teams.

To set up team security, create a column that associates the data with the correct Sigma team. If the Southwest should only see data from California, Nevada and Arizona, set up a column with that logic.

If( [Region] = “California”, “South West”,  [Region] = “Nevada”, “South West”,  [Region] = “Arizona”, “South West”)

This will create a column that has the value Southwest for all rows of data that have the [Region] set to California, Nevada, or Arizona.

Expand this logic to include all of the teams you want to test for. Then create your filter column.  

CurrentUserInTeam([Team])

This will return True for every row of data where the current user belongs to the team indicated in that row of the [Team] column. You now know which data should be shown to someone, based on what teams they are a member of.

Filters can also be set up to test for multiple conditions. You could filter a sheet to show salespeople only data from leads that they own, and show all the data to sales managers.

( [Salesperson Email] = CurrentUserEmail() ) or ( CurrentUserInTeam(“Sales Manager”) )

This will return TRUE for all rows when the viewer is in the “Sales Manager” team and will return TRUE only for rows where the viewer email matches the Salesperson Email for all other viewers.

[Step 4] Set up your visible workbook page(s)

Visualizations and other visible elements on your workbook should all use your securely filtered table as a source. To do this, you should.

  1. Create or open a visible workbook page.
  2. Create a new data element from your filtered table. 

When a user views your workbook, the hidden table will automatically filter based on your filter criteria. All of the children elements will then also show only the data relevant to the user. Workbook users without “Can Edit” access to the workbook will only have access to the filters and controls that are set up on the visible pages, and will not be able to access the RLS controls. 

Related Resources

Data Element Filters
Intro to Control Elements
Hidden Controls in Workbooks