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 underly 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 Customer 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).
Examples of RLS rules
An RLS rule evaluates against two system variables:
Function | Description | Examples |
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. | ts_groups = 'east'
|
ts_username |
Returns the user with the matching name. | ts_username != 'mark' |
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:
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.
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.
Related information
-
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.