How to Apply Row Level Security on multiple columns in TIBCO Spotfire

Products	Versions
Spotfire Analyst	10.0 and higher

Description

Row level security is applied to a data set to restrict data access per user. In TIBCO 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:
User-added image

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 as Americas and Business as Finance.
User-added image

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 as Americas, Asia (Any Business) and Business as Finance, Healthcare (Any Region) and combination like Region should be Europe and Business should be Transportation:
User-added image

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 as Americas or Asia and Business as either Finance, Healthcare or Transportation only:
User-added image

Issue/Introduction

This article provides an overview on applying row level security with multiple columns

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

Example Spotfire .dxp files for both Approach 1 and Approach 2 are attached for reference. Note: In the .dxp files, instead of using security Information link, data is imported from from clipboard so it opens up without any error.