SQL Server 2016 - Row-Level Security

In SQL Server 2016, many new security features have been introduced like ‘Row Level Security’, ‘Always Encrypted’, ‘Dynamic Data Masking’, and ‘Enhancement of Transparent Data Encryption’ that will help users protect their data.
In today's post, I will cover Row-Level Security released to on-premise SQL Server instances in June 2016. This feature simplifies the design and coding of security in the application. For example, it ensures that employees can access only those data rows, that refer to their department, or restricts customer’s data access to only the data, which is relevant to their company.

The key significance about RLS is that the access restriction logic is located in the database tier rather than away from the data in another application tier. The database system applies the access restrictions every time that data access is attempted from any tier. This makes our security system more reliable and robust by reducing the surface area of your security system.

In Oracle 11g version, a similar feature is available through the Virtual Private Database (VPD) technology. It is one of the most popular security features in the database and is now available in SQL Server as well.

From BOL: Row level security enables customers to control access to rows in a database based on the characteristics of the user executing a query.

To implement the RLS we need to consider three main parts:

• Predicate Function - Defines logic based on which user will get access
• Security Predicates - Binds predicate function to table 
• Filter Predicate - Filters the data without raising error for any DML operations
• Block Predicate - Raises error and block users if DML operation violates predicate function logic
• Security Policy - groups all predicated that are referenced in predicate function logic

Suppose we have a table which contains data for employees and their salary, and we want to restrict this data for some employees based on Employee ID and department

Table sample

Create Users 

Create Function Predicate










Create Security Policy

Retrieve data


If we execute our select as Aero user we will retrieve Limitation and Restrictions in Row Level Security.

Update Data

Block Predicates: Defines rules that identify which rows a DB user is not allowed to perform Insert, Update or Delete operations

Use Cases

RLS can be used in below scenarios:

1. A hospital can create a security policy that allows nurses to view data rows for their own patients only.
2. A bank can create a policy to restrict access to rows of financial data based on the employee's business division, or based on the employee's role within the company.
3. A multi-tenant application can create a policy to enforce a logical separation of each tenant's data rows from every other tenant's rows. Efficiencies are achieved by the storage of data for many tenants in a single table. Of course, each tenant can see only its data rows.

RLS filter predicates are functionally equivalent to appending a WHERE clause. The predicate can be as sophisticated as business practices dictate, or the clause can be as simple as WHERE TenantId = 42.

In more formal terms, RLS introduces predicate based access control. It features a flexible, centralized, predicate-based evaluation that can take into consideration metadata or any other criteria the administrator determines as appropriate. The predicate is used as a criterion to determine whether or not the user has the appropriate access to the data based on user attributes. Label-based access control can be implemented by using predicate-based access control.