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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Expanding the Synapse Forums

New forum boards available in Synapse

Ask questions in Data Engineering, Data Science, Data Warehouse and General Discussion.

MayFabricCarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors