Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
webchris
Resolver I
Resolver I

Fabric - 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.:

UserProduct
a@b.com2345
a@b.com1234
c@b.com2345
c@b.com5678

 

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 

 

1 ACCEPTED SOLUTION
webchris
Resolver I
Resolver I

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 ProductsPerUser

UserProduct
a@b.com2345
a@b.com1234
c@b.com2345
c@b.com5678

 

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
GO

Now, I can implement the security policy on each table that has the "Product" as a field.

 

Works like a charm...

 

 

View solution in original post

7 REPLIES 7
webchris
Resolver I
Resolver I

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 ProductsPerUser

UserProduct
a@b.com2345
a@b.com1234
c@b.com2345
c@b.com5678

 

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
GO

Now, I can implement the security policy on each table that has the "Product" as a field.

 

Works like a charm...

 

 

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 😀

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

@webchris thanks! 😀

fabricator1
Advocate II
Advocate 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)

Well, I found it already, it is like all the videos I found so far, just the 'direct' implemention to a table. 

fabricator1
Advocate II
Advocate II

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

November Update

Fabric Monthly Update - November 2024

Check out the November 2024 Fabric update to learn about new features.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.