Earlier this year Microsoft Azure implemented Row-Level Security (Brent’s announcement). In following Microsoft’s cloud-first release concept, this great feature is now part of SQL Server 2016 CTP2, and expected to be included in the RTM version.
When I first saw the feature and decided to blog on it, my mind jumped to the performance cost that might come from implementing this feature. Then I did a bad thing, I read other bloggers’ posts. I don’t want to duplicate what is already out there. To look at the performance impact of this feature check out Tommy Mullaney‘s post Row Level Security: Performance and common patterns. Take note that, in Tommy’s post, he presents some of the better patterns. There are many anti-patterns out there which can cause performance to tank very quickly. Be cautious and make sure to load test before implementing this feature.
With the ominous performance warning out of the way, what makes RLS so great?
RLS is designed to take existing T-SQL patterns and make them transparent. If 100% of your data access comes through stored procedures or application code your data might be fairly safe. But, if users have direct access to query SQL Server it is a hassle to make sure that they are only viewing the rows which are relevant to them. One example of this is if you have power users that regularly connect to SQL Server via Excel. You can make them use stored procedures only but there are limitations to that and you will need to support those stored procedures for people performing self-service BI tasks.
RLS solves these problems by taking your custom filtering, in the form of a user defined table-valued function, and implementing it on the object for everyone. Now that power user can run SELECT * FROM myTable without a WHERE clause and only view the data that they have access to.
Leave a Reply