Row Level Security in Spotfire using Information Links

Introduction

This article explains how to implement row level security in Spotfire using information links

Requirements

You need to have two data tables in your database, one which contains all of your data (AllData) and one which contains the security mapping which will be used to limit the data returned (SecurityMapping) as well as the corresponding Information Designer elements. For example:

Step by Step instructions

1. Create column elements for AllData and SecurityMapping.
2. Create a Filter element in Information Designer for "UserName" column in "SecurityMapping" table - Name it UserName_Filter.
   • Change the expression to "%1=%CURRENT_USER%" .
   • Make sure that %CURRENT_USER% and UserName are in the same format, e.g., "user@email.com" versus "user".
     note: You can verify this by running an information link and checking the SQL in sql.log.
3. Create an Information link - AllData_IL .
   • Add both columns (MatchColumn, ActualData) from AllData table.
4. Create an Information link - SecurityMapping_IL .
   • Add both columns (MatchColumn, UserName) from SecurityMapping table.
   • Add filter created earlier - UserName_Filter .
5. Add data table to your analysis.
   • File >> Add Data Table >> Information Link >> AllData_IL .
6. Insert columns from SecurityMapping information link.
   • Insert >> Column >> Add Columns From >> Information Link >> SecurityMapping_IL >> Match on "MatchColumn" >> Inner Join (Rows matching in both original and new data only).
7. Edit data table properties to exclude "SecurityMapping" from schedule updates so that it is loaded for each user.
   • Edit >> Data Table Properties >> Schedule Updates >> Select the checkbox for SecurityMapping_IL (Reload the following data for each user).
      

Now, if you want to use Scheduled Updates, they can be used together with personalized information links to achieve row level security using the "Reload the following data for each user" functionality. This has the performance benefits of caching the primary data table in Scheduled Updates as well as the security benefits of personalized information links so that each user sees only the data they are allowed to see.

When User2 logs in, it will load AllData_IL from the WebPlayer cache since it is preloaded during schedule updates. It will do a fresh load of SecurityMapping_IL and will only load the 2nd row in above example (Finance, User 2). When doing an inner join between AllData_IL and SecurityMapping_IL, all rows with Finance in MatchColumn will be loaded.

Reference: KB 42244