Data Security: Control Who Sees What
Implement row-level, column-level, object-level, and file-level access controls plus dynamic data masking to protect sensitive data in lakehouses and warehouses.
What is data-level security?
Imagine a spreadsheet where different people see different rows and columns.
The sales manager sees all regions. A regional manager sees only their region’s data. The finance team sees revenue columns but not customer phone numbers. An intern sees everything except salary data — and salaries appear as “XXXX” instead of real numbers.
That’s data-level security. Even when someone has access to a table, you control which rows they see (RLS), which columns they see (CLS), and whether sensitive values are masked (DDM).
Row-Level Security (RLS)
RLS filters table rows based on who is querying. It’s implemented differently in lakehouses, warehouses, and semantic models.
RLS in warehouses
You create a security policy using T-SQL:
- Create a predicate function — returns 1 for rows the user can see
- Create a security policy — binds the function to a table
- Test — query the table as different users to verify filtering
The function typically checks SESSION_USER() or a role mapping table.
RLS in semantic models (Power BI)
You define roles in the model with DAX filter expressions:
- Role “Europe Sales” →
[Region] = "Europe" - Role “Asia Sales” →
[Region] = "Asia"
Users assigned to a role see only matching rows in reports.
Scenario: Ibrahim's regional RLS
Nexus Financial has trading desks in London, New York, Singapore, and Sydney. Traders must see only their own desk’s data. Ibrahim implements RLS on the FactTrades warehouse table:
- A mapping table links each user to their trading desk region
- A filter predicate checks: does the querying user’s desk match the row’s region?
- When a London trader queries, they see only London trades — even though the table contains all regions
The compliance team gets a role with no filter (they see everything for audit purposes).
Column-Level Security (CLS)
CLS restricts which columns a user can query. Unlike RLS (which filters rows), CLS hides entire columns.
| Where | How |
|---|---|
| Warehouse | GRANT SELECT ON table(column1, column2) TO user — only named columns are accessible |
| Semantic model | Object-Level Security hides columns from report consumers |
Exam tip: RLS + CLS together
RLS and CLS can be combined. A query might filter rows (only your region’s data) AND restrict columns (you can see order amounts but not customer SSN). The exam tests whether you understand that these are independent — applying RLS does not automatically apply CLS, and vice versa.
Dynamic Data Masking (DDM)
DDM shows obfuscated values instead of real data, without changing the underlying stored data. It’s a warehouse feature using T-SQL.
Masking functions
| Function | What It Does | Example |
|---|---|---|
| Default | Full mask — shows 0 for numbers, XXXX for strings, 01-01-1900 for dates | SSN: XXX-XX-XXXX |
| Shows first letter and domain | a***@contoso.com | |
| Random | Random number within a specified range | Salary: 47382 (random, not real) |
| Partial (custom string) | Shows prefix and suffix, masks middle | Phone: (04) XXXX-XX89 |
Who can see unmasked data?
- db_owner and users with UNMASK permission see real values
- Everyone else sees masked values
- Masking is applied at query time — the stored data is unchanged
| Feature | RLS | CLS | DDM |
|---|---|---|---|
| What it controls | Which rows a user sees | Which columns a user can query | What values a user sees (obfuscated vs real) |
| Data changed? | No — rows are filtered at query time | No — columns are hidden from the query | No — values are masked at query time |
| Where supported | Warehouse, semantic model, lakehouse (via views) | Warehouse (GRANT), semantic model (OLS) | Warehouse (T-SQL) |
| Implementation | Security policy + predicate function | Column-level GRANT/DENY | ALTER COLUMN with masking function |
| Typical use case | Regional data isolation, multi-tenant data | Hide PII columns from analysts | Show partial data (last 4 digits of SSN) |
Folder and file-level access in OneLake
Lakehouse data lives in OneLake in two sections:
- Tables/ — Delta tables managed by Spark/SQL
- Files/ — unstructured files (CSVs, images, Parquet files)
OneLake security lets you set read permissions on specific folders within a lakehouse. This is useful when different teams need access to different datasets within the same lakehouse.
| Access Level | What It Controls |
|---|---|
| Folder-level | Grant/deny read access to specific folders in Files/ or Tables/ |
| Inheritance | Subfolder permissions inherit from parent unless explicitly overridden |
| Workspace role interaction | Workspace Admins and Members bypass OneLake folder security; Contributors and Viewers are subject to it |
Scenario: Anika's folder isolation
ShopStream’s lakehouse has folders for different data domains: /Files/customer-data/, /Files/product-data/, /Files/marketing-data/. The recommendation team needs product data but must NOT see customer PII.
Anika configures OneLake folder security: the recommendation team’s security group has read access to /Files/product-data/ only. When they browse OneLake, they see only the product folder — customer and marketing folders are invisible.
A Fabric warehouse contains a table with employee salaries. HR should see real values, but managers should see masked values (random numbers within a range). Which feature should you implement?
Ibrahim configures OneLake folder security on a lakehouse. A user with the Contributor role cannot see the `/Files/trading-data/` folder. A user with the Member role CAN see it despite having no explicit folder permission. Why?
Next up: Governance: Labels, Endorsement & Audit — apply sensitivity labels, endorse trusted items, and track everything with audit logs.