Row, Column, Object and Folder Security, Masking and OneLake Security

What it is

Granular security decides which part of an item a reader sees. In a warehouse or SQL analytics endpoint you use T-SQL: row-level security (RLS), column-level security (CLS), object-level security (OLS) and dynamic data masking (DDM).

OneLake security applies the same ideas to the data in OneLake itself. Its roles grant access to chosen tables and folders, with optional row and column filters, and the rules follow the data into Spark, the SQL analytics endpoint and Direct Lake semantic models.

How it works

Row-level security in T-SQL

RLS uses an inline table-valued predicate function and a security policy. Fabric Warehouse supports filter predicates, which silently remove rows the user may not see.

CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_SalesRepFilter(@SalesRep AS VARCHAR(128))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_result
    WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'salesmanager@contoso.com';
GO
CREATE SECURITY POLICY Security.SalesFilter
ADD FILTER PREDICATE Security.fn_SalesRepFilter(SalesRep) ON dbo.Sales
WITH (STATE = ON);

USER_NAME() returns the signed-in user's name (their UPN in Fabric), so each rep sees only their own rows.

Column-level and object-level security

CLS grants access to some columns only. A query that selects a column the user lacks permission on fails, so users write SELECT with the allowed columns rather than SELECT *.

GRANT SELECT ON dbo.Customers (CustomerID, CustomerName, City) TO [analyst@contoso.com];

OLS controls whole objects, such as tables, views and procedures, with GRANT, DENY and REVOKE. DENY overrides a GRANT the user gets through a role.

DENY SELECT ON dbo.EmployeeSalary TO [analyst@contoso.com];

Semantic models have their own RLS and OLS, defined with roles in the model. That is mainly DP-600 territory; for DP-700 know that model roles apply to report users, not to people querying the warehouse.

Dynamic data masking

DDM hides part of a value in query results while leaving the stored data unchanged.

ALTER TABLE dbo.Customers
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

ALTER TABLE dbo.Customers
ALTER COLUMN Phone ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XXX-",4)');

GRANT UNMASK TO [supportlead@contoso.com];

Users with the Admin, Member or Contributor workspace role see unmasked data, as does anyone granted UNMASK. DDM is a convenience, not strong protection: a user who can filter on a masked column can still infer values.

Folder and file level access with OneLake security

OneLake security roles are defined on a lakehouse (and other supported items) under Manage OneLake security. Each role lists:

• Data: the tables, schemas or folders under Files that the role can read.
• Members: users or groups, or members chosen by their existing item permissions.
• Optional rules: a row filter written as a T-SQL SELECT with a WHERE clause, and a list of allowed columns.

The built-in DefaultReader role gives everyone with ReadAll access to all data. To restrict access you edit or remove it, otherwise it keeps granting everything.

The roles are enforced for Spark and OneLake APIs, for Direct Lake on OneLake semantic models, and for the SQL analytics endpoint when it runs in user identity mode. In delegated identity mode, the endpoint uses its own SQL permissions instead.

OneLake security restricts only readers: users with the Viewer role or a shared item. Admin, Member and Contributor still see everything.

A worked example

Regional managers must read only their region's rows in the sales table of a lakehouse, from Spark notebooks and from reports, and the Files/hr folder must be hidden from them.

Create a OneLake security role per region that includes the sales table with a row filter on Region, and leave Files/hr out of every role. Remove the managers from DefaultReader, give them the Viewer role, and switch the SQL analytics endpoint to user identity mode. One definition now covers Spark, T-SQL and Direct Lake reads.

How the exam asks about this

• "Hide the salary column from analysts querying the warehouse." Column-level security with GRANT SELECT on the other columns, or DENY on the column.
• "Show only the last four digits of the card number to support staff." DDM with partial().
• "Apply the same row filter to Spark and T-SQL users of a lakehouse." OneLake security role with a row filter.