ThoughtSpot 101: Row-Level Security

Data

ThoughtSpot 101: Row-Level Security

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

1. Row Level Security Overview

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

2. Simple Method: Rule-Based RL

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

3. Complex Method: Access Control List

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

RLS Overview

What is RLS?

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.

RLS Benefits

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.

RLS Use Cases

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:

Checklist of different scenarios where use cases are weighed

Admin Permissions

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.

ThoughtSpot Admin panel with checked options mentioned above

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.

Simple Method: Rule-Based RLS

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.

PROs list, No ELT Required for ACL Table, Lower admin overhead, fast to implement

Cons list, High admin overhead when many groups need RLS, can be difficult to scale with complex RLS logic

Group Setup

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:

Sales Liveboard

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.

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.

1. Create a group in the ThoughtSpot admin panel

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.

Group Name: 6

2. Locate the table and column where you row-level security will be implement

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

DIMSALESTERRITORY table

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

3. Create your RLS rule

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.

Sales Territory table

4. Test your RLS rule

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:

Sales Liveboard with Canada's total sales

Adding Multiple RLS rules to one table

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.

DIMSALESTERRITORY table evaluates as an "OR" statement

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

Complex Method: Access Control List (ACL) Table

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.

PROs list, RLS rules can be auto managed/updated and can let you implement more complex scenarios

Cons list, requires technical resources to create/manage logic

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:

ThoughtSpot Schema Viewer

1. Create your ACL Table schema

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.

Group table

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

2. Create your all territories user group in the Admin console

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

Edit Group pane with Group Name set to all_territories

3. Create the join between the DIMSALESTERRITORY table and the ACL table

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.

Join table joining sales and territories

4. Create your RLS rule on DIMSALESTERRITORY and FACTSALES

RLS pane with username=ts_username or group=ts_groups

5. Test

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.

Sales table with updated regional sales

Example of a more complex ACL Implementation

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.

Solution A

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”

Group table with territories, usernames, and more

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.

Data model that looks like a spiderweb

Solution B

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:

New ACL table restructured

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

Data model complicated enough to kill god

Testing

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

Complete Sales Liveboard

Conclusion

 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.

More About the Author

Jack Hulbert

Analytics Architect
ThoughtSpot 101: Row-Level Security In this blog, we’re going to cover some fundamentals for a successful Row-Level Security (RLS) implementation in ThoughtSpot. What is ...
ThoughtSpot 101: 5 Tips for Creating Really Good Liveboards In our last blog, we gave you some tips on how to create worksheets that will bring your users a delightful search analytics ...

See more from this author →

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072

×

Love our blog? You should see our emails. Sign up for our newsletter!