DP-700: Implement data security on Fabric tables

I will explain how to implement data security on Microsoft Fabric tables — specifically, Row-Level Security (RLS) and Column-Level Security (CLS). This skill is relevant for the DP-700 exam because it demonstrates access control and data protection in an analytics solution, and it is essential in practice to meet privacy and access segregation requirements.

What you need to know

Data-level security means restricting what each user can see in analytical data. The two most common complementary approaches in Fabric are:

• Row-Level Security (RLS): limits the rows visible in a table based on rules (for example, only see customers from their own territory).
• Column-Level Security (CLS): hides sensitive columns (for example, credit card numbers) from certain roles or users.

Simple example: a Sales table with columns (SaleID, SalesPersonID, Amount, Cost) and a Users dimension with (UserID, Role, Region). We want a salesperson to see only sales from their Region (RLS) and only managers to see the Cost column (CLS).

How it works

In a Fabric environment (OneLake / Lakehouse / Tables), there are two general options to apply these securities:

• Apply security policies on the table object itself (using security settings or filters in the tables service).
• Apply security in the exposure layer (for example, in a SQL view that filters rows and removes columns, or in Power BI with RLS, although it is preferable to apply at the data source for consistency).

Typical workflow for RLS in SQL/Views:

-- Exemplo conceptual de view com RLS baseado em função/claim do utilizador
CREATE VIEW vw_Sales AS
SELECT s.SaleID, s.SalesPersonID, s.Amount, s.Cost
FROM Sales s
JOIN Users u ON s.SalesPersonID = u.UserID
WHERE u.Region = CURRENT_USER_REGION();

Note: in managed environments, identity roles and claims can be sourced from Azure AD; in many scenarios you use roles or linking keys between identity and data.

In practice (step by step)

Step 1 — Model a user/security dimension table: each user or role should have required attributes (Role, Region, AllowedColumns).

Users(UserID, UserName, Role, Region, AllowedColumns)

Step 2 — Implement RLS using a view or built-in policy:

1. If your environment supports built-in table policies, define the RLS policy associated with a role/claim.
2. If not, create parameterized views that use the user identity (or a function that resolves their Region) to filter rows.

Step 3 — Implement CLS by minimizing the exposed surface:

• Create views without the sensitive columns for unauthorized roles.
• Grant permissions only on these views, not on the base table.

-- View for general users (Cost column removed)
CREATE VIEW vw_Sales_Public AS
SELECT SaleID, SalesPersonID, Amount
FROM Sales;

-- View for managers (full access)
CREATE VIEW vw_Sales_Managers AS
SELECT SaleID, SalesPersonID, Amount, Cost
FROM Sales;

Step 4 — Assign appropriate permissions (Azure AD, roles in SQL/Workspace): ensure users do not have direct access to the base table.

Common mistakes

1) Granting permissions on the base table: giving direct SELECT on the table overrides all views and policies; always grant only on views or use policies implemented in the engine.

2) Assuming RLS replaces encryption: RLS filters visibility, but sensitive data may require encryption at rest/in transit and dynamic masking according to compliance requirements.

3) Neglecting performance management: views with complex joins or identity-resolution functions per row can degrade performance — test and optimize with indexes/partitions where applicable.

How to practice

Set up a lab in Fabric/Workspace with a Sales table and a Users table. Try implementing RLS using views and, if supported by your environment, native table policies. Test with different user accounts (use Azure AD groups) and validate each user sees only what is expected.

To prepare for the exam, use the official free Practice Assessment from Microsoft and the official study guide (both free). These resources help you confirm which areas are measured and practice with Microsoft-approved sample questions.

Summary

• RLS limits rows visible per user; CLS restricts sensitive columns — both are complementary.
• Implement security ideally at the central point (table/policy or controlled views), not only at the reporting layer.
• Avoid granting direct access to the base table; use views or policies and manage permissions via Azure AD/roles.
• Test performance and consider additional measures (masking, encryption) according to compliance requirements.