SQL Security Tips - Implement Row Level Security Quickly

Introduction

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. 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.
2. Suppose you have a hospital database in which you have login users of different doctors & nurses. Now, your challenge is to show data to doctors or nurses to their relevant patients to whom they are giving treatment, not any other patient data should be available.

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

Now, I know you were thinking that all the above problems can easily be resolved on the code side 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 application like web, mobile, console, and 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 3rd party came which want to integrate your data or access database directly then in such cases it is tuff to apply the same logic.

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

As the name suggested, Security was 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 access within the database
• Easy to implement & maintain

How does RLS work?

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

There are 2 predicates that 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 doctors from seeing other doctors’ patient data then in such case, you can apply filter predicate.

2) Block Predicate

This predicate helps implement the policy by which inserting, updating, and deleting rows will prevent violating the filter predicate. In other words, we can say it explicitly blocks the write operation.

For example, you have a multi-tenant application, and you want to restrict one tenant user to insert or update another tenant’s data. Or suppose you have a sales representative who belongs to a specific region, so they can not insert, update or delete other regions’ 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 creating RLS

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

I am creating a new patient table in this demo with 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, which Nurse2 in which they are the in-charge, and a doctor user can see the entire table’s data.

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

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

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

Now, create a function that will have security logic. The Logic is very simple if the user is a 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, run the select query with the default user “dbo.” 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 that are relevant to Nurse1 visible.

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

Now, running the same statement with the Doctor user, as per our expectation, 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 Don’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.

Please, share your thought on RLS.

Thanks!