RLS rules
ThoughtSpot Row Level Security Rules define how the user’s username or the names of the groups they belong into WHERE clauses on the SQL queries generated.
The two basic patterns for RLS Rules are direct RLS rules that reference a column on the table the rule is placed on and ACL table rules that reference a separate table of entitlements within the same database.
If neither of these patterns is easily implemented, please consider the ABAC via tokens method of RLS, available starting in ThoughtSpot 9.11.
RLS rules overview🔗
RLS rules are defined within ThoughtSpot on table objects, and automatically extend to all worksheets, saved answers, and Liveboards based on that table, every time.
RLS rules are defined using either the ts_username variable or ts_groups variable. The RLS rules translate into WHERE clauses in any SQL query generated from that table object, with the variables expanding into the details of the signed-in user. ts_groups turns into the set of group name properties of the ThoughtSpot groups the user belongs to.
The ts_username special value translates to t1.fieldName = 'users-username' within the generated queries.
The ts_groups special value translates to t1.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 the IN.
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. The ABAC via tokens method allows for specifying the data type of the attributes sent in.
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.
The user and group creation REST APIs can be utilized to programmatically create and assign the appropriate groups to be used within RLS rules
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 column within the table that the RLS rule is defined on.
A common RLS rule type pattern for a multi-tenanted data warehouse involves each user belonging to a ThoughtSpot group match 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 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 like:
WHERE t1.customer_id IN ('cust_1', …)
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 |
Access control list (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 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 but 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🔗
A comprehensive examples and best practices guide to RLS data model setup is available on the ThoughtSpot Community.