Forum Discussion
Benchmark with security roles
For everyone reading this in future. I ended up creating separated views that suits the requirements for each users, adding a security column and combing all tables. Therefore, of course, without any role applied the data is duplicated.
Another benefit of this is, that you are much more flexible with this approach. Like, if one user does not want some datapoint to be shared with the others (because it would be obvious to guess who is behind the datapoint), it then can be displayed only for him, but not for the others.
If anyone is interested, I can share more details on my approach.
best
Early disclaimer: Security by Obscurity Does Not Work.
With that out of the way - one approach is to create a hidden shadow table with anonymized data that is not covered by RLS. I guess you can see where this is going.
Possible? Yes. Recommended? No.
Well, if I understood you correctly that is what I also thought of. Duplicating the table and hiding all information and no relationship between those two tables.
But the problem is, that I also do have to duplicate all filters and need to set the scale to a fixed value.
Since users only got read access on this report I thought maybe if I can somehow extract the applied role I can write a DAX which masks all other info.
If there is no DAX to extract the role, I thought of getting the role membership from AD and then extract the username with DAX.
Another solution I thought of is creating a view where i combine several tables and each table suits the security requirements of each users.
For everyone reading this in future. I ended up creating separated views that suits the requirements for each users, adding a security column and combing all tables. Therefore, of course, without any role applied the data is duplicated.
Another benefit of this is, that you are much more flexible with this approach. Like, if one user does not want some datapoint to be shared with the others (because it would be obvious to guess who is behind the datapoint), it then can be displayed only for him, but not for the others.
If anyone is interested, I can share more details on my approach.
best