ABAC via RLS with variables

Table of Contents

Overview
Create formula variables
Add or update RLS rules with variable references
- RLS rule examples
Create an ABAC token request with variable attributes
Verify the entitlements
Additional resources

Attribute-Based Access Control (ABAC) is an access control model in which security entitlements are assigned to a ThoughtSpot user directly as a set of attributes with lists of values, rather than relying on a JOINed entitlements table within the data model.

ThoughtSpot allows assigning attribute values to a user at session creation time by adding the values to the user’s access token request.

RLS rules are defined on table objects, which bind dynamically generated WHERE clauses to any generated query. Within RLS rules, attributes are referenced by their variable names using the ts_var() function to dynamically filter data and enable user-specific security policies.

Overview🔗

RLS rules have a defined set of system variables such as ts_username or ts_groups. Once ABAC via RLS is enabled, the ts_var() formula is available within the RLS rule editor to refer to any defined Formula variable within ThoughtSpot.

Formula variables are custom variables defined within ThoughtSpot that enable dynamic and context-aware logic in RLS rules. They are assigned at the user level during session creation for the ABAC pattern, although they can be set at Org and data model levels as well.

Supported operators🔗

RLS rules that use the ts_var() function support only equal to (=) and not equal to (!=) operators. Internally, = translates to IN (…) and != translates to NOT IN (…) in the generated SQL, because ts_var() returns a list of values.

Inequality comparison operators such as > (greater than), < (less than), >= (greater than or equal to), and <= (less than or equal to) are not supported and do not resolve in RLS rules.

Numerical operators, such as addition (+), subtraction (-), multiplication (*), and division (/), are also not supported with ts_var(). You cannot perform arithmetic on the value returned by ts_var() or use it within a calculated expression in an RLS rule.

Implementation steps🔗

The ABAC implementation with formula variables and RLS rules includes the following steps:

Creating formula variables
To generate tokens with variable attributes, the variables must be available in ThoughtSpot. To create variables, use the Variable REST API.
Adding RLS rules with formula variables
When defining an RLS rule with variables, use the ts_var function. These RLS rules will apply to the Models, Liveboards, and other objects derived from that Table. A formula variable must be defined before it can be used in an RLS rule.
Creating a token request with variable attributes
Attribute values are assigned to users by requesting a token using the /api/rest/2.0/auth/token/custom REST API endpoint.
Verifying entitlements
To ensure data security rules are applied, check user entitlements and verify that they are translated accurately during query generation. Variables and their assigned values are visible to administrators using the /api/rest/2.0/template/variables/search endpoint with the response_content parameter set to METADATA_AND_VALUES.

Indexing🔗

Several features within ThoughtSpot, such as autocompletion in Search on values within columns, use ThoughtSpot indexing. Due to the runtime nature of ABAC tokens, ThoughtSpot indexing will not be restricted by the values supplied in a token. This means the indexed columns may expose values in search suggestions or autocompletion that a user should not see, even if ABAC filters would block access to the underlying data. To prevent this, you can do one of the following:

Disable indexing for columns and fields that must be restricted by ABAC. You may also want to disable indexing on all sensitive columns.
Define an RLS rule on those fields, since RLS is enforced at the indexing layer and will secure suggestions and sample values.

Create formula variables🔗

Formula variables must be defined in ThoughtSpot before they can be used in any RLS rule via the ts_var() formula.

To view the variables available on your instance, use the POST /api/rest/2.0/template/variables/search API call.

To create a new variable, use the /api/rest/2.0/template/variables/create API endpoint.

To configure formula variables for all Orgs on your instance or the Primary Org, cluster administration privileges are required. Org administrators can configure formula variables for their respective Orgs. Users with the CAN_MANAGE_VARIABLES (Can manage variables) role privilege can also create and manage variables for their Org context.

The /api/rest/2.0/template/variables/create API endpoint allows creating formula variables for the following data types:

VARCHAR
INT32
INT64
DOUBLE
DATE
DATE_TIME

During variable creation, specify the data_type.

Formula variables for BOOLEAN and TIME data types are not supported.

{
  "type": "FORMULA_VARIABLE",
  "name": "country_var",
  "is_sensitive": false,
  "data_type": "VARCHAR"
}

The variable update API (/api/rest/2.0/template/variables/{identifier}/update) allows updating the variable name and other attributes of the variable definition, but not the values assigned to users or other principals.

Variable values are set either by generating a token using the /api/rest/2.0/auth/token/custom API endpoint or via the Update Variable Values REST API.

Add or update RLS rules with variable references🔗

RLS rules are defined on Table objects:

Navigate to the Data workspace and click the Table for which to define RLS rules.
Click Row security and then click + Add row security.
In the Row Security Editor, define the rules. To reference the formula variable in the rule, use the ts_var(varName) function, with no quotes around the formula variable name. For example, to limit a column called region to the values set in a formula variable called region_var, set the RLS rule to: region = ts_var(region_var).

Note	Variable values are set through the token request. The RLS rule specifies how the values will be used in the generated RLS `WHERE` clauses in the SQL.

RLS rule examples🔗

RLS rules must always evaluate logically to SQL boolean TRUE or FALSE.

If a user has no variable values for a given formula variable, this results in FALSE.

RLS rule with a single variable reference🔗

In this formula example, country refers to the "country" column in the data table, and country_var is the variable.

country = ts_var(country_var)

If country_var is assigned a single value, the user is permitted to view only rows where the country column matches that value.

If country_var is assigned multiple values, the formula translates to country IN ('value1', 'value2', …). The query engine interprets = as the IN clause in this case and returns rows that match these values; for example, WHERE country IN ('Australia', 'Germany').

RLS rules with multiple variables🔗

The RLS rules support the AND operator, which means that you can combine multiple conditions in a single RLS rule, so that a row is accessible only if all the specified conditions are met.

The following rule restricts data access to rows if the country column in the data table matches the value assigned to country_var and the Department column matches the value assigned to department_var for that user.

country = ts_var(country_var) AND Department = ts_var(department_var)

The rule in this example restricts data access to rows where the region column in the table matches the value assigned to region_var and the product column matches the value assigned to product_var.

region = ts_var(region_var) AND product = ts_var(product_var)

Group override rule with variable-based check🔗

In any security formula you build, you may want a clause that gives access to all data to certain groups. In the rule definition, you can include system variables, such as ts_groups, to build your preferred logic:

In this example, users can access data if they are in the "data developers" group, or if the Department column matches the value assigned to their department_var variable.

'data developers' in ts_groups OR Department = ts_var(department_var)

Variables with numeric and date data types🔗

The following rule restricts access to rows where the country column matches one of the values provided by the country_var variable, for a VARCHAR column.

country = ts_var(country_var)

The following rule restricts access to rows where the Revenue column matches one of the values provided by revenue_cap_var. Use the to_double() cast function to ensure the string values from the token are compared correctly against a numeric column.

Revenue = to_double(ts_var(revenue_cap_var))

The following rule restricts access to rows where the date_column matches one of the values provided by the date_var variable, for a DATE column.

date_column = ts_var(date_var)

Range comparisons, such as filtering for dates before or after a value provided by a variable, are not supported because RLS rules with ts_var() only support the = and != operators.

Create an ABAC token request with variable attributes🔗

To set or update variable values for a user, use the POST /api/rest/2.0/auth/token/custom endpoint when logging in the user.

You can also use the /api/rest/2.0/template/variables/{identifier}/update-values endpoint for bulk operations or targeted resets.

The variable attributes defined in the token request take effect only if they are referenced in an RLS rule. If the variables are not used in any formula or RLS rule, they have no impact on data access. Before generating the request with variable attributes, ensure that the variables are added to the RLS rules for the table.

In the token request, include the following properties along with the username and secret_key:

variable_values
persist_option

Optionally, you can specify the objects and org_identifier to set the scope of access control.

Variable values🔗

The variable_values array requires the following parameters:

name
String. Name of the formula variable. The formula variable referenced on the token request must be available in ThoughtSpot and included in the RLS rule.
values
Array of strings or numeric values. When assigning values, ensure the data format of the values matches the data type set for that variable. If you are adding a variable to filter by country with the VARCHAR data type, specify string values as shown in this example:

"variable_values": [
    {
      "name": "country_var",
      "values": [
        "Japan",
        "Singapore",
        "Australia"
      ]
    }
 ]

All values are passed into the token as arrays of strings, even if the column is a numeric or date type in ThoughtSpot and the database. The column data type will be respected in the query issued to the database.

Using wildcard values🔗

ThoughtSpot provides the following wildcard values that simplify "allow all" and "deny all" patterns without changing your RLS rules:

TS_WILDCARD_ALL
Assigns all values in the column, providing access to all data. Ensure that the RLS rules are clearly defined to avoid unintended data exposure.
TS_WILDCARD_NONE
Resolves the rule as false and denies access to data.

To "allow all" but restrict access to specific data, you can use these wildcards in the following way:

in_department_var = TS_WILDCARD_ALL. Allows access to all data.
not_department_var = ['Sales', 'Engineering']. The user is allowed to access all data except the departments specified in the variable values.

Similarly, you can deny access to all data and allow access only to specific departments:

in_department_var = ['Sales', 'Engineering']. The user is allowed to access all the departments specified in the rule.
not_department_var = TS_WILDCARD_NONE. The user cannot access any data except the allowed departments.

Denying all by default🔗

For every variable included in the token request, you can assign one or more values. All variables referenced in RLS rules are required. If a variable is not assigned any value or if its value is set to TS_WILDCARD_NONE, query generation fails with the following error:

Error in loading data
No values are assigned to some or all Formula Variables

Formula variable error

Due to this error, no data is returned, effectively denying all data access for the user.

Persist options and session-based rules🔗

Variable attributes must be persisted for them to apply.

To append or replace attribute values for a user, use one of the following persist_option values in the token request:

REPLACE
Replaces the full set of existing variable assignments with the new values from the token request.
APPEND
Adds the attribute values defined in the API request to the existing attribute values for the user.

If you don’t want to append or replace any attribute values, do not pass any details about the variable in the token update request.

Note

The ABAC implementation with RLS and formula variables does not support session-based rules. Do not use the legacy persist_option value of NONE.
"persist_option": "RESET" attribute is also a legacy value and is not supported.
When creating a token request with "persist_option": "REPLACE", the presence or absence of specific keys in your JSON payload determines whether the existing security rules are retained, updated, or deleted.

Resetting a user or a variable🔗

Passing an empty array along with a formula variable name in the token request does not reset the attribute values for that formula variable for that user.

To change formula variable attributes of a user, especially to set entitlements to an empty set, use the /api/rest/2.0/template/variables/{identifier}/update-values API endpoint.

Warning

The RESET operation erases all variable value settings for all users for a variable, regardless of any variable_value_scope provided. Use with caution; it is a complete reset.

A formula variable exists across all Orgs in ThoughtSpot, but the values are recorded on a per Org and per Principal basis. To use the Update Variable Values REST API, you’ll need to provide the org_identifier as well as the username as principal_identifier and set principal_type to USER as seen below:

{
  "variable_assignment": [
    {
      "variable_identifier": "country_var",
      "variable_values": [],
      "operation": "REPLACE"
    }
  ],
  "variable_value_scope": [
    {
      "org_identifier": "Prod",
      "principal_type": "USER",
      "principal_identifier": "[email protected]"
    }
  ]
}

The above command results in [email protected] being denied any access when country_var is used in an RLS rule.

Note	Do not use the `/api/rest/2.0/users/{user_identifier}/update` endpoint, as it does not support updating variable values.

Session-based ABAC and one-time users🔗

The ability to set variable values only for the current session, previously achieved by setting persist_option: NONE, is not supported with RLS via ABAC.

For session-based rules, create dedicated one-time user accounts for your application users and apply persisted rules. Unless specifically stated in your contract, there is no limit to the number of users that can be created and provisioned in ThoughtSpot.

This approach ensures that Liveboard schedule attachments enforce security rules and deliver only secured output to your end users. When combined with cookieless authentication, this configuration addresses all use cases that previously relied on session-based JWT.

You can simplify user provisioning and programmatically manage user creation and deletion workflows using ThoughtSpot’s REST APIs.

Variable scope🔗

To restrict the scope of variable attributes and rules to a specific Org context and object, define org_identifier and objects.

Apply to specific objects🔗

To apply variable entitlements to a specific object, specify the object ID in the objects array as shown in this example. The API supports only the LOGICAL_TABLE object type:

"objects": [
   {
     "type": "LOGICAL_TABLE",
     "identifier": "9b751df2-d344-4850-9756-18535797378c"
   }
]

If the object ID is not specified in the API request, the variable values will be applied to all formulas and rules that use those variables, across all objects in the Org for that user.

The following example shows the request body for generating a token with formula variable attributes scoped to a particular Model object:

{
  "username": "UserA",
  "validity_time_in_sec": 300,
  "persist_option": "APPEND",
  "auto_create": true,
  "secret_key": "f8aa445b-5ff1-4a35-a58f-e324133320d5",
  "variable_values": [
    {
      "name": "product_var",
      "values": [
        "TS_WILDCARD_ALL"
      ]
    },
    {
      "name": "country_var",
      "values": [
        "Japan",
        "Singapore",
        "Australia"
      ]
    }
  ],
  "objects": [
    {
      "type": "LOGICAL_TABLE",
      "identifier": "35aa85fe-fbb4-4862-a335-f69679ebb6e0"
    }
  ]
}

Apply to Org context🔗

The org_identifier attribute in the token request specifies the Org context for the user session and entitlements.

If the org_identifier parameter is not defined in the token request, the token is issued for the user’s last logged-in Org. For new users, the token will be assigned to the default Org on their instance.

To apply variable entitlements to a user session, you must ensure that the RLS rules with variables and relevant objects are available in the Org context specified in the token request.

Note

ThoughtSpot access tokens are JWTs that are compressed by default to handle large payloads. It is recommended to keep the compression enabled to ensure all tokens can get properly interpreted by the application regardless of their size, and to obfuscate the values passed in the payload. If you want to disable it, contact ThoughtSpot Support.

Verify the entitlements🔗

You can also use the POST /api/rest/2.0/template/variables/search API call to get the list of variables assigned to a specific user, Org, and Model.

Set the response_content parameter to METADATA_AND_VALUES to see the values that have been set for each user per Org:

{
  "record_offset": 0,
  "record_size": 50,
  "response_content": "METADATA_AND_VALUES"
}

This results in the following response:

[
  { "id":"d3abc655-b706-4f91-90ea-cc26bc966d46",
    "name":"country_var",
    "variable_type":"FORMULA_VARIABLE",
    "sensitive":false,
    "values":[
      {
        "value": null,
        "value_list": ["CustomerC","CustomerD"],
        "org_identifier": "Prod",
        "principal_type": "USER",
        "principal_identifier": "[email protected]",
        "model_identifier": null,
        "priority": null
      },{
        "value": null,
        "value_list": null,
        "org_identifier": "Prod",
        "principal_type": "USER",
        "principal_identifier": "[email protected]",
        "model_identifier": null,
        "priority": null
      }
     ]
  }
]

To verify the entitlements:

Log in to your app with a user account that does not have the Can administer and bypass RLS privilege, and initiate the user session with the ABAC token.
Access a Liveboard, Saved Answer, or start a new search query.
Inspect the generated SQL for your query or visualization.
Verify the rules and check whether only the permitted rows are displayed.
Verify whether data is displayed when no variable values are defined.
Repeat the steps for different variable assignments to confirm the RLS rule is enforced as expected for all scenarios.

Additional resources🔗

For information about variables and variable APIs, see Variables and Variable APIs.
For information about RLS rules, see RLS rules and ThoughtSpot product documentation.