title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | ms.custom | |
---|---|---|---|---|---|---|---|---|---|---|
Implement column-level security in Fabric data warehousing |
A guide to use column-level security in Fabric data warehousing. |
WilliamDAssafMSFT |
wiassaf |
stwynant |
04/24/2024 |
fabric |
data-warehouse |
how-to |
|
Applies to: [!INCLUDE fabric-se-and-dw]
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.
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.
- 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.
-
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.
- 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.
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.