In this blog, we’re going to cover some fundamentals for a successful Row-Level Security (RLS) implementation in ThoughtSpot.

• What is RLS?
• RLS Benefits
• RLS Use Cases
• Admin Permissions

• Overview
• Group Setup
• Step–by–Step Instructions

• Overview
• Step–by–Step Instructions
• Option A: Modify ACL Table
• Option B: Add New ACL Table

Row-level security provides the ability to have a single data source provide different data to multiple audiences. Row-level security can help us avoid duplicating or creating different versions of data sources, worksheets and Liveboards for different audiences.

RLS in ThoughtSpot works by referencing the username for a logged-in user and any groups that they are a part of. It then leverages formulas and an Access Control List (ACL) to filter rows so that the user only sees the records in a data source they have been assigned based on their group membership. Row-level security in ThoughtSpot is executed using inner joins between the user directory and system tables from your data warehouse.

The benefits of row-level security are straightforward.

You keep data secured and hidden from users who shouldn’t see it.

You can eliminate the need to create distinct objects for different user groups. RLS allows you to create one object and only show the relevant data to those users.

This answer depends entirely on your business and use case. For ThoughtSpot embedded customers who are serving data for external audiences and customers with data that needs to be secured for specific internal users, it should be considered essential. Let’s walk through some examples:

Who Can Manage Row-Level Security in ThoughtSpot?

Creating row-level security rules is limited to those who meet the following criteria:

• You are a ThoughtSpot administrator.
• You are granted “Can Administer and Bypass RLS” or “Can administer ThoughtSpot” privileges by your administrator.

Implementation Patterns

Row-level security implementations can vary in complexity. We’re going to look at two approaches that we see with our customers; simple rule-based RLS and using an ACL.

In this approach, permissions are granted using a combination of data values in a system table and user or group names in ThoughSpot to create a rule that is used to filter the data. In order to do this, the group name or user names must exactly match the data values in the rows that you want a user to see.

In this example, we’ll show how you can use the ts_groups and ts_username attributes to implement simple rule-based RLS. These attributes allow us to relate ThoughtSpot users to our data model used in our Sales Liveboard below:

Let’s simulate creating an RLS rule locked down by SALESTERRITORYKEY, where each sales territory has a corresponding group. The field used to create the rule is located in the DIMSALESTERRITORY table, which provides us with the sales territory information for each customer and transaction in the FACTSALES table.

The column SALESTERRITORYKEY is the field containing values in the range of 1-10, where each value uniquely identifies a sales territory (Southeast, Central, Canada, Northwest, etc.).

The name of the group will be 6, which is the SALESTERRITORYKEY value representing the Canadian region in the DIMSALESTERRITORY table. We will use “Canada” as the display name so that it is friendly for those managing groups and users.

The Group Name field will be evaluated against the values in the DIMSALESTERRITORY table when we create our RLS rule. The users in this group will only be able to view sales records where the SALESTERRITORYKEY is equal to 6.

We will be creating the RLS rule on both the DIMSALESTERRITORY table and the FACTSALES table to prevent any data leakage.

*Note that rules can only be applied to tables, not worksheets or views.

We are using the SALESTERRITORYKEY field, a numeric field, to create a boolean rule against the group you created. In the case of our ‘Canada’ group, it will now return records for records where SALESTERRITORYKEY=6 for members within that group.

In this scenario, the to_string() function is required to cast the SALESTERRITORYKEY column as a string so the RLS rule can be evaluated correctly, since the ts_groups() field is a string. You could also avoid this step by re-typing the column in the database.

Validate that the rule is working as expected by logging in as a test user that is a member of the group we created in Step 1 and view the Sales Liveboard.

Here is our liveboard with simple rule-based RLS applied. You will notice that only sales figures for Canada are displayed:

You can add multiple RLS rules to one table, but it’s important to know that when combining rules, ThoughtSpot will evaluate these as an “OR” statement, meaning that users that are subject to either individual rule will be able to see the data.

For scenarios where you need multiple criteria, you will want to use an ACL table to control your permissions.

The ACL approach is common when the requirements for securing your data model have more complex hierarchies or multiple conditions that need to be satisfied. The ACL method requires the creation of a table in your data warehouse where data access controls can be managed and joined into your data model.

Examples of Use Cases Requiring ACL to Create Row-Level Security Could Be:

• Sales managers need to be able to view regional and sub-regional sales. Sales associates should only see their store’s sales.
• People managers should be able to see their direct report’s compensation history, while HR directors should have access to all data, and individual employees should only see their own compensation.

Let’s take our sales rep example from earlier and imagine we want to give some reps access to multiple sales territories, while others have access to only one territory or all territories in the data.

Our Data Model in ThoughtSpot Schema Viewer:

TERRITORY NAME is a field in our DIMSALESTERRITORY table that is used to control security and will be joined to the DIMSALESTERRITORY table. The TERRITORY NAME field is assigned to each sales representative.

A USERNAME will be used to map the territory-level restrictions to users who require it

GROUP is used to provide access to all territory data to those who are added to that group in the admin console. In this scenario, users in the ALL_TERRITORIES group will be able to see data for all territories.

Above: From this table, we can infer that user jhulbert should only see data for the territories “Canada” and “Southeast.”

Users added to this group will not be subjected to the RLS rule for sales territory and will be able to see all data.

In this scenario, we will perform a left join between our DIMSALESTERRITORY and ACL_TABLE. The column SALESTERRITORYREGION is the column in the DIMSALESTERRITORY table we will want to join to with the TERRITORY NAME column in the ACL_TABLE.

This relationship will allow us to relate users to sales records based on the territories to which they have access. The left join will allow us to retain records from our DIMSALESTERRITORY and only include users that match the ACL_TABLE.

By logging in as a test user in the ALL_TERRITORIES group, you can validate that a user can see all territory data.

Here is a ThoughtSpot search result for a user in the ALL_TERRITORIES group in which all territories are present.

By logging in as a test user subject to RLS, you can validate that a user can see only the territories assigned to their username in the ACL_TABLE. For user jhulbert, we can confirm that I only have access to sales figures for the Southeast and Canada regions.

Imagine that you have an additional requirement for users to have restrictions on what products they can view sales for in addition to their territories.

Modify your existing ACL table to include access control columns for both Product key and Territory Name.

ACL Table requirements:

• For each “All Access” group, there should be a single record for each available value. Ensure that there are no duplicate values, or this could duplicate records in your data source.
• For each user with restrictions, there should be one record for each value and each dimension that they have limited access to.
• The refined ACL table will be joined to both DIMPRODUCT and DIMSALESTERRITORY

From this table, we can infer that user jhulbert should only see data where:

TERRITORY NAME = “Canada,” “Central” and “Northwest”

ProductKey = “HJ-5811” and “BK-M82S-44”

This is the data model in ThoughtSpot. The ACL table is connected to both the DIMSALESTERRITORY table and the DIMPRODUCT table to secure our dimensions Product Key and Territory Name.

Create an additional table to map our users and groups to our product dimension, which gives us two ACL tables. This new ACL table will be joined to the DIMPRODUCT table and leverage the same security as the RLS rule written in the previous step.

The structure of that new ACL table:

The data model with the second ACL table was added and joined to the DIMPRODUCT table:

When logged in as our test user jhulbert, we have security applied on both ProductKey and SALESTERRITORYREGION.

 A well-thought-out row-level security strategy is imperative when launching your analytics platform. If you are wondering how to implement RLS at your organization or have questions about ThoughtSpot, we would love to help you out! Get in touch with us.