There are many scenarios where a model contains data for many distinct groups, such as customers, and you want viewers to only have access to the data that pertains to their specific customer. This is easily implemented with row level security, when there is a data column that uniquely identifies each customer.
But what if you have a requirement that certain types of viewers should see all columns of data per row, while others should not have access to certain columns? Here is an approach that can be implemented right out of the box in Sisense. The one prerequisite is that any table that contains sensitive data must have a column that uniquely identifies every row, i.e. a primary key column.
For every table that contains sensitive data in certain columns, create a new table that contains the original table's primary key column, a binary "switch" column with a name like [Access Allowed] and a column for each of the sensitive columns. Remove the sensitive columns from the original table. Modify the import query to load only the non-sensitive columns in the original table. Modify the import query to load the new table with the primary key and sensitive columns of the original table twice, once with [Access Allowed] set to 1 and the second time with 0 and blank (or null) for all the sensitive columns. Associate the new table to the original table by primary key column Create a dimension table for secure access with the column [Access Allowed]. One row with value 0, one row with value 1. Associate the dimension table with the sensitive data table by the [Access Allowed] column. Set up a data security rule on this column, assigning the secure user group to value 1 and the general group to value 0 If there is a hierarchy greater than two levels of column-level data security, this model can be extended to create additional one-to-one related tables to the original table.
... View more
