Skip to content

Latest commit

 

History

History
76 lines (53 loc) · 3.34 KB

column-level-security.md

File metadata and controls

76 lines (53 loc) · 3.34 KB
Raw
title description author ms.author ms.reviewer ms.date ms.topic ms.custom
Column-level security in Fabric data warehousing
Learn about column-level security in tables in Fabric data warehousing.
WilliamDAssafMSFT
wiassaf
stwynant
04/24/2024
conceptual
ignite-2023

Column-level security in Fabric data warehousing

Applies to: [!INCLUDE fabric-se-and-dw]

Column-level security simplifies the design and coding of security in your application, allowing you to restrict column access to protect sensitive data. For example, ensuring that specific users can access only certain columns of a table pertinent to their department.

Column-level security at the data level

The access restriction logic is located in the database tier, not in any single application tier. The database applies the access restrictions every time data access is attempted, from any application or reporting platform including Power BI. This restriction makes your security more reliable and robust by reducing the surface area of your overall security system.

Column-level security only applies to queries on a Warehouse or SQL analytics endpoint in Fabric. Power BI queries on a warehouse in Direct Lake mode will fall back to Direct Query mode to abide by column-level security.

Restrict access to certain columns to certain users

In addition, column-level security is simpler and than designing additional views to filter out columns for imposing access restrictions on the users.

Implement column-level security with the GRANT T-SQL statement. For simplicity of management, assigning permissions to roles is preferred to using individuals.

Column-level security is applied to shared warehouse or lakehouse, because the underlying data source hasn't changed.

Only Microsoft Entra authentication is supported.

Examples

This example will create a table and will limit the columns that charlie@contoso.com can see in the customers table.

CREATE TABLE dbo.Customers
  (CustomerID int,
   FirstName varchar(100) NULL,
   CreditCard char(16) NOT NULL,
   LastName varchar(100) NOT NULL,
   Phone varchar(12) NULL,
   Email varchar(100) NULL);

We will allow Charlie to only access the columns related to the customer, but not the sensitive CreditCard column:

GRANT SELECT ON Customers(CustomerID, FirstName, LastName, Phone, Email) TO [Charlie@contoso.com];

Queries executed as charlie@contoso.com will fail if they include the CreditCard column:

SELECT * FROM Customers;
Msg 230, Level 14, State 1, Line 12
The SELECT permission was denied on the column 'CreditCard' of the object 'Customers', database 'ContosoSales', schema 'dbo'.

Next step

[!div class="nextstepaction"] Implement column-level security in Fabric Data Warehousing

Related content