ROW LEVEL Security SQL SERVER 2016

To understand RLS (ROW LEVEL SECURITY), let’s understand the different problems first.

Problem 1

Suppose, you have a multi-tenant e-commerce website and different companies registered on your website and you have a centralized single database for all the clients. Now as a product owner, it is your responsibility that one tenant’s data should not be available to another tenant. This is a very common problem.

Problem 2

Now, suppose you have hospital database in which you have login user of different doctors & nurses. Now, your challenge is to show data to doctor or nurses of their relevant patient to whom they are giving treatment, any other patient data should not be available .

Here, limiting the user’s access to only certain rows of the data in the database many have various reasons like compliance standards, regulatory need or security reasons.

Now, I know you were thinking that all the above problems can be resolved at code side easily by writing custom logic. I will say here yes you are right but this is not the 100% solution. For example, if you have 4 different applications like web, mobile, console, windows (Excel) and all have their own DAL, then you have to implement this custom logic to every application and suppose tomorrow if any time a new 3^rd party came which wants to integrate your data or access database directly, then in such cases, it is tough to apply the same logic.

So, all the above problem can be easily handled using SQL SERVER 2016’s feature which is ROW Level Security (RLS). Security is one of the key areas which is handled in SQL SERVER 2016 very seriously. As RLS (Row Level Security) is centralized security logic, you don’t need to repeat the same security logic again and again.

As the name suggests, security is implemented at Row Level in SQL SERVER 2016. In the Row Level, security data is accessed according to user roles. It is a centralized data access logic.

RLS has the following properties:

• Fine-grained access role (control both read & write access to specific rows)
• Application transparency (no application changes required)
• Centralized the access within the database
• Easy to implement & maintain

How RLS Works?

RLS is a predicate based function which runs seamlessly every time when a SQL is run on a particular table on which RLS predicate function is implemented.

There are 2 predicates which can be implemented in RLS:

1. Filter Predicate: By the name, it is clear that it will filter the row or we can say exclude the rows which do not satisfy the predicate and stop further options like Select, Update & Delete.
   For example: Suppose, you want to restrict a doctor from seeing other doctor’s patient data, then in such case, you can apply filter predicate.
2. Block Predicate: This predicate helps in implementing policy by which insert, update and delete rows will prevent which violates the filter predicate. In other words, we can say it explicitly blocks write operation.
   For example, you have multi-tenant application and you want to restrict one tenant user to insert or update other tenant’s data. Or suppose, you have sales representative who belongs to a specific region so they cannot insert, update or delete other region’s data.

Demo

I know you will be super excited to see the demo of this feature, so let’s do it right away.

There are 2 basic steps to create RLS:

1. Create inline table function or we can say predicate function and write custom logic to control user access to every row.
2. Create the security policy and apply it.

In this demo, I am creating a new table called Patients which has the following schema:

Here, I have inserted 2 rows for Nurse1 & 2 rows for Nurse2:

The objective is to show only those rows to Nurse1, Nurse2 in which they are in charge and a doctor user can see entire table’s data.

To achieve this, let's first create 3 users in database:

Once the users are created, the next step is to grant permission of select to Nurse1 & Nurse2 user and full permission to doctor user.

Now, before creating function, it is a standard to create a security schema. In our case, we are creating a schema with name sec as shown in the below figure.

Now, create a function which will have security logic. The Logic is very simple if the user is doctor or any in charge name, then return 1 else 0.

Now create a security policy to proceed further:

Till now, we are good to go. Now, let’s test the security policy.

Firstly, running the select query with default user “dbo.” and we have not given permission for this user if you see fn_RLSPredicate, we have not mentioned it so obviously the result would show “0” records.

Now, running the same select statement but executing with “Nurse1” login, you will find 2 records which are relevant to Nurse1 is visible.

Similarly, I am running the same statement for Nurse2 user by running command “Execute as user” so, again I will get 2 records:

Now, running the same statement with Doctor user and as per our expectation, it should show all 4 records.

So, as you can see, we have achieved the goal using RLS (Row Level Security) feature. Now, the next thing which might occur in your mind is how to disable this policy if required, then doesn’t worry it is very simple. Just alter the security policy and make state = off as shown in the below figure.

I hope till now, we are good to work on RLS. In the next couple of posts, we will dig deeper in RLS.

Please share your thoughts for RLS.