Row-Level Security in Tabular Models

This document explains how to implement Row-Level Security (RLS) in SQL Server Analysis Services (SSAS) Tabular models. RLS allows you to restrict data access for specific users based on the rows in a table that they are allowed to see.

What is Row-Level Security?

Row-Level Security (RLS) is a feature in SQL Server Analysis Services Tabular models that enables you to restrict access to rows in tables based on user identity. This is crucial for applications that need to expose subsets of data to different users, ensuring that users only see the data they are authorized to access.

RLS is implemented using DAX (Data Analysis Expressions) filter expressions that are associated with security roles. When a user is a member of a role that has RLS defined, these DAX expressions are applied to their queries, effectively filtering the data returned.

Implementing Row-Level Security

Implementing RLS in SSAS Tabular models involves defining security roles and then creating DAX filter expressions for specific tables within those roles.

Step 1: Create a Security Role

You can create security roles using SQL Server Management Studio (SSMS) or Visual Studio with the Analysis Services projects extension.

  1. In SSMS, connect to your SSAS instance.
  2. Right-click on your Tabular database and select "Create Role".
  3. Give your role a meaningful name (e.g., "Sales Managers", "Region A Users").

Step 2: Define Table Filter Expressions (DAX)

For each role, you define which rows users in that role can access. This is done by creating DAX filter expressions for specific tables.

Consider a scenario where you have a 'Sales' table with a 'Region' column, and you want 'Sales Managers' to see only sales data for their respective regions.

Example DAX Filter for 'Sales' Table:

If you have a related 'Users' table that maps user logins to regions, you can use functions like `USERNAME()`, `USERPRINCIPALNAME()`, `LOOKUPVALUE()`, and `IN` to filter data. 


-- Assuming a 'Sales' table and a 'Users' table with 'UPN' and 'Region' columns
-- and the current user's UPN is available via USERPRINCIPALNAME()

-- Basic filter for a specific region
[Region] = "North"

-- More dynamic filter using USERPRINCIPALNAME() and LOOKUPVALUE()
-- This assumes a 'Users' table with 'UPN' and 'Region' columns,
-- and that the user logging in has their UPN stored in the 'Users' table.
LOOKUPVALUE(
    'Users'[Region],      -- Column to return
    'Users'[UPN],         -- Column to search
    USERPRINCIPALNAME()   -- Value to search for (current user's UPN)
) = 'Sales'[Region]

-- Using the IN operator for multiple regions or a list of users
-- Example: Allow access if the user's region is in a list of allowed regions
[Region] IN {"North", "East"}

-- Example: Filter based on the user's assigned department in a related table
-- Assuming a 'DimUser' table with 'UserPrincipalName' and 'Department'
-- and a relationship between 'DimUser' and the fact table.
VAR UserDepartment = LOOKUPVALUE('DimUser'[Department], 'DimUser'[UserPrincipalName], USERPRINCIPALNAME())
RETURN
    RELATED('DimUser'[Department]) = UserDepartment
Important:
  • The DAX expression should return a boolean value (TRUE or FALSE).
  • When the expression evaluates to TRUE, the row is visible; when FALSE, it is hidden.
  • Ensure your model has appropriate relationships defined for these filters to work correctly across tables.
  • Use `USERNAME()` or `USERPRINCIPALNAME()` carefully. `USERPRINCIPALNAME()` is generally preferred as it returns the user's UPN, which is more consistent.

Step 3: Assign Users to Roles

Once roles and filters are defined, you need to assign users or Windows groups to these roles.

  1. In SSMS, right-click on the role you created.
  2. Select "Members".
  3. Click "Add" and enter the Windows login name of the user or the name of the Windows group.

Testing Row-Level Security

After implementing RLS, it's crucial to test it thoroughly to ensure it works as expected.

In SSMS, after defining roles and assigning members, you can use the "Test as Role" feature.

  1. Right-click on the Tabular database.
  2. Select "Process" -> "Test as Role".
  3. Choose the role you want to test.
  4. Execute DAX queries and observe the results to verify data access restrictions.

Best Practices for RLS

  • Keep Filters Simple: Complex DAX expressions can impact query performance. Optimize your DAX for readability and efficiency.
  • Use Related Tables Wisely: Leverage relationships to filter data across multiple tables. Ensure these relationships are correctly defined.
  • Avoid Overlapping Roles: Design your roles to be distinct. If a user belongs to multiple roles, their access is the union of all permissions.
  • Document Your Roles: Clearly document the purpose of each role and the DAX filters applied.
  • Regularly Review Permissions: Periodically review user assignments to roles to ensure access remains appropriate.
  • Consider Security Model: Understand whether you're filtering based on Windows logins, Azure AD users, or other identity sources.

Common Scenarios

  • Sales Territory Management: Sales reps see only their assigned territories.
  • Customer Data: Account managers see only their assigned customer accounts.
  • Hierarchical Security: Managers see data for their direct reports and themselves.
  • Multi-tenant Applications: Each tenant sees only their own data.