Row-Level Security Implementation Using OR Operator Across Multiple Dimensions

[AssafHanina]

Row-Level Security Implementation Using OR Operator Across Multiple Dimensions

Overview

In Sisense, the Data Access Security, allows Admins to restrict the data access for Users and Groups, in row granularity. 
In many cases, customers restrict the data to multiple dimensions. 
For Example: 

1. Restriction on Country 
2. Restriction on User

When applying data security across two different dimensions, the security rules are enforced using an 'AND' operator between the dimensions.

In many cases, customers are looking for the ability to use the Data Security feature with an 'OR' operator between two or more dimensions, a functionality that is not supported natively.

Example Use Case

A Manager of a Country, [email protected] in our example, needs access to information related to his country, including details on both sales and manufacturing activities within that Country.

User Permissions 

• Country Restriction - CHINA 
• Security Apply on Manufacture Country = 'CHINA' and Sales Country = 'CHINA' with 'OR' condition Between the two dimensions

User Output example on fact_transactions using OR condition

User [email protected] access should be for transactions: [1,5,8,13,16,20] 

User output example on fact_transactions using AND condition (current Sisense Configuration)

While Applying Data Security over the 2 dimensions for User [email protected] as the following:

• dim_manufacture_country.Country = 'CHINA'
• dim_sales_country = 'CHINA' 

the Restriction will allow the user the access for records: [1,20] 

Data Model Tables and relations
the list of tables which created for the use case

• dim_employee - list of all employees
• dim_sales_country - list of all sales country
• dim_manufacture_country - list of all manufacture country
• fact_transactions - transaction data
• fact_orders - orders data 
  
  

Table Relations

Basic Solution 

In general, it is recommended to apply data security to a single dimension rather than using multiple tables. 

Implementation Steps

Step 1 - Create a surrogate key
In each of the fact tables (or tables that require to have the restriction), Create a surrogate key as the additional column, in the following format:

Note - Make sure that the Values in the Surrogate key are not Null

[manufacture_country] + '_' + [sales_country] ​

Table Output 

Step 2 - Create a Unique security key table
The Security key table is a combination of all possible keys from all fact tables; see the example:

select distinct fo.security_key
from [fact_orders] fo
union 
select distinct ft.[security_key]
from [fact_transactions] ft​

Use Union instead of Union ALL to remove the duplication of the security_key to keep it as the primary key

Step 3 - Create the relationship
Create the relationships between the fact table/s and the security table on the security_key column

Step 4 - Assign the Data Security Member by user/group

• In the Data model page, Click on the Options
• Click on Data Security 
• Select security_key column from the security_key table
• Select the User / Group 
• Select the relative Members for the Country 
• Assign all the related Keys
   

Step 5 - Create a dashboard for the validation

• create a pivot table and select the relative columns
• check the results - should return all the records of China for manufacture and sales country
  
  

Advance Solution 

The advanced solution is more scalable, easier to understand and debug, and can improve the dashboard load time (in case the amount of members in the data security is high). 

Step 1 - Create a surrogate key
In each of the fact tables (or tables that require to have the restriction), Create a surrogate key as an additional column, in the following format:

Note - Make sure that the Values in the Surrogate key are not Null

[manufacture_country] + '_' + [sales_country] ​

Table Output

Note - best practice is to create the column directly in the source data base

Step 2 - Create a security key table
The Security key table is the combination of all possible keys from all fact tables, see the example:

select distinct fo.security_key
from [fact_orders] fo
union 
select distinct ft.[security_key]
from [fact_transactions] ft​

Use Union instead of Union ALL to remove the duplication of the security_key in order to keep it as the primary key

Step 3 - Create a Security Table and Store the Members on the User Level

implementation prerequisites

• On the Customer level (Source Data Base), it is to maintain the table according to the security rules for each user 
• The Security keys will be assigned to each user according to his permissions
• The Members per user will be Unique 
• the table will be added to the data model and the security will apply to that table 

Implementation Example

1. Create the Users_scope table Permissions, for example:
   [email protected] allows one to see CHINA country
   User: User to apply the security on
   Scope: The Dimension/s
   Value: The Security info that the security_keys are assigned to
   
   
   Example Table
   
   
   
2. Create a table: Users_security_keys and Assign the Distinct Security_keys related to CHINA from the Security_key table for each user in Users_scope table. 
   
   Table output for [email protected] 
   
   
   Again, The Security_key value per user is unique and can't repeat in Users_security_keys table.
3. Create the relation in the data model from Users_security_keys table to the fact tables based on the security_key column.
   The Relation is a Many to Many by design but is one to Many on a User Level
   
   Model Relation
   
   
4. Create the Data Security on Users_security_keys table where User = User
   Please find the example below
   
   
   
   
5. Create a dashboard for the validation
   
   

Solutions Compare Between the Methods

Topic - Main Points	Basic Solution	Advanced Solution
Dashboard Update while Applying the Row Level Security	Dynamic - Changes apply on the Dashboard at the time the Members Updated	Limited - Changes apply after the member updates in user security table.  Elasticubes - Changes will be updated after the Build Live - Changes will be updated while the Member updates in the table
Member Assignment in Data Security	Users can have a list of Members	Users have Single Member
Performance - Amount of members	Limited - Performance issues raised in a high amount of Members (recommended less than 100 Members)	Advanced - Single member per user which outputs the list of all members from the Users_security_table
Assignment on User/Group	Data Security Apply on User Or Groups.  Provide Flexibility of Managing the Same Access for Users on a Group Level	Data Security Apply on User Level.  Each User will have its own permissions
Security Table Size	Contains a List of Unique Security Keys. Relation from Security table to Facts is on One to Many	A large table contains a list of all users with a unique member list by user.  Relation from Security table to Facts is Many to Many By Design, But One to Many on the User Level
Members' Maintenance across multiple Data Models	Changes in Members for a single user / Group should update for each data model	Changes in Members for Single User are updated in the table, no changes are required in Sisense Level for Existing Users
Members Tracking (Changes between Permissions)	Harder to Maintain, require to Get the Amount of Members before Applying the Changes	Handled on the Customer Side, Easier to Have it as a Change Log in Tracking Security table in the Customer Data Base

Comparison summary
Both Method has Pros and Cons related and selecting the right method is per the use case. 
Few Examples:

• For Quick Changes in the Members that should affect the dashboard immediately, while the Amount of Members is Small, Use the Basic Solution 
• For Large Deployment, where changes shouldn't update on the fly, Similar data security for a user among multiple cubes, use the Advanced Solution

Advanced Options and Points

1. Make Sure that the Security key is not Null, or does not have any Null values in one of the compositions of the Surrogate key
2. Keep the Identical structure of the Surrogate key in all fact tables
   Identical meaning, if starting the Seucrity_key with Manufature_country + '_' + sales_country, this structure should be created in all fact tables. 
3. In case need additional Scopes, the new Scope should be added as part of the Security key, keeping the Identical Structure.
4. Surrogate_key using '_' is an example and several alternatives can created, keeping a similar structure
5. Best Practice is to Generate the Surrogate key and all the Logic within the Source Data Base.

Best Regards

Check out this related content: 
Academy
Documentation