Data Security in Microsoft Fabric Datawarehouse & Lakehouse (SQL Endpoint)

Published in

Tech Start

7 min readMar 15, 2024

This blog post will delve into the diverse security features of the Microsoft Fabric Data Warehouse and Lakehouse SQL Endpoint. First, let’s examine the various methods to implement data security in Fabric Data Warehouse, and SQL endpoint in Lakehouse:

- Implementing standard access control at the object level with GRANT and DENY statements.
- Enforcing Row-Level Security to ensure users only see the data they are permitted to access.
- Instituting column-level security to safeguard sensitive information from unauthorized access.
- Utilizing Data Masking to obscure the data for additional protection.

Row Level Security (RLS) in Microsoft Fabric Datawarehouse from the mapping table.

Overview

Row-level security (RLS) in Fabric Warehouse and SQL analytics endpoint (in Lakehouse) allows you to control access to rows in a database table based on user roles and predicates. For example, you can ensure that users can only access the rows in a table that are assigned to their sales region. RLS is a great way to implement security in your datawarehouse. In this document, we will explain how to implement RLS in your datawarehouse using the mapping table.

How the Row Level Security works?

Here is the document that describes Row Level Security in Data Warehouses and SQL Endpoints. Row-level security in Fabric Synapse Data Warehouse & SQL Endpoint supports predicate-based security. Filter predicates silently filter the rows available to read operations.

Setup

Step by Step guide on how to setup RLS in your datawarehouse. [link]

Here are the main steps to setup RLS in your datawarehouse for this example:

Create a mapping table

CREATE TABLE sales.SalesRepCountryMapping (
    SalesRep VARCHAR(100),
    SalesEmailId VARCHAR(100),
    CountryCode CHAR(2)
);
-- Insert sample data
INSERT INTO sales.SalesRepCountryMapping (SalesRep, SalesEmailId, CountryCode)
VALUES
    ('LeeG', 'LeeG@CONTOSO.OnMicrosoft.com', 'US'),
    ('AlexW', 'AlexW@CONTOSO.OnMicrosoft.com', 'CA')

-- provide access to the Admin
INSERT INTO sales.SalesRepCountryMapping (SalesRep, SalesEmailId, CountryCode)
VALUES
    ('ADMIN', 'admin@CONTOSO.onmicrosoft.com', 'UK'),
    ('ADMIN', 'admin@CONTOSO.onmicrosoft.com', 'US'),
    ('ADMIN', 'admin@CONTOSO.onmicrosoft.com', 'CA'),
    ('ADMIN', 'admin@CONTOSO.onmicrosoft.com', 'IN')

2. create 2 transactional table where we would like to implement RLS.

Creating the SalesByCountry

CREATE TABLE sales.SalesByCountry (
    CountryCode CHAR(2),
    SalesDate DATE,
    ProductID INT,
    QuantitySold INT,
    Revenue DECIMAL(10, 2)
);
INSERT INTO sales.SalesByCountry (CountryCode, SalesDate, ProductID, QuantitySold, Revenue)
VALUES
    ('US', '2024-02-27', 101, 50, 2500.00),
    ('US', '2024-02-27', 102, 30, 1800.00),
    ('CA', '2024-02-27', 101, 20, 1000.00),
    ('CA', '2024-02-27', 103, 15, 750.00),
    ('UK', '2024-02-27', 102, 25, 1500.00),
    ('UK', '2024-02-27', 104, 10, 500.00),
    ('DE', '2024-02-27', 101, 40, 2000.00),
    ('DE', '2024-02-27', 103, 18, 900.00),
    ('FR', '2024-02-27', 102, 22, 1320.00),
    ('FR', '2024-02-27', 105, 12, 720.00);

Creating the salesTargetData

CREATE TABLE sales.salesTargetData (
    CountryCode NVARCHAR(2), -- e.g., 'US', 'CA', 'UK'
    SalesDate DATE,
    ProductID INT,
    QuantitySold INT,
    Revenue DECIMAL(18, 2),
    Target DECIMAL(18, 2),
    Achievement DECIMAL(18, 2)
);
INSERT INTO sales.salesTargetData (CountryCode, SalesDate, ProductID, QuantitySold, Revenue, Target, Achievement)
VALUES
    ('US', '2024-02-27', 101, 50, 2500.00, 3000.00, 0.83),
    ('US', '2024-02-27', 102, 30, 1800.00, 2000.00, 0.90),
    ('CA', '2024-02-27', 101, 20, 1000.00, 1200.00, 0.83),
    ('CA', '2024-02-27', 103, 15, 750.00, 900.00, 0.83),
    ('UK', '2024-02-27', 102, 25, 1500.00, 1800.00, 0.83),
    ('UK', '2024-02-27', 104, 10, 500.00, 600.00, 0.83),
    ('DE', '2024-02-27', 101, 40, 2000.00, 2400.00, 0.83),
    ('DE', '2024-02-27', 103, 18, 900.00, 1080.00, 0.83),
    ('FR', '2024-02-27', 102, 22, 1320.00, 1600.00, 0.83),
    ('FR', '2024-02-27', 105, 12, 720.00, 900.00, 0.80);

3. Creating a function for the SalesByCountry & salesTargetData evaluation

Here we are mapping the SalesEmailId with the CountryCode and then using the mapping table to evaluate the SalesByCountry and salesTargetData table.

-- Creating a function for the SalesByCountry evaluation
CREATE FUNCTION Security.fn_security_predicateSales_by_country_code(@country_code AS varchar(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_security_predicateSales_by_country_code_result
    FROM
        sales.SalesRepCountryMapping as mapping
        inner join sales.SalesByCountry as sales on mapping.CountryCode = sales.CountryCode
        WHERE mapping.SalesEmailId = USER_NAME()
        AND sales.CountryCode = @country_code

-- Creating a function for the SalesTargetData  evaluation
CREATE FUNCTION Security.fn_security_predicate_SalesTargetData_by_country(@country_code AS varchar(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_security_predicate_SalesTargetData_by_country_result
    FROM
        sales.SalesRepCountryMapping as mapping
        inner join sales.SalesTargetData as sales on mapping.CountryCode = sales.CountryCode
        WHERE mapping.SalesEmailId = USER_NAME()
        AND sales.CountryCode = @country_code

4. Creating a security policy for the SalesByCountry & salesTargetData table

Row-level security in Fabric Synapse Data Warehouse supports predicate-based security. Filter predicates silently filter the rows available to read operations.

More information on how to create a security policy can be found here

-- Creating a security policy for the SalesByCountry table
CREATE SECURITY POLICY sales.SalesByCountryFilter
ADD FILTER PREDICATE Security.fn_security_predicateSales_by_country_code(CountryCode)
ON sales.SalesByCountry
WITH (STATE = ON);
GO

-- Creating a security policy for the SalesTargetDataFilter table
CREATE SECURITY POLICY sales.SalesTargetDataFilter
ADD FILTER PREDICATE Security.fn_security_predicate_SalesTargetData_by_country(CountryCode)
ON sales.salesTargetData
WITH (STATE = ON);
GO

Output

After setting up the RLS in your datawarehouse, you can now test the RLS by running the following query:

Testing Row-Level Security (RLS) involves executing queries as both an admin and a normal user to ensure proper access control to rows in a database table.

Here is the mapping table:

-- Please note that the Admin/ Normal user can only see the countries which are specified in the mapping table
select USER_NAME(), * from sales.SalesByCountry

when user: admin is exceuting the query:

when Alex is executing the query:

Before executing the query, ensure that Alex has been granted access to the data warehouse and the specific table. We have already provided Alex with access at the workspace level, it means he is going to have access to all the Datawarehouse tables.

Implement Object-Level Security in conjunction with Column-Level Security.

In the scenario described, Alex was able to access the table without explicit permissions because he was granted “Reader” access at the workspace level, which allowed all data warehouses within the Fabric workspace to inherit this access.

To test object-level security and column-level security, we will revoke the user’s workspace access and grant them access to a specific table in the Data Warehouse ([sales].[SalesByCountry]). The admin will use the GRANT statement, specifying the columns the user needs access to, in order to restrict access to sensitive columns.

GRANT SELECT ON sales.SalesByCountry (CountryCode, SalesDate, ProductID, QuantitySold)TO [AlexW@CONTOSO.OnMicrosoft.com];

As Alex does not have workspace-level access, he must connect to the data warehouse using a client tool like SQL Server Management Studio (SSMS). We encounter an error when Alex attempts to access the Revenue column. Additionally, it is observed in the screenshot that Alex can only view the sales.SalesByCountry table, to which we have granted access.

Here is the source code for this blog.

Data Security in SQL Endpoint in Lakehouse

As described above for the Datawarehouse, for the SQL Endpoint as well the same code and concept applies. For example, we provide Alex access to SQL Endpoint (Lakehouse) without providing any explicit access (options are unchecked).

Then ADMIN executes the following query create the RLS on the table. The table that we are referring is a shortcut table from a different Datawarehouse (DW1), so we can have the RLS and CLS on the shortcut table as well, along with the internal table.

Please note that for the shortcut table, we need to apply the RLS on the SQL Endpoint itself regardless of if the base table already got a RLS applied.

-- RLS

CREATE FUNCTION fn_security_predicateSales_by_country_code1(@country_code AS varchar(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_security_predicateSales_by_country_code_result
    FROM
        dbo.salesrepcountrymapping as mapping
        inner join dbo.salesTargetDataDW1 as sales on mapping.CountryCode = sales.CountryCode
        WHERE mapping.SalesEmailId = USER_NAME()
        AND sales.CountryCode = @country_code

GO
CREATE SECURITY POLICY salesTargetDataDW1Filter1
ADD FILTER PREDICATE dbo.fn_security_predicateSales_by_country_code1(CountryCode)
ON dbo.salesTargetDataDW1
WITH (STATE = ON);
GO

-- CLS
GRANT SELECT ON  [LH1].[dbo].[salesTargetDataDW1] (CountryCode, SalesDate, ProductID, QuantitySold) 
TO [AlexW@CONSTOSO.OnMicrosoft.com]