Row-Level Security in SQL Server 2016

Data is the biggest concern for any organisation to make a significant growth in their business. SQL Server 2016 delivers breakthrough mission-critical capabilities in scalability, performance, and availability for our most important OLTP and data warehousing workloads. In SQL Server 2016, there are many security features like Always Encrypted, Dynamic Data Masking, Enhancement of Transparent Data Encryption and Row-Level Security also added.

Why Row-Level Security (RLS)?

Before introduction of Row-Level Security, customer come-up with different solutions to implement Row-Level Security. For that, one hand they were using SQL views and other hand we have custom application code but both solutions contain problems because-

SQL views are decentralised where we need to create view on the top of each data table that we want to protect and spreading our access logic all over the place and also susceptible to runtime errors. It has become difficult to maintain during app upgrades also.

In Custom application code also became difficult to maintain as code base grows and access logic is separated from the data because multiple applications are accessing and referencing the same database and we need to re-implement the access logic. 

What is Row-Level Security (RLS)?

Microsoft development team has been widely focused on Built-in Raw-Level Security (RLS) programmability feature in SQL Server 2016 that centralises our row access logic within the database. Raw-Level Security enables customers to fine-grained access control to rows in a database table based on the characteristics of the user executing a query. 

RLS offers –

Fine-Grained Access Control – RLS controls both read and write access to specific rows of data in a shared database and provide the flexibility access criteria over the user identity role/ group memberships, connection data and time of day etc.

Application Transparency – RLS works transparently at query time where no application level changed required means it’s helpful to reduce application maintenance and complexity also.

Centralized Security Logic – The best feature of RLS is that it supports centralized security logic means access logic is centralized within the database and helping us to maintain a consistent data access policy and reduce the risk of accidental data leakages.

How RLS works?

RLS is the form called Predicate-based access control. It works automatically applies a security predicate to all queries on a table and this predicate determines which users can access which rows. For an example, In sales data table, a very simple predicate could be where SalesRepName = Current_User and more complicated predicates could include JOINs to look up info in other tables as per our business logic.

RLS supports two types of security predicates.

Filter predicates- They silently filter the rows available to read operations (Select, Update, and Delete).

Block predicates- They explicitly block write operations (After Insert, After Update, Before Update, Before Delete) that violate the predicate.

How to implement RLS

Usually, each row of our table will have label(s) that determine which user can access a particular raw. We can use sales table for an example where SalesRepName is the column which could be used for RLS. In this case, we should create an inline table-valued function that defines user access criteria and this function know as predicate function. After creating the predicate function, we should create a security policy that adds security predicates on tables by using predicates function.

We will learn more on RLS in our next post.... How Row-Level Security works in SQL Server 2016?

Row-Level Security Limitations in SQL Server 2016