Forum Discussion
Is there a way to use row-level security to hide SQL Server query results based on returned data?
So bit of an explanation here, with the tl;dr of the question at the bottom.
My organization has a web application that stores and tracks records for users. We've been using SSRS for our reports so far, which are integrated into our application. One of the security modules we have in place is user-based. Each user, and each record, has an access level. If the user's access level is lower than the record's, they can't see it. We have a similar setting for restricting user access by their organization relative to the record's. When pulling an SSRS report, our users are able to pass parameters through our application interface to the report, and we also automatically pass the user's organization profile and access level to ensure they are only retrieving records they're authorized to view.
My higher-ups are quite insistent on migrating everything over to Power BI. Problem is, I've found no way to automatically pass parameters to the report on generation. There is no way to pass that required access level to the report prior to retrieving the data. My understanding is that Power BI Embedded may have a feature like this, but since it utilizes an hourly, metered connection for its model, my employers are not willing to entertain that as an option.
So I'm looking into row-level security instead. Is there a way to utilize RLS to restrict what results a user can view, based on one of the column values that is being returned? And assuming that's possible, is it possible to do this securely? My understanding is that we'd have to have our clients' administrators set up the user roles and assign them in Power BI. Is there a way to prevent users from simply changing their own roles, and viewing records they don't have access to?
5 Replies
- mhossain
Solution Sage
I would suggest go through this blog https://radacad.com/what-do-you-need-to-implement-dynamic-row-level-security-in-power-bi
Only workspace admin/member can change/update/modify the role, so viewer can not change their roles, they won't see it. As far as I understood your question, it's completely doable. Let me know if this helps.
- MNolletteRegular Visitor
I know *how* to implement row-level security in general, I just don't know if what I described is possible - and if so, how to do it. Good to know on the security front though, thank you for that!
- mhossain
Solution Sage
Thanks.
On your questions "Is there a way to use RLS to restrict the results that a user can see, based on one of the column values that are returned?"
This is definately doable.