DP-700: how to implement row-level access policies in Fabric

I will teach how to implement Row-Level Security (RLS) on Microsoft Fabric tables. This skill is important for the DP-700 exam because it demonstrates data access control in analytics environments, and in practice protects sensitive records by ensuring users see only what they are allowed to.

What you need to know

Row-Level Security (RLS) limits the rows a user can query in a table, based on their identity or rules. In Fabric (for example in Power BI tables or Delta tables in a Data Warehouse/Table workspace), RLS can be applied through roles and filters that use columns such as UserID, DepartmentID or session attributes.

Conceptual example: you have a Sales table with a Region column. If an analyst belongs only to the "North" region, RLS ensures their queries only return rows where Region = 'North'.

How it works

In general terms, there are three components in RLS:

• User identity: authenticated via Azure AD.
• Rule definition: a filter that expresses which rows are visible.
• Filter enforcement: mechanism that injects the filter into queries at runtime.

In common Fabric implementations you can use two main approaches:

• Native RLS in the Power BI model (roles with DAX) — applicable when the model is serving Power BI reports within Fabric or Power BI Service.
• RLS at the table/Delta level with security policies in OneLake / Data Warehouse (using permissions of the SQL endpoint or object-level policies) — applicable for scenarios where multiple tools access the tables.

In practice

I will describe a practical example with two options: (A) RLS in the Power BI model using DAX roles; (B) RLS on managed tables using a security column and filters at the SQL endpoint. The steps are simplified and instructive.

A — RLS in the Power BI model (DAX roles)

1. Prepare the table: ensure a column that identifies membership, e.g.: UserEmail or RegionID.
2. In Power BI Desktop (or in the Model Designer in Fabric), create a role: Model > Manage Roles.
3. Define the DAX expression for the role. Example to filter by the current user email:

   [UserEmail] = USERPRINCIPALNAME()

4. Test the role using View As Roles to simulate different users.
5. Publish the model to Fabric/Power BI Service. In the service, assign users to the roles if necessary (or use the dynamic expression USERPRINCIPALNAME to avoid manual assignments).

B — RLS at the table/SQL endpoint level

1. Include a control column, e.g.: AllowedDepartmentID, and maintain a mapping table UserDepartment(UserPrincipalName, DepartmentID).
2. Create a secure view that joins the data with the mapping and applies the filter. Example in SQL for the SQL endpoint:

   CREATE VIEW v_Sales_Secure AS
   SELECT s.*
   FROM Sales s
   JOIN UserDepartment u ON s.DepartmentID = u.DepartmentID
   WHERE u.UserPrincipalName = ORIGINAL_LOGIN();

   Note: the function that returns the user can vary depending on the endpoint; in Fabric/SQL environments use the appropriate function for the user identity.
3. Grant users access only to the view v_Sales_Secure and deny direct access to the Sales table.
4. Configure roles/privileges in the workspace so that applications and users query only the view.

Best practices

• Prefer dynamic filters (e.g.: USERPRINCIPALNAME()) to avoid manual role management.
• Keep identity mappings up to date (integration with Azure AD simplifies this).
• Test RLS with real accounts or simulation tools.

Common mistakes

• Assuming RLS replaces object permissions: RLS filters rows, but you still need to ensure users cannot access raw tables directly.
• Using columns with NULL values to control access without proper handling — NULL can make filters ineffective and lead to data leaks.
• Relying only on manual role assignments instead of dynamic expressions; management does not scale well in large organizations.

How to practice

Practice implementing both methods in a Fabric trial environment or in a training subscription. Create datasets with control columns and test with different identities.

To prepare for the exam, use the OFFICIAL Microsoft Practice Assessment (it is free) and consult the official Study Guide (free) for DP-700 — these resources help you validate knowledge without resorting to prohibited material.

In summary

• RLS controls which rows each user can see — essential to protect sensitive data in analytics.
• It can be implemented in the Power BI model (DAX roles) or at the table/SQL endpoint level (secure views and mappings).
• Always test with real identities and avoid configurations that allow direct access to underlying tables.
• Use Microsoft’s official resources (Practice Assessment and Study Guide) to practice correctly and for free.