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: Restriction on Country 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, testsecurity@sisense.com 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 testsecurity@sisense.com 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 testsecurity@sisense.com 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 Create the Users_scope table Permissions, for example: testsecurity@sisense.com 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 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 testsecurity@sisense.com Again, The Security_key value per user is unique and can't repeat in Users_security_keys table. 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 Create the Data Security on Users_security_keys table where User = User Please find the example below 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 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 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. In case need additional Scopes, the new Scope should be added as part of the Security key, keeping the Identical Structure. Surrogate_key using '_' is an example and several alternatives can created, keeping a similar structure 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
