OneLake Row, Column, and Folder Security

Three controls inside one role

Sharing a whole lakehouse is often too coarse. One table belongs to finance, one column holds salaries, and one group of rows belongs to a single region. A OneLake security role can solve the three problems, because within a role you can refine access at three levels of granularity.

Inside one role the three controls apply together, so OneLake takes their intersection. A member receives only the access allowed by the object, row, and column settings of that role.

You set them in one place. Open the item, select Manage OneLake security, and open a role. The tables and folders added to the role are its object-level security. To restrict a table further, select more options next to the table and choose Row security or Column security.

One condition comes from the role itself. You can apply row-level security and column-level security only to roles that grant Read permission. Creating roles and choosing their permissions and members is the subject of the lesson on OneLake security roles. That lesson also covers the DefaultReader role and the time a change takes to apply. This lesson is about the three controls.

Tables, schemas, and folders as objects

Object-level security answers the first question: which objects exist for this role. Table-level and folder-level security, also called object-level security, lets you grant access to specific tables or folders in a data item.

One mechanism covers a lot, because of how OneLake stores data. Delta Parquet tables in OneLake are represented as folders, so you can secure tables the same way you secure folders. Schemas are also folders, so you can secure them the same way. With object-level security you create permissions for both structured and unstructured data at the folder level.

Row and column rules need a table. Object-level security is the control that also reaches files.

Not every folder is a table. For OneLake security and the query engines, a folder is a valid table only when it meets three conditions:

• The folder exists in the Tables/ directory of the item. In a schema-enabled item, the folder must also be in a valid schema folder.
• The folder contains a _delta_log folder with the JSON files for the table metadata.
• The folder does not contain any child shortcuts.

This matters when you add rules. If you configure RLS or CLS on a table, OneLake denies access when the table's folder does not meet these conditions. Without RLS or CLS, OneLake treats a folder that does not meet the conditions as a folder and applies folder-level security.

What does a user see of an object that no role grants? For users with no access to a table, the data is never exposed. Names are a different matter. OneLake security does not guarantee that the metadata for a table is not accessible, and certain error messages and experiences might show column names.

The SQL analytics endpoint has its own behavior here. The SQL analytics endpoint always returns all schema names in object metadata, regardless of the user's table-level permissions. Tables for which the user has no permission are filtered out and do not appear in the listing. As a result, users may see schemas that contain no visible tables. An empty schema in the object explorer is therefore not a security error.

Folder grants: inheritance and traversal

A grant on a folder affects the hierarchy in two directions, and they do different things.

Inheritance goes down. Permissions granted on a folder apply downward to its files and subfolders.

Traversal and listing go up. When users have permission on a child item, OneLake security lets them list and traverse its parent folders, so they can navigate to the data they can access. Traversal does not grant access to sibling files or folders.

Take a lakehouse where Files/folder1 contains file11.txt and subfolder11, and Files/folder2 contains file21.txt. A role grants Read on subfolder11.

A second role that grants Read on folder2 lets its members read file21.txt and traverse Files, but they cannot see folder1 or any of its children.

Column-level security: an allow list

Column-level security protects the sensitive column of a table that the role is allowed to read. Column-level security grants access to selected columns in a table instead of the full table. Data in columns that you remove from the allowed list is not visible to users.

By default, users have access to all columns. To restrict a column, open Column security on the table in the role and set the column permission to Hide. At least one column must remain in the list of allowed columns. You can define column-level security for any Delta Parquet table.

Values and names are treated differently. The name of a secured column might be visible in certain experiences, but the data values never appear. In the SQL analytics endpoint, users might see columns in Object Explorer or sys.columns even when column-level security prevents them from reading those columns. This behavior is by design.

A query that asks for every column meets a problem: some of those columns are hidden. The engines solve it in different ways.

So the same role looks tolerant in a notebook and strict in the SQL analytics endpoint. The rule is the same in both. Only the handling of the request for every column differs.

The allow list is tied to column names. In the SQL analytics endpoint, renaming or removing an allowed column invalidates the security rule, and the rule then denies all access to the table until the original column naming is restored.

If the new name has to stay, the rule is what you change. Update or remove the affected roles, in the lakehouse where the role was created. Waiting does not help: when a policy is invalid, metadata sync is blocked by design until the rule is corrected in the OneLake security panel. A SQL GRANT does not help either: in user identity mode, where the endpoint enforces OneLake roles, you cannot define RLS, CLS, or OLS directly using T-SQL.

Row-level security: a rule that filters rows

Row-level security is for a table that everybody in the role may read, but not completely. You define roles that contain rules for filtering rows of data for members of the role. When a member of an RLS role queries the data, OneLake evaluates the RLS rules and returns only the allowed rows.

Rows are a concept relevant only to tabular data, so you cannot define RLS for non-table folders or unstructured data. You define RLS rules in a role that grants access to table data in Delta Parquet format.

Which engines can filter a table with these rules, and when OneLake blocks the read, is covered in the lesson on OneLake security enforcement.

Writing the row rule

To write the rule, open the role details page, select more options next to the table, and select Row security. In the code editor, type the SQL statement that defines which rows users can see, and select Save.

RLS rules use SQL syntax to specify the rows that a user can see. The syntax takes the form of a SQL SELECT statement with the RLS rules defined in the WHERE clause:

SELECT * FROM Sales WHERE Amount > 50000 AND State='CA'

Every rule starts with SELECT * FROM and the table name. The conditions come after WHERE. A member of a role with this rule sees the rows of Sales in which Amount is greater than 50000 and State is CA. A row with an Amount of exactly 50000 is not greater than 50000, so it stays hidden.

Each Boolean statement has a column name, an operator, and a static value or set of values to evaluate against.

RLS rules support only a subset of SQL.

AND needs both statements to be true. OR needs one of them to be true.

Three more facts decide how a rule behaves.

• Row-level security evaluates string data as case insensitive.
• The maximum number of characters in a row-level security rule is 1000.
• RLS roles do not support dynamic and multitable queries.

As a best practice, avoid vague or overly complex RLS expressions. Strongly typed expressions with integer or string lookups that use the equals operator are the most secure and the easiest to understand.

When a rule stops matching its table

A rule is written for one table and must keep matching it. The table shows what happens when it does not.

Two of these rows need a comment. To correct a rule that references a deleted or renamed column, update or remove the affected roles, or restore the missing column, and make the update in the lakehouse where the role was created.

A rename is the dangerous case. OneLake security roles are tied to the table name. Renaming a table breaks the association, and policies do not migrate automatically, which can result in unintended data exposure until policies are reapplied.

When a user has several roles

A user can belong to multiple OneLake security roles, and then the question is how the roles meet. OneLake first resolves each role independently. After resolving each role, OneLake combines the roles by using a union, or least-restrictive, model. If one role grants access to TableA and another grants access to TableB, a user who belongs to both roles can access both tables.

There is no deny to stop this. OneLake security supports only Grant roles, and you cannot deny access that is granted through a different role or permission model. Taking a table out of one role closes nothing while another role still grants it.

When several roles grant the same table, the rules combine as follows.

The lesson on OneLake security enforcement covers the evaluation of several roles in full, also through shortcuts. For the design of row and column rules, one consequence matters here. Because roles add up, rules that depend on each other must not be separated. Keep RLS and CLS rules that must apply together in the same role.

Row-level and column-level security can be used together to restrict user access to a table, but the two policies have to be applied using a single OneLake security role. OneLake security does not support the combination of two or more roles where one contains RLS rules and another contains CLS rules. Users that try to access tables that are part of an unsupported role combination receive query errors.

The same limit appears with column lists. OneLake does not support a role combination in which two roles allow a different set of columns for a table and either role also applies RLS to that table.

Who the rules do not restrict

Before you test a rule, check who you are in the workspace. OneLake security roles use a grant model, and you cannot deny access that is granted through a different role or permission model. For that reason, these controls do not restrict access for users in the Admin, Member, and Contributor roles at the workspace level. The roles are for users in the Viewer workspace role or with Read permission on the item.

So an Admin who runs a notebook on a table with a row rule sees every row. That result says nothing about what a Viewer in the role will see.

The rule has exceptions in the SQL analytics endpoint, and one of them is frequent. It needs the endpoint in user identity mode, the mode in which OneLake roles decide access to tables. When row-level security is configured in user identity mode, the defined security rules are enforced for all users, including those in Admin, Member, and Contributor roles.

So a Contributor who is a member of a role with a row rule can see all the rows of the table in a notebook and fewer rows of the same table in the SQL analytics endpoint. Both results are correct.

Key takeaways

• A folder grant inherits downward, and traversal opens only the path to it. Members can traverse the parent folders, but sibling files and folders stay out of reach.
• A request for every column depends on the engine. With hidden columns, Spark notebooks return the allowed columns, and the SQL analytics endpoint and semantic models return an error.
• A row rule is a SELECT * FROM statement with the conditions in the WHERE clause. AND needs both conditions, and a greater-than condition leaves out the value itself.
• A rule that does not match its table fails closed. A rule that references a missing column makes the query fail with no data.
• A renamed allowed column blocks the table in the SQL analytics endpoint until the role is updated in the lakehouse where it was created.
• The SQL analytics endpoint lists every schema name and hides only the tables that the user cannot read.
• A renamed table loses its rules. Roles are tied to the table name, and data can be exposed until the policies are reapplied.
• Column lists of several roles combine as a union, except in the SQL analytics endpoint, which intersects them. Row and column rules that go together belong in one role.
• Admin, Member, and Contributor are not restricted, except by row rules read through a SQL analytics endpoint in user identity mode.