Dynamic Row-Level Security Administration: A Safe and Transparent Approach
In the realm of data management, ensuring secure and tailored access to information is crucial. Dynamic row-level security (RLS) plays a pivotal role in achieving this by enabling fine-grained access control. This security model allows data to be visible only to authorized users based on their roles or attributes, thus maintaining confidentiality and compliance across various platforms like PowerBI or Analysis Services.
In this blog post, we will explore effective strategies for implementing dynamic RLS, utilizing practical examples from real-world applications to demonstrate how organizations can safeguard their data while ensuring it is accessible to the right people.
Dynamic Row-Level Security Implementation
All PowerBI or Analysis Services dynamic row-level security implementations require a table that defines dimension permissions for users or AD groups. We have experimented with multiple methods for managing this table, such as direct inserts into a SQL table, importing Excel sheets, or using the organizational authorization systems as sources.
Currently, the most effective implementation of the dynamic RLS control table in our portfolio is based on the Active Directory data of the client coupled with a list of the dimensions to be filtered.
What do we do?
- Import Active Directory data (groups and users).
- Import AD group membership data.
- Prepare the exact values for the dimensions by:
- Importing from source systems, or
- Adding a codetable to maintain the values.
- Administrators pair the Active Directory data with the dimension values in a codetable.
- Extract group members from the AD groups using a stored procedure.
- Refresh the tabular model in an automated manner.
How the Abylon Rapid Platform Simplifies the Solution
- Active Directory data is automatically refreshed with our standard meta-data driven warehouse solution.
- Pairing with Smart Excel is based on references: only groups and dimensions that exist can be chosen.
- Historization is built-in, and users can simply query changes.
- Changes to the reports can be implemented in real-time, with the help of stored procedures and tabular processes.
Let us see a valid use case in a data flow chart, this case is handling country level RLS:
- We have two sources: the Active Directory and a source system (such as SAP or any other systems).
- We retrieve the AD data with data warehouse functionality and the country list to define a new table called RLS where the business key is the reference to countries and AD groups (DWH ID or some other identification column from the source).
- The pairing is done by administrators.
- After modifications are made, a stored procedure populates the RLS dimensions in the Data mart layer, which contains the user-country ID pairs.
- A tabular process refreshes the model, and if there are paginated reports with databases as a source, the data will be filtered for the viewer.
What are the advantages?
Consistency:
- Administration plays a massive part in a data warehouse solution.
- Using references guarantees data consistency.
Security:
- The RLS definition code table can be historized and has its own access control.
- User-level administration is not required since we work with AD groups.
Transparency:
- AD group security is an industry standard.
- Group memberships can be queried easily.
Finally, an example of a code table for multi-level dynamic row-level security:
In this case we can define region, country, and brand security levels. To make this work, a fine-tuned stored procedure is necessary to handle the dimensions of row level security.
Explore More About Multi-Level Access Rights
Interested in diving deeper into dynamic row-level security? Check out our detailed blog post on “Multi-level Access Rights in Dynamic Row Level Security” where we discuss the complexities and solutions for managing sophisticated access structures. Learn how to effectively implement and manage multi-level access rights to enhance data security and compliance.
