How rule-based RLS works
Row-level security (RLS) works at group or user 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 Liveboards 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.
By default, ThoughtSpot implements a 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.
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:
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.
Returns the user with the matching name.
You cannot use these variables (
ThoughtSpot filters a table’s rows by evaluating a rule against the authenticated user.
A rule is an expression that returns a boolean,
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
Rules can be simple or they can incorporate logic such as
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
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
|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
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 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
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
concatdoes not usually impact performance.
RLS rules do not support use of aggregate functions.
To learn the procedure you follow for setting a rule, set RLS rules.
For a list of operators and functions you can use to build RLS rules, see Row-level security rules reference.
For information on bypassing rules on a Worksheet, see Change inclusion, join, or RLS for a Worksheet.