Date Posted:
Product: TIBCO Spotfire®
Product: TIBCO Spotfire®
Problem:
How to Apply Row Level Security to Excel data
Solution:
This article describes how to apply row level data security for a data table that has data from a file like Excel, csv etc. and security needs to be applied based on group of users. For example, all users in group A should not have access to data in the file but they need access to the folder/analysis file, whereas the rest of users should be able to view the data. Example data:
| Actual Data |
|---|
| Finance Record 1 |
| Sales Record 1 |
| Sales Record 2 |
| Sales Record 3 |
| Finance Record 2 |
Option 1: Users of Group A restricted by listing in a WHERE clause condition of a Personalized Information Link
1) Add Excel data to the analysis file in DataTable1
2) Insert one Calculated Column with expression as "1", called for example "Val" (this will give all rows the value 1)
3) Create a personalized Information Link with only %CURRENT_USER% and 1 as the two columns, with users of Group A in the WHERE clause. Below is the example custom sql:
SELECT distinct
%CURRENT_USER% AS "NAME",
'1' AS "Val"
FROM
"TSS7110WIN_DEMO"."dbo"."BASEBALL" B1
WHERE
'%CURRENT_USER%' not in ('user1','user2') and
<conditions>
4) Create DataTable2 in the analysis with data from this Information Link. This Information Link will pull one row (containing current user and value 1) only if that user is not part of Group A. 5) Create a relation between DataTable1 and DataTable2 on the 'Val' column: Edit > Data table properties > Relation > Select DataTable1 and DataTable2 and select the 'Val' column in both tables
6) Open the Filter Panel, and beside the DataTable1 name there will be one icon for the filtering in related data tables (see the reference Filtering in Related data tables for more details). Click on that icon and select: Filtering in DataTable2 > Include filtered rows only
Now, when users not in Group A open the analysis, DataTable2 will have records and therefore DataTable1 will be populated with data. Conversely, when users of Group A open the analysis, DataTable2 will be empty which will then cause DataTable1 to also be empty.
Option 2: Users of Group A restricted by listing in an Excel file
1) Add Excel data to the analysis file in DataTable1
2) Insert one Calculated Column with expression as "1", called for example "Val" (this will give all rows the value 1)
3) Create an Excel file in a secure network location (accessible by users of the analysis, for example \\myserver\myfolder\myfile.xlsx) with the list of users in Group A and add this Excel data to the analysis file in DataTable2
4) Create a personalized Information Link with only %CURRENT_USER% and 1 as the two columns, without filtering any user in the WHERE clause. Below is the custom sql:
SELECT distinct %CURRENT_USER% AS "NAME", '1' AS "Val" FROM "TSS7110WIN_DEMO"."dbo"."BASEBALL" B1 WHERE <conditions>5) Create DataTable3 in the analysis with data from this Information Link. This Information Link will pull one row (containing current user and value 1)
6) Select Insert > Columns, select from DataTable3 to DataTable2, matching on the 'Name' column with 'Left outer join' as the join method, and select 'Val' as the column to insert.
7) Insert Calculated Column in DataTable2, for example named 'Val2' with expression:
case when [Val] is null the 1 else 0 end8) Create a relation between DataTable1 and DataTable2 on the 'Val2' column: Edit > Data table properties > Relation > Select DataTable1 and DataTable2 and select the 'Val2' column in both tables
9) Open the Filter Panel, and beside the DataTable1 name there will be one icon for the filtering in related data tables (see the reference Filtering in Related data tables for more details). Click on that icon and select: Filtering in DataTable2 > Include filtered rows only
Now, when users not in Group A in the Excel file open the analysis, DataTable2 will have [Val] column null and therefore DataTable1 will be populated with data. Conversely, when users of Group A open the analysis, [Val] column in DataTable2 will have value due to which DataTable1 will not populate data. An advantage of this approach is the list of users is in the Excel which can have its access restricted (although read access is required for the report to be able to load) and there is no need to make any changes in Information Link when the list of users changes.
For both options, if you are going to cache the dashboard using Scheduled Updates, exclude the data table using personalized Information Link from being cached by Edit > Data Table Properties > Select Personalized information link table > Scheduled Updates> Check the table for reloading it for each user. See "How to setup caching for reports with personalized information links with row level security" for more details
Note: You can use any data source to create the Information Links mentioned above, since we modify the SQL as mentioned. Doc: Create Data Table Relations Doc: Creating Information link Doc: Add Data Tables Doc: Insert Columns Doc: Filtering in Related data tables KB: How to setup caching for reports with personalized information links with row level security