Spotfire Server: How to Apply Row Level Security on multiple columns

How to apply Row Level Security on multiple columns in Spotfire.

Solution:

Row level security is applied to a data set to restrict data access per user. In Spotfire, row level security can be setup using Personalized Information links. Row level security can be easily applied when security is based on only one column or more columns with "AND" condition between them. For example:

1. When security needs to be applied only on one column:

2. When security needs to be applied on two or more columns with AND condition, i.e. "user abc has access to rows in dataset with Region = Americas AND Business = Finance".

See Personalized Information links and KB - How to setup caching for reports with personalized information links with row level security for more information.
 

But the above approach will fail, if you are looking to apply OR condition between Region and Business or have security mapping tables of the following formats:

1. When security needs to be applied on two or more columns with OR condition, i.e. "user abc has access to all rows in dataset with Region = Americas, Asia (Any Business) AND Business = Finance, Healthcare (Any Region) AND combination like Region should be Europe and Business should be Transportation":

2. When security needs to be applied on two or more columns with OR condition, i.e. "user abc has access to all rows in dataset with Region = (Americas OR Asia) AND Business in (Finance, Healthcare, Transportation)":

Resolution:

You cannot directly apply OR condition when setting up data table relations or inserting columns or matching columns. Instead the following approaches can be used:
 

Approach 1:
If your requirement is as mentioned above in first point, then you can use the following approach:

1. You can follow steps 1-6 as mentioned in KB article How to setup caching for reports with personalized information links with row level security

2. Once the actual data set,for example "AllData", has been imported then create another data table based on AllData, for example "PivotedData", which would just have unique combination of Region and Business columns: 

Select Data > Add...
     Source: Data table from current analysis
       Data table: AllData     
       Update behavior: Automatic     
         Added transformations         
           Transformation name: Pivot         
               Row identifiers:             Region             Business         
               Value columns and aggregation methods: (None)         
               Column titles: (None)         
               Column naming pattern: %M(%V) for %C         
               Transfer columns and aggregation methods: (None)         
               Transfer column naming pattern: %A(%T)

3. Then add another data table which will contain security data and name it as "SecurityData".

4.  Add calculated columns while transforming data: 

Select Data > Transform data...
     Added transformations
              Transformation name: Calculate new column         
                  Column name: BusinessMatch         
                  Expression: case  when [Region] is null then [Business] end         
               Transformation name: Calculate new column         
                   Column name: RegionMatch         
                   Expression: case  when [Business] is null then [Region] end 

These calculated columns will contain only data where either Business or Region is null.

5. Now add column from PivotedData to SecurityData table and match only on Region columns: 

Select Data > Add...
     Source: Data table from current analysis
          Data table: PivotedData     
          Update behavior: Automatic     
          Data was added as new columns in data table 'SecurityData'     
          Matching behavior: Tries to match the specified columns when data is loaded     
          Matched columns: RegionMatch – Region     
          Added columns: Business     
          Ignored columns: (None)     
          Join method: Left outer join     
          Treat empty values as equal: No 

6. Now add column from PivotedData to SecurityData table and match only on Business columns: 

Select Data > Add...
     Source: Data table from current analysis
          Data table: PivotedData     
          Update behavior: Automatic     
          Data was added as new columns in data table 'SecurityData'     
          Matching behavior: Tries to match the specified columns when data is loaded     
          Matched columns: BusinessMatch – Business     
          Added columns: Region     
          Ignored columns: (None)     
          Join method: Left outer join     
          Treat empty values as equal: No    

The above inserted columns will fetch corresponding Region and Business based on matched Business and Region respectively i.e. if Region columns are matched, then all Business values would be fetched where region is Americas. Similarly, when Business columns are matched, then all Regions values would be fetched where Business is Finance.

7. Now, add calculated columns which would be used to set up data table relation between AllData and SecurityData tables. If Region/Business columns inserted above are null then use original Region/Business column (when both values are present eg: Region = Europe and Business = Transportation) otherwise use inserted column values: 

Data > Add calculated column...
     Column name: Region Filter     
     Expression: SN([Region (2)],[Region]) Data > Add calculated column...    
      Column name: Business Filter     
      Expression: SN([Business (2)],[Business]) 

8. Setup the data table relation between AllData and SecurityData tables: 

Data > Data table properties > Relations
     Added relation:
              [SecurityData].[Region Filter] – [AllData].[Region]
              [SecurityData].[Business Filter] – [AllData].[Business]   

9. Open the Filter Panel, go to AllData table > Click on related tables icon > Filtering in Data table > Select Include Filtered Rows Only.

10. If you are caching this analysis using schedule updates, Edit data table properties to exclude SecurityData table from schedule updates so that it is loaded for each user.
    Data >> Data Table Properties >> Schedule Updates >> Select the checkbox for SecurityData, PivotedData, PivotedData (Reload the following data for each user).
 

Approach 2: 
If your requirement is similar to that mentioned in second point of Details section, then you can use the following approach:

1. All steps will remain the same as Approach 1 except steps 4, 5, 6 and 7. Step 4 is not required now as there is no combination of security data involved.

2. Step 5 and Step 6 will have column matches on original Region and Business columns from SecurityData and PivotedData tables.

3. Step 7 calculated columns will have different expression. Now as Regions/Business present in SecurityData table are only required, the rest of them can be nullified: 

Data > Add calculated column...
     Column name: Region Filter     
     Expression: case  when Find([Region (2)],UniqueConcatenate([Region]))<>0 then [Region (2)] end 
Data > Add calculated column...
     Column name: Business Filter     
     Expression: case  when Find([Business (2)],UniqueConcatenate([Business]))<>0 then [Business (2)] end 

Video: Setting up Personalized Information links https://www.youtube.com/watch?v=puc9gjv8_VI&feature=youtu.be