Microsoft Fabric Row-Level Security (RLS) & Column-Level security(CLS) in Warehouse and SQL Endpoint

[mahes-a]

Row-Level Security (RLS) and Column-Level Security (CLS) are methods that simplify the design and coding of security in applications by imposing restrictions on data access. CLS allows for specific users to access only certain columns of a table relevant to their department, protecting sensitive data. RLS, on the other hand, restricts users to accessing only data rows pertinent to their role or department. Both methods locate the access restriction logic in the database tier, applying the restrictions every time data access is attempted from any tier, making the security system more reliable and robust.

In Microsoft Fabric Warehouse and SQL Endpoint

•  Row-Level Security (RLS) is implemented by using the CREATE SECURITY POLICY Transact-SQL statement, and predicates created as inline table-valued functions.

• Column-level security is implemented using the GRANT T-SQL statement. 

Implementing Row-Level Security (RLS) in Microsoft Fabric Warehouse

1. This example provides how we can grant row level access to two users (sales user and a manager user) in Fabric.
2. We will create and populate a table with four rows.
3. We will then create an inline table-valued function and a security policy for the table.
4. The example then shows how we grant access so the select statements output rows will be filtered for the using RLS implementation.
5. The sales user will be able to view only his sales while the manager user will have all access to view all the sales records. 

• Access to the sales and manager user will be provided using the Workspace roles. From Workspace -> Manage access -> Add people or groups and add the user

Visit Security for data warehousing to learn more about the security topics for securing the SQL Endpoint of the lakehouse and the Warehouse in Microsoft Fabric

• Create and populate table.

--Create a Schema
CREATE SCHEMA [Sales]
GO
--Create a table to hold data.
CREATE TABLE [Sales].[SaleDetails](
	[SaleID] [int] NOT NULL,
	[ProductID] [varchar](100),
	[ProductName] [varchar](100) ,
	[SalesPersonID] [varchar](100) 
) 
GO
--populate the table with four rows of data, showing orders for each sales representative.
INSERT INTO Sales.SaleDetails  VALUES (1, 'Prd1', 'ProductName1', 'salesusr@contoso.com');
INSERT INTO Sales.SaleDetails  VALUES (2, 'Prd2', 'ProductName2', 'salesusr1@contoso.com');
INSERT INTO Sales.SaleDetails  VALUES (3, 'Prd3', 'ProductName2', 'salesusr3@contoso.com');
INSERT INTO Sales.SaleDetails  VALUES (4, 'Prd4', 'ProductName3', 'salesusr4@contoso.com');

• Create a new schema, and an inline table-valued function. The function returns 1 when a row in the SalesPersonID column is the same as the user executing the query (@SalesRep = SUSER_SNAME()) or when the manager user is executing the query
  USER_NAME() = 'manageruser@contoso.com';  This example of a user-defined, table-valued function is useful to serve as a filter for the security policy created in the next step.

--create schema
CREATE SCHEMA Security;
GO

--create a inline security function 
CREATE FUNCTION Security.tvf_finsecuritypredicate(@SalesRep AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS tvf_finsecuritypredicate_result
WHERE @SalesRep = SUSER_SNAME()  OR  USER_NAME() = 'manageruser@contoso.com';  
GO

• Create a security policy adding the function as a filter predicate. The state must be set to ON to enable the policy

CREATE SECURITY POLICY SalesFinFilter
ADD FILTER PREDICATE Security.tvf_finsecuritypredicate(SalesPersonID)
ON Sales.SaleDetails
WITH (STATE = ON);
GO

• Let's test the filtering predicate, by selecting from the Sales.SaleDetailstable as each user.

• The sales user should only see their own sales

•  Manager user and the user should see all four rows. 

Implementing Column-Level Security (CLS) in Microsoft Fabric SQL Endpoint

The example will use Grant Statement to provide members of the Security Group with access to view only few columns of a table. 

• Create a Microsoft Entra ID security group (Azure Active Directory is now Microsoft Entra ID) and make sure that the Group Type is Security.

• Add users to the Security Group 

• Using the share icon  of the lakehouse, share the lakehouse to the security group, ensure no "Additional Permissions" are provided when sharing. 

• Open the SQL endpoint of the lakehouse and open the Query window using "New SQL query" and use the Grant statement to provide access to selected columns of the customer details table.

GRANT SELECT ON [ProductLakeHouse].[dbo].[customerdetails]([CustomerID],[CustomerName],[PhoneNumber]) TO [CLSAcessAADgrp];

• Copy the connection string for the SQL endpoint and use Sql Management Studio to login as the user who is part of the security group created in previous steps and validate the user should be able to view the specified columns of the table.

Summary

In conclusion, this guide provides the steps to implement Row-Level Security (RLS) and Column-level security on Microsoft Fabric Warehouse and SQL Endpoint.