How rule-based RLS works

You cannot set RLS rules on Worksheets, only on tables. However, administrators can disable RLS on Worksheets that are derived from tables with RLS rules. After RLS rules are disabled, users with access to the Worksheet can see all its data.

When RLS is disabled, default Worksheet queries only take into account RLS rules on tables whose columns appear in the query. Other related tables that may underlie the Worksheet are ignored. This means that not all RLS rules on underlying tables are applied when a user queries a Worksheet.

When RLS is enabled, ThoughtSpot implements a default strict application of RLS rules, which takes into account RLS rules from all the tables underlying the Worksheet. This is recommended if you have key dimension tables that Worksheets rely on but that are not necessarily regularly accessed through query. If you do not want to take into account RLS rules from underlying tables, contact ThoughtSpot Support. Additionally, ThoughtSpot’s RLS logic may override the table join types. If you would like your RLS implementation to always take into account the table join types, contact ThoughtSpot Support.

Users in the Administrators group or with the Has administration privilege have full access to everything in the system. As a result:

If your installation has enabled the Can Administer and Bypass RLS privilege, administrators can also grant Can Administer and Bypass RLS to groups. Members of groups with Can Administer and Bypass RLS:

This behavior is true regardless of whether the privilege is from a direct group membership or indirect (through a group hierarchy, where the user is part of a group that is part of a larger, top-level group with the RLS privilege).

An RLS rule evaluates against two system variables:

Legacy syntax assumes ts_groups is a string and can still be used. For example, ts_groups ='administration' is a valid formula definition. The new syntax is recommended in RLS formulas.

In Worksheet or Answer formulas only the new syntax where ts_groups is a list is supported.

Example 1: determine if a hardcoded string value is present in the ts_groups list

fx := 'single_group_name' in ts_groups

Example 2: determine if a multiple hardcoded string value is present in the ts_groups list. Note that AND is not available in RLS formulas.

fx := 'group1' in ts_groups and|or 'group2' in ts_groups

Example 3: determine if a column value is present in ts_groups

fx := column in ts_groups

Example 4: determine if all the ts_groups values are defined in the column

min( if(column in ts_groups) then 'true' else 'false') → this would evaluate to false if NOT all of ts_groups is contained in column and true otherwise.

Example 5: determine if at least one of the ts_groups values is defined in the column

max( if(column in ts_groups) then 'true' else 'false') → would evaluate to true if at least ONE of ts_groups values is contained in column, and false otherwise.

ThoughtSpot filters a table’s rows by evaluating a rule against the authenticated user.

A rule is an expression that returns a boolean, TRUE or FALSE. If the rule evaluates to TRUE, a user can see that row. If the rule evaluates to FALSE for the user, then the user cannot view the data and instead they see the message No data to display.

Rule expression can be implicit or explicit. And rules may or may not contain logic. A simple implicit RLS rule has the format:

COLUMN_FILTER = ts_groups

An example of an explicit rule that contains logic would be:

if ( COLUMN_FILTER ) then true else false

Rules can also reference tables other than the table you are securing.

Consider a simple RLS rule example. Your company has a vendor-purchase table like the following image:

You want to give your vendors the ability to see trends in company purchases. You give vendor personnel access to ThoughtSpot and add them to self-titled vendor groups. So, all users from the Starbucks vendor are in the Starbucks group and all users from round table are in the Round Table group. Then, you set a Row security on the vendor-purchase table as follows:

VENDOR = ts_groups

Only users in Starbucks group see starbucks data and so forth. Rules ignore case inconsistencies and spaces are evaluated so round table in the data matches the Round table group but not a group named RoundTable .

Rules can be simple or they can incorporate logic such as if/then rules. For example, vendors should see their own data but your accounts payable group needs to see all the vendor data:

VENDOR = ts_groups or 'Accounts Payable' = ts_groups

This rule continues to work as you add data from new vendor or team members to Accounts Payable. In this way, a well-written rule is self maintaining, meaning you don’t have to revisit the rule as your system changes.

You can also create rules that reference tables other than the table you are securing. For example, if you have a sales table and store dimension table, you can use attributes from the store table to secure the sales table.

You can define multiple rules on table. In this case, ThoughtSpot treats the rules as additive. That is, they are applied using an OR operator. If any of the rules evaluate to true for a user on a row, that row’s data is visible.

If a user is a member of multiple groups, the user can see all the rows that are visible to all of their groups. The most permissive policy is used.

Members of groups with Can Administer and Bypass RLS are exempt from row-level security (RLS) rules. This is true regardless of whether the group membership is direct or indirect (through a group hierarchy).

Use these best practices for rule-based row-level security: