Row, Column, Object and File Security

What it is

Granular security limits what a user sees inside an item. Row-level security (RLS) filters rows, column-level security (CLS) hides columns, object-level security (OLS) hides whole tables or columns, and file-level control limits folders and files in OneLake.

Fabric enforces these in three places: the warehouse or SQL analytics endpoint (T-SQL), the semantic model (DAX roles) and OneLake itself (OneLake security roles). The exam tests which place fits a requirement.

How it works

Where each rule lives

All of these restrict Viewers and users with item-level read access. Workspace Admins, Members and Contributors can edit the item, so the rules do not limit them.

Object-level and column-level security with T-SQL

In a warehouse or a SQL analytics endpoint, standard T-SQL permissions control tables, views and columns.

-- object level: whole table
GRANT SELECT ON dbo.Sales TO [SalesAnalysts];
DENY SELECT ON dbo.Payroll TO [SalesAnalysts];

-- column level: only the listed columns
GRANT SELECT ON dbo.Employee (EmployeeID, Name, Department) TO [HRAnalysts];
DENY SELECT ON dbo.Employee (Salary) TO [HRAnalysts];

A query that names a denied column fails, and SELECT * fails too. Users must list only the columns they may read.

Row-level security with a security policy

Warehouse RLS uses an inline table-valued function as the predicate and a security policy that binds it to a table.

CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_SalesRep(@SalesRep AS varchar(100))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_result
    WHERE @SalesRep = USER_NAME();
GO
CREATE SECURITY POLICY Security.SalesFilter
    ADD FILTER PREDICATE Security.fn_SalesRep(SalesRepEmail) ON dbo.Sales
    WITH (STATE = ON);

Each user now sees only rows where SalesRepEmail equals their user name. Rows are filtered silently; no error is raised.

Semantic model RLS and OLS

In Power BI Desktop, Modeling > Manage roles creates roles with DAX filter expressions. A static role uses a fixed value; a dynamic role compares a column with the signed-in user.

'Sales Rep'[Email] = USERPRINCIPALNAME()

After publishing, you add users or groups to each role in the semantic model's Security page in the service. View as (in Desktop) and Test as role (in the service) let you check the result.

OLS hides tables or columns from a role. It is usually configured with an external tool such as Tabular Editor. A visual that uses a hidden object shows an error for that role, as if the object did not exist.

OneLake security and file-level control

OneLake security roles are defined on a lakehouse. A role grants read on chosen tables or folders, and on tables it can add row and column constraints. Folder access in the Files area is how you control access at file level.

These roles apply when users read through OneLake, for example from Spark notebooks or through shortcuts. As with the other rules, they restrict readers, not workspace editors.

A worked example

An HR warehouse holds an Employee table. HR analysts may query everything except Salary, and department managers may see only their own department in a Power BI report.

1. Grant SELECT on the allowed columns to the analysts' group, and deny SELECT on Salary.
2. In the semantic model, create a role Managers with a filter on the department table that matches USERPRINCIPALNAME() to a manager email column.
3. Add the managers' security group to the role in the service and give them the Viewer role or an app.

How the exam asks about this

• "Users must not see the Salary column when they query the warehouse." Column-level security with GRANT or DENY on the column.
• "Each sales rep sees only their own orders in a report." A dynamic RLS role using USERPRINCIPALNAME().
• "A table must not appear at all for one group of report users." Object-level security on the semantic model.