Forum Discussion
Advocate IIIFabric - Row Level Security on Warehouse - Stage 2...
Good morning
I have one more question regarding the Row Level Security and how to implement it in Fabric warehouses.
In my setup, I do have one table, that shows all users and which products each of them is allowed to see, e.g.:
| User | Product |
| [email protected] | 2345 |
| [email protected] | 1234 |
| [email protected] | 2345 |
| [email protected] | 5678 |
So, as I can read in the documentation, a security policy would effect this table. But how can I now ensure, that the remaining list of products then limits all other tables in my warehouse (in which the product field is available)? (As far as I understood, the security policy needs to be implemented on all fact tables in my warehouse to work properly)
Do a need a second policy which then derives the result of the first policy?
Thanks for your thoughts and hints
Christian
Okay, I managed to find a solution...
In my setup, I do have one table, that shows all users and which products each of them is allowed to see, e.g.:
Table ProductsPerUserUser Product [email protected] 2345 [email protected] 1234 [email protected] 2345 [email protected] 5678 To derive the Products the user is allowed to see, I had to extend the security function as follows:
CREATE FUNCTION
[SC_Security].[fn_securityProducts](@Product AS INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT 1 AS result
FROM
ProductsPerUser PPU
INNER JOIN
Products PROD
ON PPU.Product = PROD.Product
WHERE (PPU.User = USER_NAME() OR USER_NAME() = 'dbo')
AND PROD.Product = @Product
GONow, I can implement the security policy on each table that has the "Product" as a field.
Works like a charm...
7 Replies
fabricator1Advocate II
This YouTube video may be helpful, although it doesn't touch upon the topic of applying a rule to multiple tables:
Row-Level security in Fabric Warehouse & SQL Endpoint (youtube.com)- webchris
Well, I found it already, it is like all the videos I found so far, just the 'direct' implemention to a table.
- webchris
Okay, I managed to find a solution...
In my setup, I do have one table, that shows all users and which products each of them is allowed to see, e.g.:
Table ProductsPerUserUser Product [email protected] 2345 [email protected] 1234 [email protected] 2345 [email protected] 5678 To derive the Products the user is allowed to see, I had to extend the security function as follows:
CREATE FUNCTION
[SC_Security].[fn_securityProducts](@Product AS INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT 1 AS result
FROM
ProductsPerUser PPU
INNER JOIN
Products PROD
ON PPU.Product = PROD.Product
WHERE (PPU.User = USER_NAME() OR USER_NAME() = 'dbo')
AND PROD.Product = @Product
GONow, I can implement the security policy on each table that has the "Product" as a field.
Works like a charm...
- fabricator1
That's nice!
I'm trying to do the same thing, and managed to apply the RLS to my tables.
However, it seems that the RLS also affects my own user.
Is there a way to make it so that any person who is workspace Contributor, Member or Admin won't get affected by the RLS?
Is that what the OR USER_NAME() = 'dbo' clause should do?
I tried that, but it didn't have any effect for my user (except if I hardcode my own username there)
Appreciate any hints or clues 😀- webchris
Hi fabricator1
I think the
OR USER_NAME() = 'dbo')
part solves this issue for the automatically running process.
Maybe you need to add your user_name as well as an exception.
Hope this helps
- fabricator1
I only have experience with setting up RLS directly in Power BI import mode semantic models.
There, the RLS can be propagated from dimension tables to fact tables by using the relationships between the tables (depending on the filtering direction of the relationships).
So my first thought would be to try to set up relationships in the modelling pane of the Data Warehouse, and see if the rules applied to the dimension tables get propagated to the fact tables. I have no clue if that would actually work, but may be worth a shot
(although I'm unsure if the relationships in the modelling pane only applies to the semantic model, and not to the data warehouse itself).
(In Power BI, you can choose to set up RLS on just one table - this will propagate to other tables if they are connected via relationships with filtering in the desired direction. Or you can set up rules on multiple tables directly.
I'm not sure if it works the same way in the data warehouse but that would be nice.)
By the way, RLS for the Direct Lake semantic model has been announced (although not yet made available). This will be similar to RLS in Power BI Desktop, I assume.
Ref. Microsoft Fabric November 2023 update | Microsoft Fabric-blogg | Microsoft Fabric under the header "RLS/OLS security and stored credentials for Direct Lake semantic models".
I guess this will be an alternative to applying RLS to the Data Warehouse. It should support Direct Lake mode (not fall back to Direct Query). However, this will probably only apply to the downstream semantic model and not to the data warehouse itself.