Row-Level Security (RLS) is one of the most often asked for features that I hear from customers. Done correctly, Row-Level Security ensures data segregation for users in a single database. This ensures each user can only view or manipulate data that they can access. Now you can share a database in a multi-tenant environment without the need to physically carve up the database or create a separate DB for every customer. Without Row-Level Security, the options are grossly inefficient, expensive and take a long time to implement. However, these were the only available solutions for many years.
Row-Level Security Prior to SQL 2016
While it was possible to implement security at the row level in the past using SQL Server, it needed a high number of man hours to develop, test and deploy. So, most of the time was spent programming the filtering logic into the application. This was needed to tie specific rows of data to a specific user or group. Therefore, the logic had to be embedded in stored procedures, views and triggers. Unfortunately, this was required in order to lock down the data at the row level for a given user or group. Then the resulting solution was complicated and riddled with lots of code sprinkled throughout the database, making the solution complicated. Risk of a coding error is increased, which could accidentally allow a user to see data they should not.
Row-Level Security in SQL Server 2016
In SQL Server 2016, Microsoft implemented native RLS by introducing centralized security policies that can act as a logic layer for filtering the data for applications. Best of all, it is available in Standard Edition. Therefore, RLS is a promising feature at a lower cost. However, Row-Level Security does come with caveats and best practice recommendations. If you support a multi tenant application which uses a copy of the same database for each of your customers, departments, etc., Row-Level Security is an ideal solution. No more struggles to keep many duplicates of the database in schema-sync with the application!
Should I Implement Row-Level Security Immediately?
After you understand what RLS can and cannot do and decide it is worth checking out, the next step is to design a proof-of-concept. You will then get a clear idea of what to expect in a production application. A proof-of-concept will also help you answer all your questions up front before you begin a production project using Row-Level Security. Questions like: Will RLS impact my application performance? How difficult is RLS to support? How would offloaded reporting work? Can I implement RLS at a user, group and customer level? What if my application uses a shared user account? There is a lot to consider, but with Row-Level Security as a Standard Edition offering, there are far fewer application design changes to worry about. You may find that the time and cost of implementing stronger security is now well within your IT budget.
Are you considering a project that uses Row-Level Security and need help? Contact SQLWatchmen.
