WHERE t1.customer_id IN ('cust_1', ...)
RLS rules
Table of Contents
In ThoughtSpot, the Row Level Security Rules are applied on Tables to restrict access to specific rows of data based on user or user group context. These rules ensure users only see the data they are permitted to access.
Overview🔗
RLS rules are defined within ThoughtSpot on Table objects, and automatically extend to all Models, saved answers, and Liveboards based on that Table.
The two basic patterns for RLS Rules are:
-
Direct RLS rules that reference a column on the table the rule is placed on.
-
Access control list (ACL) table rules that reference a separate table of entitlements within the same database.
If neither of these patterns can be implemented or if you want a fine-grained access control with dynamic attributes, consider implementing ABAC via RLS rules with variables.
System variables in RLS rules🔗
Rules are typically written using the system variables such as ts_username and ts_groups to specify how the user’s username or group memberships are translated into WHERE clauses in the SQL queries generated from the Table.
-
ts_username - To apply rules to the signed-in user.
The ts_username special value translates tot1.fieldName = 'users-username'within the generated queries. The variable is replaced with the signed-in user’s username. -
ts_groups - To apply rules to the groups that the user belongs to. The
ts_groupsspecial value translates tot1.fieldName IN ('group a', 'group b', …). The name property of every ThoughtSpot group that a user belongs to will be added to the list after theIN. The variable is replaced with the list of group names the user belongs to, ensuring that only permitted rows are returned in query results.
The username or ThoughtSpot group name property must be an exact match to the values within the database column. ThoughtSpot usernames and group names cannot be changed once they have been created, but their values can be any valid string. To programmatically create and assign the appropriate groups to be used within RLS rules, you can use the user and group creation REST APIs.
For more information about the system variables and how RLS rules are applied using these variables, see the RLS rules documentation.
Supported data types🔗
Both ts_username and ts_groups are string values, so the RLS rule may require using casting functions on the columns within the database if those columns are not also string types.
To implement rules for different data types and attributes, you can use custom variables.
RLS groups🔗
Many different features tie into ThoughtSpot groups: access control, roles, permissions, and row-level security.
Because the name property of the ThoughtSpot group is used within the generated SQL queries, it is best practice to create separate RLS groups for the sole purpose of matching against the values within the tenant ID column in the data warehouse.
You also must consider that the names of any other ThoughtSpot groups, used for access control or granting roles and permissions, must not have names that accidentally match with values in the database columns.
Direct RLS rules🔗
A direct RLS rule directly filters on columns within the Table on which the RLS rule is defined..
A common RLS rule type pattern for a multi-tenanted data warehouse involves each user belonging to a ThoughtSpot group matches the values in a 'customer name' or 'tenant name' column on every table. Alternatively, username can be used if the data is stored at an individual rather than the tenant level.
Direct RLS rule example🔗
Imagine a table in ThoughtSpot that looks like:
| customer_id | product_id | customer_region_id |
|---|---|---|
cust_1 | prod_a | region_1 |
cust_1 | prod_b | region_1 |
cust_2 | prod_a | region_3 |
cust_2 | prod_c | region_4 |
A direct RLS rule would look like customer_id = ts_groups.
If the signed-in user belongs to a ThoughtSpot group named cust_1, ThoughtSpot will add a filtering clause to any SQL generated on this table as shown here:
This will filter the table down to the following rows, after which all other aggregations and other parts of the query will be processed by the data warehouse:
| customer_id | product_id | customer_region_id |
|---|---|---|
cust_1 | prod_a | region_1 |
cust_1 | prod_b | region_1 |
ACL table rules🔗
Rules that reference a separate Access Control List (ACL) table of 'entitlements' for that user or the groups they belong to, which is then JOINed to other tables.
RLS rules on one table object can reference other table objects that are joined within a given connection. This allows a more complex RLS pattern to be used, where an "access control list" or "entitlements" table (ACL table) exists in the database, with a JOIN in ThoughtSpot to the fact or dimension tables.
The RLS rule is defined on the fact or dimension table, but will reference the ACL table, typically by comparing the ts_username variable against a column in the ACL table, which filters the ACL table’s rows to only those defined for the user. The JOIN defined between the ACL table and the other tables then reduces the rows to only those that match the remaining rows in the ACL table.
ACL table pattern is two-step logically:
-
The ACL table of entitlements filters down to just the appropriate rows for the user by comparing the username or the user’s group membership to one column of the ACL table.
-
Any number of the columns in the now reduced ACL table are INNER JOINed to other tables within the data model. This serves to filter the rows on the other tables to only those that match the rows in the ACL table for the user.
The description above is entirely logical - each data warehouse will process the query in its own optimized way.
ACL table example🔗
Let’s take our direct RLS table example from above. What if each user might be filtered on any combination of the three columns? You could do this directly with ThoughtSpot groups. However, you would end up with a large number of groups to manage and assign appropriately:
| customer_id | product_id | custom_region_id |
|---|---|---|
cust_1 | prod_a | region_1 |
cust_1 | prod_b | region_1 |
cust_2 | prod_a | region_3 |
cust_2 | prod_c | region_4 |
The ACL table pattern introduces an additional table, the Access Control List (ACL) table:
| thoughtspot_username | customer_id | product_id | customer_region_id |
|---|---|---|---|
user_a | cust_1 | prod_a | region_1 |
user_a | cust_1 | prod_b | region_1 |
user_b | cust_2 | prod_a | region_3 |
user_c | cust_2 | prod_c | region_4 |
Within ThoughtSpot, you will establish INNER JOINs between each of the columns of the ACL table and the fact or dimension table that needs to be filtered.
With the JOINs set up properly, you place the RLS Rule not on the ACL table, but on the fact and dimension tables. You will be able to reference the fields within either table.
thoughtspot_username = ts_username, where thoughtspot_username is the field on the ACL table.
This will cause queries that reference the fact or dimension to include the INNER JOIN to the ACL table, with the filter condition on the signed-in user.
Updating the ACL table🔗
In the ACL table pattern, the embedding web application must define a process for updating the ACL table in the data warehouse with the correct entitlement rows for each user. This is a separate process that does not involve ThoughtSpot, although it can be combined with the authenticator service used for Trusted Authentication.
Additional resources🔗
See the RLS best practices guide for a comprehensive guide with RLS examples. A data model setup is also available on the ThoughtSpot Community.