How rule-based RLS works

Use rule-based RLS to restrict a group’s access to data.

Users see only accessible row data. Row level security works at the group level and is configured on tables. A table’s RLS rules also apply to any objects with data from that table. So, searches, answers, worksheets, and pinboards that rely on a table’s data fall under RLS rules.

Worksheet queries and RLS

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.

By 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.

You can configure a stricter application of RLS rules to take 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. To do this, 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.

Privileges that allow users to set, or be exempt from, RLS

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

  • Row level security does not apply to them.

  • They can create, edit, and delete RLS rules.

  • They can also disable RLS rules on individual worksheets.

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:

  • Are exempt from row-level security (RLS) rules.

  • Can add/edit/delete existing RLS rules.

  • Can check or uncheck Bypass RLS on a worksheet.

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).

Examples of RLS rules

An RLS rule evaluates against two system variables:

ts_groups

Returns a list of all the groups the current logged in user belongs to. For any row, if the expression evaluates to true for any of the groups, the user can see that row.

Example
ts_groups = 'east'
ts_username

Returns the user with the matching name.

Example
ts_username != 'mark'
You cannot use these variables (ts_groups and ts_username) within an expression. For example, ts_groups = substr(rls_group_name, 0, 3) is valid, but substr(ts_groups,0,3) = rls_group_name is NOT valid.

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 vendor-purchase table such as:

rls example0

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.

RLS rules do not support use of aggregate functions.

Multiple rules and multiple group membership

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).

Best practices for using Rule-Based Row Level Security

Use these best practices for Rule-Based Row Level Security:

  • Use Share as the first level of data access.

    Non-administrative users and groups have no way to access any data without first having it shared with them. So, only share what you need.

    When you share, share worksheets. This is a general best practice. Worksheets simplify the data environment for end users; they only need to choose among a few sources, rather than many tables. Also, one worksheet can also combine data from several tables.

  • Set row level security wherever you want to keep data secure.

    It is always a possible that a particular search only includes data from a single table, and a user will see something they shouldn’t. So, protect your data by setting row level security wherever you want to keep data secure.

  • Explicitly grant access for users that should see all rows.

    As soon as you define a rule on a table for one group, you prevent access by all others outside of that group hierarchy. Subsequent rules should specifically add groups that need access.

  • Keep in mind that multiple rules on a table are additive with or.

    If you are concerned with security, start with very limited access. Then, expand the access as needed.

  • Keep rules simple.

    Complex rules can impact the system performance. So, err on the side of simple rules rather than complex rules with a lot of logic. For example, performance is better with strings that exactly match the group name, instead of using complex string manipulation. Using concat does not usually impact performance.

  • RLS rules do not support use of aggregate functions.

Related information

Was this page helpful?
×