Microsoft Fabric Warehouse Security: Object, Column and Row Level

By: Koen Verbeeck | Updated: 2024-07-30 | Comments (2) | Related: > Microsoft Fabric

Problem

We are implementing a data analytics solution in Microsoft Fabric. A warehouse is used for the gold layer, and we want to give users access to the data. However, by sharing the warehouse, they can read all the data in all the tables. Some data is sensitive, and only users with the correct permissions should be able to view it. Is it possible to implement more granular access control to the data?

Solution

For an introduction to Microsoft Fabric and the warehouse workload (along with important concepts), check out the following tips:

In a previous tip, Microsoft Fabric Warehouse - Configure Access and Permissions we discussed how to share access to a warehouse in a Fabric workspace.

This could be done through either assigning a workspace role (such as Viewer) or sharing the warehouse itself. With a workspace role, a user can view all the warehouses and SQL Analytical endpoints and the data inside them. In contrast, with a shared warehouse, the user can only view the data of that particular database.

You could state that using one of the two methods is equivalent to assigning a user to the db_datareader role in a SQL Server database. In this tip, we'll explore the options available to implement a more fine-grained access to the data. Similar to the other tip, I'm using the fictional user Alice as the "test subject" for the security implementation.

Implementing Security in the Microsoft Fabric Warehouse

Object-level Security

With object-level permissions, we can control access to specific database objects, like tables, views, stored procedures, etc. This can be achieved with the T-SQL commands GRANT, DENY, and REVOKE, just as in SQL Server. For an introduction to these concepts, please refer to Understanding GRANT, DENY, and REVOKE in SQL Server.

Let's illustrate with an example. Alice has the ReadData role on the warehouse, which means she can currently query all tables. With the following statement, we explicitly deny her access to a particular table:

DENY ALL ON OBJECT::dbo.dimension_employee TO [[email protected]];

With the following T-SQL statement, we can verify the assigned permissions:

SELECT DISTINCT
     pr.principal_id
    ,pr.[name]
    ,pr.[type_desc]
    ,pr.authentication_type_desc
    ,pe.state_desc
    ,pe.[permission_name]
FROM sys.database_principals  pr
JOIN sys.database_permissions pe ON pe.grantee_principal_id = pr.principal_id;

explicit permissions set on a table for Alice

When Alice logs in, she won't be able to query the table:

As a user, you can check your permissions using the fn_my_permissions function. At the database level, we can verify Alice can connect to the warehouse:

SELECT *
FROM sys.fn_my_permissions(NULL, 'Database');

Alice has the CONNECT permission on the database

However, when run on the table, nothing is returned.

SELECT *
FROM sys.fn_my_permissions('dbo.dimension_employee', 'Object');

This is because Alice is denied access to this table. When we run this statement for another table, we can see Alice has SELECT permissions on all the columns of the table:

SELECT *
FROM sys.fn_my_permissions('dbo.dimension_city', 'Object');

alice has select permissions on all columns

Column-Level Security

Instead of granting permissions on the table (or view) level, it's possible to have more fine-grained access to the data. This can be done with the same GRANT statement we used before. First, we'll revoke all access to the dbo.dimension_city table:

DENY ALL ON OBJECT::dbo.dimension_city TO [[email protected]];

Then, we'll assign SELECT permission to a specific set of columns:

GRANT SELECT ON dbo.dimension_city(City, StateProvince, Country, Continent)
TO [[email protected]];

When Alice does a SELECT * on the table, several error messages will be thrown stating she doesn't have access to certain columns:

not all columns of the table can be selected

When Alice runs fn_my_permissions for the table, only the four columns from the GRANT are retrieved:

Row-Level Security

In the previous two sections, we specified how to limit access to objects, such as tables or views, or specific columns. With row-level security (RLS), we define which rows a particular user can retrieve. For example, if a user is responsible for a specific sales region, it's possible to configure RLS so that the user only sees sales for that particular region. Implementing RLS in Microsoft Fabric is exactly the same as in SQL Server (or other related products). You can read more about it in the tip: SQL Server 2016 Row Level Security Introduction.

Let's illustrate the concept with an example. Suppose Alice is the sales territory manager of the Mideast region. When she queries the sales data, she should see only data for her region. First, we need to map users to sales territories. With the following view, Alice is assigned to the Mideast:

CREATE OR ALTER VIEW dbo.SalesTerritoryUsers
WITH SCHEMABINDING AS
SELECT DISTINCT
     SalesTerritory
    ,SalesPerson = IIF(SalesTerritory = 'Mideast','[email protected]','Someone else')
FROM dbo.dimension_city;

Keep in mind that when working with RLS in the warehouse, all views need to be schema bound (which is set by the WITH SCHEMABINDING option). Next, we create a view on top of the fact sales table to incorporate the sales territory and the user:

CREATE OR ALTER VIEW dbo.fact_sale_rls
WITH SCHEMABINDING AS
SELECT
     c.SalesTerritory
    ,u.SalesPerson
    ,s.SaleKey
    ,s.CityKey
    ,s.CustomerKey
    ,s.BillToCustomerKey
    ,s.StockItemKey
    ,s.InvoiceDateKey
    ,s.DeliveryDateKey
    ,s.SalespersonKey
    ,s.WWIInvoiceID
    ,s.[Description]
    ,s.Package
    ,s.Quantity
    ,s.UnitPrice
    ,s.TaxRate
    ,s.TotalExcludingTax
    ,s.TaxAmount
    ,s.Profit
    ,s.TotalIncludingTax
    ,s.TotalDryItems
    ,s.TotalChillerItems
    ,s.LineageKey
FROM dbo.fact_sale           s
JOIN dbo.dimension_city      c ON s.CityKey         = c.CityKey
JOIN dbo.SalesTerritoryUsers u ON c.SalesTerritory  = u.SalesTerritory;

The idea is that when Alice queries this view, she will only get data for the Mideast region. Once we have our views, we need to define a couple of security objects. We're putting them in a separate schema called sec:

CREATE SCHEMA sec;

Then, we create a table-valued function that will return a result when the input parameter matches the name of the current user. As an admin, we want to be able to see all rows, so we add another check to see if the user name is equal to that of the admin.

CREATE FUNCTION sec.tvf_securitypredicate(@SalesRep AS nvarchar(50))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN 
SELECT result = 1
WHERE   @SalesRep   = USER_NAME()
    OR  USER_NAME() = '[email protected]';

Finally, we create a security policy on the sales view. This security policy passes the SalesPerson column into the table-valued function we just created. If the current user matches the salesperson from the view, the row will be returned.

CREATE SECURITY POLICY SalesTerritoryFilter
ADD FILTER PREDICATE sec.tvf_securitypredicate(SalesPerson)
ON dbo.fact_sale_rls
WITH (STATE = ON);

When we select data from the view, we get all the data because we're the admin:

When Alice runs the same query, she only gets data for the Mideast region, as expected:

alice only gets data for the mideast region

How to Circumvent Warehouse Security

There's a very important caveat when working with the warehouse security controls we explained in the previous sections. If the user can access the data through Spark (or through OneLake), they will be able to see all data and all tables. This can happen when a user has been assigned the ReadAll role when sharing a warehouse (see the tip Microsoft Fabric Warehouse - Configure Access and Permissions for more info on this topic). For example, when Alice browses OneLake (using Azure Storage Explorer), she can see all the Parquet files that make up the delta table.

Alice can see all parquet files of the table and preview them without any restriction

As you can see in the preview of the Parquet file, Alice can see all the columns of the dimension_city table, even though we restricted access with column-level security.

When implementing security in the warehouse, ensure all users only have access to the warehouse itself, not other workloads such as Spark. Microsoft is working on a security feature called "OneSecurity," where security can be defined in a single place and then carried to all the workloads. However, it's not clear when this feature will be released.

Next Steps

For more information about security in Fabric, you can check out the official documentation.
You can find a list of all Microsoft Fabric tips in this overview.

About the author

Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-07-30

Microsoft Fabric Warehouse Security: Object-Level, Column-Level and Row-Level