Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Column-level security (CLS) is a feature of OneLake security (preview) that allows users to have access to selected columns in a table instead of full access to the table. CLS allows for specifying a subset of tables that users have access to. Any columns that are removed from the list aren't visible to users.
Note
OneLake security is currently in a limited preview. To request to join the preview and access these features, fill out the form at https://aka.ms/onelakesecuritypreview.
Prerequisites
- A Lakehouse in OneLake with OneLake data access roles turned on. For more information, see Get started with OneLake data access roles.
- A Lakehouse with schemas enabled.
- Switch the SQL Analytics Endpoint on the lakehouse to "User's identity" mode through the Security tab.
- For creating semantic models, use the steps to create a DirectLake model.
- For a full list of limitations, see the known limitations section.
Enforce column-level security
OneLake security CLS gets enforced in one of two ways:
- Filtered tables in Fabric engines: Queries to the list of supported Fabric engines, like Spark notebooks, result in the user seeing only the columns they're allowed to see per the CLS rules.
- Blocked access to tables: Tables with CLS rules applied to them can't be read outside of supported Fabric engines.
For filtered tables, the following behaviors apply:
- CLS rules don't restrict access for users in the Admin, Member, and Contributor roles.
- If the CLS rule has a mismatch with the table it's defined on, the query fails and no columns are returned. For example, if CLS is defined for a column that isn't part of the table.
- Queries of CLS tables fail with an error if a user is part of two different roles and one of the roles has row-level security (RLS).
- CLS rules can only be enforced for objects that are Delta parquet tables.
- CLS rules that are applied to non-Delta table objects instead block access to the entire table for members of the role.
- If a user runs a
select *query for a table where they only have access to some of the columns, CLS rules behave differently depending on the Fabric engine.- Spark notebooks: The query succeeds and only shows the allowed columns.
- SQL analytics Endpoint: Column access is blocked for the columns the user can't access.
- Semantic models: Column access is blocked for the columns the user can't access.
Define column-level security rules
Users can define column-level security as part of a OneLake security role for any Delta-parquet table in the Tables section of an artifact. CLS is always specified for a table and is either enabled or disabled. By default, CLS is disabled and users have access to all columns. Users can enable CLS and remove columns from the list to revoke access.
Important
Removing access to a column doesn't deny access to that column if another role grants access to it.
Use the following steps to define column-level security:
Navigate to your Lakehouse and select Manage OneLake data access (preview).
Select an existing role that you want to define table or folder security for, or select New to create a new role.
On the role details page, select more options (...) next to the table you want to define CLS for, then select Column security (preview).
By default, CLS for a table is disabled. Select Enable CLS or New rule to enable it.
The UI populates with a list of columns for that table that the users are allowed to see. This starts as all of the columns.
To restrict access to a column, select the checkbox next to the column name, then select Remove. At least one column must remain in the list of allowed columns.
Select Save to update the role.
If you want to readd a column, select New rule. This action adds a new CLS rule entry to the end of the list. Then, use the dropdown to choose the column you want to include in the access.
Once you complete your changes, select Save.
Enable OneLake security for SQL analytics endpoint
Before you can use OneLake security with SQL analytics endpoint, you must enable its User's identity mode. Newly created SQL analytics endpoints in a preview enabled workspace will default to user's identity mode, so these steps must be followed for existing SQL analytics endpoints.
Note
Switching to User's identity mode only needs to be done once per SQL analytics endpoint. Endpoints that are not switched to user's identity mode will continue to use a delegated identity to evaluate permissions.
Navigate to SQL analytics endpoint.
In the SQL analytics endpoint experience, select the Security tab in the top ribbon.
Select User's identity under OneLake access mode.
In the prompt, select Yes, use the user's identity.
Now the SQL analytics endpoint is ready to use with OneLake security.
Combine row-level and column-level security
Row-level and column-level security can be used together to restrict user access to a table. However, the two policies have to be applied using a single OneLake security role. In this scenario, access to data is restricted according to the rules that are set in the one role.
OneLake security doesn't 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.