Reporting & Analytics - February 8, 2023
Row-level security (RLS) is a critical aspect of data security and privacy that allows organizations to control access to their data at the most granular level. Given the new interactive dashboards and reports and the increased accessibility of data, implementing an RLS solution is essential to ensure that the data is secure and that only authorized users can access it.
Onit's solution for RLS in Tableau uses the existing Onit security features and changes to data sources and filters in workbooks to ensure that only authorized users can access sensitive data. This document is intended to provide an adoption guide on implementing RLS in Tableau.
Understanding the Security Data Model
Before diving into the implementation of RLS in Tableau, it's essential to understand the basics of how RLS works. This section will cover the key concepts and terminology used in RLS in Onit and Tableau.
Security Data Model in Onit
Tableau RLS is based on the Onit security model already in place. Although this document doesn't cover all the methods for gaining access to individual records, they are covered by Onit security, whether by participating in a transaction, being assigned to a user group, being a system admin, or any number of standard or custom methods. These methods are available and can be included in the Onit security cache.
Enable Tableau Report User on the User Profiles App in Onit
Another critical security component is ensuring that all users accessing Tableau reports from the Onit UI are included. These users must be configured as Tableau users on the Onit User Profiles app.
- When you're signed into the Onit, select the User Profiles app.
- If the user has not yet been added as part of the User Profiles, then click on + and select Add User Profile to create a new profile for the user.
- If the user already has a User profile but it is not enabled for Tableau, then click Edit to edit the user profile.
- On the Add/Edit User profile page, select Tableau Report User? Click Yes and save.
- Although the BSON id should be calculated automatically, ensure this value is populated as it will be required to create the Tableau user with SAML authentication on the Tableau server.
Note: This configuration within the User Profile App retrieves Onit platforms' security records to generate a security cache table in Onit for each user/record pair, which is synced into the data warehouse in app-specific security tables.
Users with this configuration set to Yes will be included in the security cache and can view records in Tableau as per their granted security access. If this configuration is set to No or not set for user(s), no data will be available in Tableau for those users.
Onit Security Cache
Refreshing the security cache from Onit's security records is part of the data warehouse's nightly refresh process, so security updates made during the business day will not be reflected until the following day.
To ensure a performant cache, only apps that drive security for a client's specific reporting needs should be included in this process. By default, for ELM, this cache is built on Matters, Invoices, and Vendors for all users with reporting access as configured in the User Profile App. System Admins are not included in the cache but are given access to all records in Tableau, as is the behavior in Onit.
Row-level security in Tableau works from the Onit UI for users configured with SAML authentication on the Tableau server. Customers should work with Onit support to ensure all necessary users have a Tableau login configured with SAML authentication.
Security Data Model in the Data Warehouse
To simplify implementing row-level security in Tableau, Onit provides security cache-related changes to the underlying data model. These changes include additional app-specific tables in the warehouse that are available to Tableau, Data as a Service, and Data Extraction Service. These additional tables are populated as part of the data warehouse's nightly refresh job.
For apps that are used to drive the data level security, an app-specific security table named {app name}_security_cache is available in the warehouse where {app_name} is the name of the app that will be used to drive the security access to data in the reports. Multiple apps can be configured to have their security cache populated in the warehouse.
For example, if the data level security on reports is based on the Matter app, then a table named matters_security_cache will be available in the warehouse. The table will contain the record to be secured (called atoms in Onit), along with a comma-separated list of users. In later sections, you will see that this security table will be joined to the matters table, and the list of users will be searched for access.
In addition to app-specific security tables, Onit provides a view of all admins containing a comma-separated list of system admin IDs. This view will be used to validate admins' determination of access to rows in Tableau.
Implementing RLS in Tableau
Now that you understand the data model, its time to dive into the implementation process. This section provides step-by-step introductions on how to create data sources, calculated fields and how to apply them to the worksheets and dashboards.
Changes to the Tableau Data Source
Several changes to the data source model may be necessary when implementing Row-Level Security (RLS) in Tableau using the security cache table.
Here is an example of the tableau data source that is optimized for RLS.
1. To apply the security filters, the data source must be linked to the security cache table. This can be done by creating relationships between the data source driving table and the security cache table based on the unique identifier.
2. In this example, a security cache table, matters_security_cache, is added to join the matters table (matters._id = matters_security_cache. atom_id). The matters_security_cache contains the list of all the matter IDs and the corresponding list of users (comma-separated IDs) with access to them.
3. An additional view, system_admin_users_agg_view, is also added. It stores all the system admin user IDs in a single row separated by commas.
Changes to the Tableau Workbook
The workbook may need to be modified when implementing Row-Level Security (RLS) in Tableau using a security cache table.
1. A calculated field, such as User Filter, determines a user's data access level. Copy the calculation below and paste it into the code pane for the calculated field.
IF REGEXP_MATCH([User Ids],USERNAME())
OR REGEXP_MATCH([Sys Admin User Ids], USERNAME())
THEN 1 ELSE 0 END
Example:
This calculated field will return 1 (allow access) to the row only if the Tableau user returned by USERNAME() tableau function is part of the [User Ids] or [Sys Admin User Ids]
2. This calculation now needs to be converted to continuous and then to a measure in the left pane in Tableau by right-clicking on it
Example:
3. Next, the User Filter must be added to the data source connection in the report. This can be done by right-clicking on the data source connection, as shown below, and selecting the Edit Data Source Filters option.
Example:
4. Modify the filter value and select one that is only valid for rows where the user BSON ID is part of the [User Ids] or [Sys Admin User Ids], i.e., if the user has access to the individual record or is a system administrator in Onit.
Example:
Row-Level Security (RLS) is a powerful feature that allows you to control access to sensitive data at the row level. By implementing RLS, organizations can ensure that only authorized users can access the data they need while keeping sensitive data hidden from those who should not have access.