I have always been a strong proponent of data level access and controls being implemented at the source layer instead of the presentation layer. This approach centralizes security management and reduces the overhead of configuring access in multiple tools that can ensure consistent access even if users switch across tools.
SQL Server 2016 was a game changer with the new security feature RLS(Row Level Security).With this feature it was possible to implement granular access at the row level based on user privilege's. Prior to SQL Server 2016, in the earlier versions one had to implement own custom security feature to limit/grant/deny row level access which most of the time wouldn’t be that robust and carried a maintenance overhead.
My recent blogs have been focused on the security aspects of Fabric and Azure. In this blog, we will delve into how we can leverage service principals to implement Row-Level Security (RLS) in Fabric Data warehouse and the limitations that you must be aware of before implementing it.
Keep in mind that if you have implemented Row-Level Security (RLS) for the Data Warehouse on Fabric, the Power BI semantic model will abide to the row-level security settings of the data warehouse. So if you are using Direct lake in your semantic model then Direct Lake will fallback to the Direct Query mode and if the semantic model uses import mode then it will ignore RLS of the data warehouse.
RLS in SQL Server
In RLS for SQL Server you have two predicates the Filter predicate and the Block predicate.
Filter predicate: The filter predicate filters the rows based on the user privileges the user has access to. This happens “silently” without the user being aware that there are other rows present in the same table that he accessing. This is different to say cryptographic hashing of rows that’s implemented using the MD2, MD4, MD5,SHA2_256, SHA2_512,SHA1 or SHA algorithms through the HASHBYTES function. The hashed rows will be visible to the users who are not authorized to view its content as the data would be in encrypted format along the rows the user has access to.
Just a side note : MD2, MD4, MD5, SHA, and SHA1 algorithms are deprecated in SQL Server 2016 and beyond. Using the SHA2_256 or SHA2_512 is strongly recommended. This is applicable for Fabric Data warehouse as well if at all you decide to encrypt your data through hashing algorithms.
Block predicate: The block predicate basically prevents unauthorized users write operations that violates the pre defined predicate. Lets see a use case.
There are two sales associates named Alice and Bob. Now the business requirement is that the user Alice shouldn't insert data for Bob and Bob shouldn't insert data for Alice in a table called CustomerInformation.This table has three columns CustomerName,CustomerPhone and SalesPerson. Prior to RLS there was no inbuild mechanism to prevent this and one had to implement a custom business rule. If we use the DENY command in SQL Server for the table say
DENY INSERT ON CustomerInformation TO Alice
This command would completely deny any insert operation to Alice on CustomerInformation table which isnt what we want. This is where Block predicate is very handy. We will see if at all it can be implemented in Fabric
The SetUp
To get started you would have to set up a service principal on entra.microsoft.com.
You can find the step by step process here.
Once its done ensure you have the ApplicationId and Client secrets noted down which is required to connect to Fabric warehouse through SSMS.
Now to the fun part
We will start by creating a demo table in data warehouse that holds the customer orders information for which we will configure Row-Level Security.
Lets start by creating a data warehouse in the fabric workspace. I created one with name DW_RLS.
In fabric, all your data warehouse objects are assigned to a default schema dbo just like SQL pool (formerly SQL DW) in Azure Synapse Analytics and on prem SQL Server.
Next, create a schema named Orders in the data warehouse and a table with name CustomerOrders under this schema.
CREATE SCHEMA Orders;
GO
CREATE TABLE Orders.CustomerOrders (
CustomerID INT,
CustomerName VARCHAR(500),
OrderID INT,
OrderDate DATE,
OrderAmount DECIMAL(10, 2),
[User] VARCHAR(500)
);
If we do not explicitly assign the table to a schema the table gets assigned to the default schema dbo .This is standard across all editions of SQL Server whether on Azure or on prem.
I created two tables with the similar name under two separate schemas. We will focus on the CustomerOrders table under the Ordersschema.
My Fabric workspace has three users. Two are service principals that have Contributor access for the workspace while the other user which is me has admin access. This by default grants me admin access to all the underlying objects under that workspace.
For conciseness I am gonna switch to SSMS from Fabric UI.
If you wanna understand how you to connect to Azure or Fabric data warehouse using SSMS you can refer to my this post on LinkedIn. The article focusses on connecting to Azure SQL DB through service principals on SSMS, though the underlying steps are exactly similar while connecting to a Fabric data warehouse. Only difference being that, for the Server Name you will have to use the connection string of Fabric data warehouse that can be retrieved from settings option seen in the above image.
I will simultaneously connect to the data warehouse through my user name and the two service principals, Fabric MSAL and Fabric OAUTH2.
Point to be Noted : Unlike Azure SQL DB there is NO option to create user for databases. Like if you refer my article on Managed Identity and Service Principal on Azure, you would notice that I had to explicitly add the managed identity and service principal as a database user to access the underlying database. But this is NOT an option in fabric which I personally believe to be a big drawback. Trying to create a database user ends up with an error.
It seems that all registered users get unlimited access for the Fabric data warehouse. I am unsure what is the roadmap for fabric on this aspect.
Anyways, next we will now create a TVF(Table Valued Function)that returns the username of the current logged in user accessing the table. Ensure you create it under the correct schema name which is Orders.
CREATE OR ALTER FUNCTION Orders.tvf_CheckUser(@UserName AS varchar(500))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS tvf_CheckUser
WHERE @UserName = USER_NAME();
GO
Next, we create a security policy to define the access level of the user
CREATE SECURITY POLICY CustomerOrdersSecurityPolicy
ADD FILTER PREDICATE Orders.tvf_CheckUser([User])
ON Orders.CustomerOrders
WITH (STATE = ON);
GO
The function above checks if the user value of the logged in user accessing the table Orders.CustomerOrders matches the value in the [User]column in the table Orders.CustomerOrders.If it does , then it allows only those relevant rows returned to the output and discards the rest.
Lets insert some mocked up data in table Orders.CustomerOrders to test all that we created till now.
INSERT INTO Orders.CustomerOrders (CustomerID, CustomerName, OrderID, OrderDate, OrderAmount, User) VALUES
(1, 'John Doe', 101, '2024-10-01', 250.75, 'sachin.nandanwar@azureguru.net'),
(2, 'Jane Smith', 102, '2024-10-02', 150.00, 'db5baae3-0000-4744-xxxx-5291acfa1ec3@5f9f6902-xxxx-407e-0000-xxxx3d7798xx'),
(3, 'Mike Johnson', 103, '2024-10-03', 300.50, 'sachin.nandanwar@azureguru.net'),
(4, 'Emily Davis', 104, '2024-10-04', 450.20, 'db5baae3-0000-4744-xxxx-5291acfa1ec3@5f9f6902-xxxx-407e-0000-xxxx3d7798xx'),
(5, 'Chris Brown', 105, '2024-10-05', 120.30, 'sachin.nandanwar@azureguru.net'),
(6, 'Sarah Wilson', 106, '2024-10-06', 500.00, 'db5baae3-0000-4744-xxxx-5291acfa1ec3@5f9f6902-xxxx-407e-0000-xxxx3d7798xx'),
(7, 'David Taylor', 107, '2024-10-07', 275.40, 'sachin.nandanwar@azureguru.net'),
(8, 'Laura Martinez', 108, '2024-10-08', 180.65, 'User1@abc.net'),
(9, 'James Anderson', 109, '2024-10-09', 350.80, 'User2@def.net'),
(10, 'Emma Thompson', 110, '2024-10-10', 400.25, 'User3@xyz.net');
The above insert statements inserts four rows for sachin.nandanwar@azureguru.net and three rows for service principal Fabric MSAL and no rows for service principal Fabric OAUTH2.Three rows for “other” users are also inserted. So in total ten rows are inserted to the table.
Now lets test this thing out. If the gif is blurry or unclear then please right click the gif and open it in a new tab.
Now you might think what happened with the Block predicate and why I didn’t implement it. Well, Fabric data warehouse RLS does not support Block Predicate. Trying to create it errors out which I think isnt much of an issue as it’s a data warehouse and not an OLTP system.
Creating a Block predicate errors out.
Conclusion
As seen Row-Level Security (RLS) provides a robust mechanism to control access to specific rows within the fabric warehouse through service principal. By implementing RLS, one can ensure that users can only view or interact with the data they are authorized to access. As RLS policies are centrally managed ,it makes them consistent across different applications that query the data. This approach simplifies security management as rules and policies are applied automatically without the need for additional layers of filtering. Overall, RLS is an effective way to safeguard sensitive information and granular level data access control.
Thanks for Reading !!!
