Product:TIBCO Spotfire Analyst
Versions:All Versions
Summary:
How to setup caching for reports with personalized information links with row level security.
Details:
This article describes how Scheduled Updates 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.
Resolution:
Versions:All Versions
Summary:
How to setup caching for reports with personalized information links with row level security.
Details:
This article describes how Scheduled Updates 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.
Resolution:
- 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). For example:
AllDataMatchColumn ActualData Finance Finance Record 1 Finance Finance Record 2 Sales Sales Record 1 Sales Sales Record 2
SecurityMappingMatchColumn UserName Finance User 1 Finance User 2 Sales User 1 - Create column elements for AllData and SecurityMapping.
- 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. You can verify this by running an information link and checking the SQL in sql.log.
- Create an Information link - AllData_IL .
- Add both columns (MatchColumn, ActualData) from AllData table.
- Create an Information link - SecurityMapping_IL .
- Add both columns (MatchColumn, UserName) from SecurityMapping table.
- Add filter created earlier - UserName_Filter .
- Add data table to your analysis.
- File >> Add Data Table >> Information Link >> AllData_IL .
- 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).
- 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).
- Edit >> Data Table Properties >> Schedule Updates >> Select the checkbox for SecurityMapping_IL (Reload the following data for each user).
When user 2 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.
Comments
0 comments
Please sign in to leave a comment.