SQL Server 2016: Row Level Security

Author by Concurrency Blog

A problem faced when working with databases is that of having many users, but you don’t want all of them to see all of the data in a table (or tables). Perhaps you have:

  • Sales people, who should only be able to view customers in their sales territory
  • A multi-tenant application in which all clients share data in a table
  • A medical practice in which doctors should only see records for their patients

Up until now, we’ve handled this with application-level code or SQL Server views. Now, with SQL Server 2016, Row Level Security (RLS) has been introduced.

Row Level Security

RLS works on both reads (SELECT) – this is a filter predicate - and writes (INSERT, UPDATE, DELETE) – this is a block predicate. The predicate is then applied to a table, based on a column in the table.

It can apply to users who are authenticated at the SQL Server level or the application level. This is especially helpful, as I’ve worked with very few applications in which every user had an associated user in the database.

To set up RLS, a function is created that handles the security. The function will take an argument – like a user ID or a department ID. Then, a security policy is added to the table which uses the function to determine which rows, if any, a user can see.

An excellent demo is provided with the AdventureWorks databases and scripts for 2016. Download and extract the samples, and view the RLS sample. Examples are provided for both database- and application-authenticated users.

Microsoft Channel 9 also has a video on the topic: https://channel9.msdn.com/events/DataDriven/SQLServer2016/Row-Level-Security.

Thoughts

Setting this up does require a thorough understanding of how users authenticate to the database, and what data each user or group should see. Developers and administrators will need to collaborate closely to ensure it’s set up properly. However, it’s a great step forward to ensuring the right users see the right data!

Author

Concurrency Blog

The latest about Concurrency