Is it possible to restrict SQL query result visibility by column data using RLS?

[MNollette]

I have an inventory management application that currently uses SSRS, and passes a user's access level as a parameter to determine what results can be returned. We're trying to start using Power BI instead, but since Power BI doesn't support the passing of parameters through a web application (without using Embedded, anyways, which we can't use), we are trying to implement RLS as a workaround. 

In the report example here I have three records being pulled - one each with access level 1, 3, and 5. Is it possible to create a role in RLS that allows a user to only see records with their access level or lower - I.e. based on the data being pulled from the SQL Server database? Most of what I see in RLS restricts visibility on visuals or columns, but I haven't been able to find anything on restricting access to actual data.

[camargos88]

@MNollette ,

If your query returns the Access Level you can create 5 roles (one to each value) and associate the users to them.

Or you can create a table mapping the users to the access level and only 1 roles and associate all the users there.

RLS can be done with fixed value (in the case of 1/2/3/5) or by the user logged (dynamic).

https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls

[MNollette]

Not quite sure it qualifies as on the fly, since I do need it to consistently do the same thing. I need five roles, each of them only being able to view rows if the row's access level is equal to or lower than theirs. 

As far as data, just assume a SQL Server table with columns Inv_Number, Description, Date, and Access Level. Access level is an integer ranging from 1-5, and my query returns all records from this table - I just need to be able to ensure that each user has a role assigned to them to prevent them from viewing rows that exceed their access level. 

[v-deddai1-msft]

Hi @MNollette ，

You could create 5 roles based on your sample:

[Access level] <=1
[Access level] <=2
[Access level] <=3
[Access level] <=4
[Access level] <=5

If this post help, please consider accept it as the solution to help other member find it more quickly.

Best Regards,

Dedmon Dai

[camargos88]

@MNollette ,

I haven't seen rls rules on the fly...

however you can create generic rules and apply the logic on the query, so each row has a identifier to associate to a rule. Maybe this is a way to go.

Can you provide more examples of your scenario ? Like a dummy data...

[camargos88]

@MNollette ,

RLS is on row level, you can create rules and associate users on them (or dynamically)....

Also, depends on what parameters you need, it's possible to use on Power BI Service, but they are static for the users... 

https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls

https://community.powerbi.com/t5/Community-Blog/Using-the-Power-BI-Service-Parameters-to-change-conn...

[MNollette]

Yeah, the static parameters make it a no-go. The access level needs to be dynamically adjusted based on user - that's why I'm inquiring on the RLS front. I know you can create rules with associated users with RLS - my question is if it's possible to do so based on query results. Everything I see in any tutorial or topic about RLS talks about adjusting the visibility of specific columns or visualizations. I can't find anything on how to make a row invisible based on its contents.