Explains what the feature is or what its benefits are to the user or customer.
When working with worksheets and row level security, you need to understand how joins are applied. This section gives some examples to explain the interaction between these two concepts.
To understand how the worksheet join rule is applied, you first need to understand worksheet schemas and the concept of root tables.
Worksheet Schemas and the Root Table
When you create a worksheet, you're effectively creating a self-contained schema made up of the tables in the worksheet and the relationships (joins) between the tables. The joins (represented by arrows in the diagram) reflect the primary key/foreign key relationships between the tables in the underlying database schema. The concept of the "root" table in the worksheet schema becomes important for understanding how the joins are applied when searching. In this context, the root table is specific to the schema structure defined for that worksheet.
Suppose you created a worksheet with a schema like the example in the diagram:
Apply All Joins
When you choose Apply All Joins when creating a worksheet, all joins between the tables get pre-applied, whether or not there is row level security present. This is the simplest case.
Apply Joins Progressively Without Row Level Security
What if you choose Apply Joins Progressively when creating a worksheet, and none of the tables in ThoughtSpot have row level security applied? In this case, the joins will be applied using the worksheet schema as in these examples:
Example 1: Progressive join with tables from one branch
Joins are applied from the root table of the worksheet down to the lowest leaf table involved in the search. If the worksheet includes all of the tables in the diagram, but when doing a search we choose only columns from Products and Departments, the joins get applied starting at the root table and moving down to all of the tables included in the search. That is, joins from Sales to Products to Departments (Join 1 and Join 2) will be used.
Example 2: Progressive join with tables from different branches
Joins are applied from the root table of the worksheet, moving down each branch, to the lowest leaf tables involved in the search. If we searched on columns from only the Suppliers and Warehouses tables, the join path would traverse down the tree to reach the lowest leaf table in each branch. That is, the joins applied would be:
- Join 1
- Join 3
- Join 4
- Join 5
Apply Joins Progressively With Row Level Security
- If the row level security is applied only outside the scope of the worksheet schema, the join behavior is the same as when there is no row level security in the system.
- If the worksheet contains even a single table with row level security, non-progressive joins (Apply All Joins) will be used if the join path includes the table with row level security. Remember that the join path begins at the root table and moves down to each of the leaf tables included in the search. So a table with row level security may occur in the join path even if its columns are not included in the search.
Example 3: Progressive joins when a table outside of the join path has row level security
Assume the table Departments has row level security applied, so that department managers can only see the department they manage. If we did a search on the tables Suppliers and Products, Progressive Joins would be used. The join path would be Join 1, Join 3. The Department table row level security would not apply, since the Departments table is not in the join path.
Example 4: Progressive joins when a table in the join path has row level security
Assume now that the table Products has row level security applied, so that buyers could only see the products they order. If we did a search on the tables Departments and Warehouses, Apply All Joins would be used, so the Products row level security would apply. The join path would be Join 1, Join 2, Join 4, and Join 5. This join path takes us through the Products table, which explains why its row level security would affect the search results, even though no columns from the Products table are included in the search.
Progressive joins with chasm trap work slightly differently. To understand this, a discussion of how chasm trap searches are executed is helpful. A chasm trap search gets broken down into multiple smaller queries that are merged before the result is returned. If any of these smaller queries includes a column on which row level security applies, it brings only the results that satisfy the row level security for the current user. Then the component queries are merged to return the end result. At this merge operation, the most strict security is used, meaning that all query components are pruned to match the query component on which row level security is applied.
So in effect, for tables joined over a chasm trap, non-progressive joins are used if any of the component columns have row level security applied to them. That is, all joins are applied up front, taking row level security into account.