Variables in row-level security

You can now create variables for use in row-level security definitions, assigning values individually to users rather than using the ts_username, ts_groups, or ts_groups_int system variables. The previously defined system variables had a few limitations; they required maintaining multiple group memberships, they sometimes created inefficient SQL statements, and the names for the groups could be confusing if they were the same as values for existing columns (for example, a South group when the data included a South region).

Assigning variables directly to a user provides clearer rule definitions, more efficient SQL, and separation of concerns between organizational groups and data attributes.

Creating row-level security variables

To create a row-level security variable, you must be an admin in all Orgs, or an admin user in a specific Org. You also need access to the Developer playground, as you create and maintain these variables in the Developer playground, and reference them within row-level security rules on a table. For more information on how to use APIs to create variables, see Variable.

Note that for every variable you create, you must set the value for a user as either a value or list of values from the data set. You can also set the value as TS_WILDCARD_ALL, which assigns all values in the column. If you do not assign a value for a created variable, searches including row-level security rules for that variable will generate errors indicating that a mandatory variable has not been defined. This ensures that data is not leaked.

Defining a row-level security rule with variables

Once you have created a variable and assigned values to a specific user, you can create a row-level security rule. Let’s say you created a variable, publisher-name for a data set referencing comic books. Your created variable limits a user to only see comics published by Marvel. This value is maintained in the column publisher in your data set.

To define a row-level security rule referencing this variable, follow these steps:

  1. Navigate to the table you want to define the rule for in the Data workspace. Click the Row security tab and select + Add row security.

  2. Name your rule and click on the first row.

  3. Enter your rule. In this case, you would reference the column name publisher and the ts_var identifier. A good rule would be: publisher = ts_var (publisher-name).

  4. You can log into ThoughtSpot as the user you defined the variable for, and test out your new rule. Any search on the table you set the rule for should reveal only the comics published by Marvel.

Note that we now support and statements in row-level security rules. For example, you could set a rule for a Sales table as region = ts_var (region-name) and product = ts_var (product-name).

Was this page helpful?
×