Implement column-level security in Fabric data warehousing
Applies to: SQL analytics endpoint and Warehouse in Microsoft Fabric
Column-level security (CLS) in Microsoft Fabric allows you to control access to columns in a table based on specific grants on these tables. For more information, see Column-level security in Fabric data warehousing.
This guide will walk you through the steps to implement column-level security in a Warehouse or SQL analytics endpoint.
Prerequisites
Before you begin, make sure you have the following:
- A Fabric workspace with an active capacity or trial capacity.
- A Fabric Warehouse or SQL analytics endpoint on a Lakehouse.
- Either the Administrator, Member, or Contributor rights on the workspace, or elevated permissions on the Warehouse or SQL analytics endpoint.
1. Connect
- Log in using an account with elevated access on the Warehouse or SQL analytics endpoint. (Either Admin/Member/Contributor role on the workspace or Control Permissions on the Warehouse or SQL analytics endpoint).
- Open the Fabric workspace and navigate to the Warehouse or SQL analytics endpoint where you want to apply column-level security.
2. Define column-level access for tables
Identify user or roles and the data tables you want to secure with column-level security.
Implement column-level security with theĀ GRANT T-SQL statement and a column list. For simplicity of management, assigning permissions to roles is preferred to using individuals.
-- Grant select to subset of columns of a table GRANT SELECT ON YourSchema.YourTable (Column1, Column2, Column3, Column4, Column5) TO [SomeGroup];
Replace
YourSchema
with the name of your schema andYourTable
with the name of your target table.Replace
SomeGroup
with the name of your User/Group.Replace the comma-delimited columns list with the columns you want to give the role access to.
Repeat these steps to grant specific column access for other tables if needed.
3. Test column-level access
- Log in as a user who is a member of a role with an associated GRANT statement.
- Query the database tables to verify that column-level security is working as expected. Users should only see the columns they have access to, and should be blocked from other columns. For example:
SELECT * FROM YourSchema.YourTable;
- Similar results for the user will be filtered with other applications that use Microsoft Entra authentication for database access.
4. Monitor and maintain column-level security
Regularly monitor and update your column-level security policies as your security requirements evolve. Keep track of role assignments and ensure that users have the appropriate access.
Related content
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for