Thursday , August 18 2022

SQL Server Row Level Security

In today’s article, we will examine SQL Server Row Level Security.

Security is of great importance in the IT world. It is vital when it comes to database security. Because the database contains all the data and information of the organization in which it is located.

Think about it, you are working in an e-commerce company and there is a lot of information in the database such as customer information, orders, payments.

Monitoring and ensuring the security of this information is one of the most important duties of the DBA – the Database Administrator. In this article, I will be talking about the security and encryption solutions that we have gained with SQL Server 2016 and that are included in the next versions.

Remember, you will also need the features here at some point on the KVKK side. If you want to apply the examples in the article yourself, you must have at least SQL Server 2017 installed on your system.

You can choose the Developer edition as the free version. SQL Server 2019 Developer edition is installed on my system.

What is SQL Server Row Level Security?

Row-level security is a long-requested feature in SQL Server, and Microsoft introduced it with SQL Server 2016. You can use this feature in SQL Server 2016 and all later versions (Express, Standard and Enterprise).

Row-Level Security (RLS) meets many business needs for many customers. So we can generate many scenarios about row-level security.

First, it is an important need when all your customers are in the same table and Salespeople can report according to this table without seeing the data from other customers.

To give a different example, consider a nurse inside a hospital. It may be necessary to ensure that this nurse can only see the data of her own patients and not the data on different nurses.

You are probably producing scenarios by thinking about how to apply this to your own applications as I give examples. That’s why row-level security – Row Level Security – allows you to configure tables.

Thus, users can only see the rows that have been granted access. Under normal circumstances, to perform this operation, you would have to apply a limitation at the application level with the where condition to the person performing the operation.

However, when performing Insert, Update and Delete operations, you need to produce different business processes to ensure that it only operates within its own limitations. That’s where Row Level Security – RLS – that comes with SQL Server 2016 is just right for you.

Footnote for SQL Server Row Level Security: This feature will provide you with session-based security.

Let’s explain how we use this feature about a few of the examples we mentioned above.

Example 1: You are a store employee and you want to see how many sales you make at the end of the day.

For this article, I will use the database named “DMC_SQLSecurity” that we created and give an example. Don’t forget to change it while you implement it on your side.

To use it in the example, I need to create 1 Sales Manager and 2 Sales Representatives.

I created the users I will need. Now I need a Sales table next. Let’s create it with the code below.

After creating the sales table, let’s add a record with the following code.

Let’s see the records are added.

Let’s give “Select” authorization to our users that we created so that they can see the data on the Sales table.

 

When we made a query on the sales table, we saw that both of our sales representatives received the data.

We will now use the SQL Server Row Level Security – RLS – feature to ensure that sales reps can only see their sales. For this, we need an Inline Table Value Function that returns a True value.

After this process, we need to create a policy that will filter the Inline function we created.

If we examine the function and policy codes we have created; As we can see, our function takes username as parameter and returns True and NULL values.

In our POLICY section, we specify that our function called fn_RLS, which we created in the PRADICATE section, will be used to automatically filter when the query is run on our table and on which table it will perform the operation.

Now, after these definitions, let’s query our Sales table.

In fact, if you want to query the Sales table with the user you connect and perform the transactions with, you will not see a record.

So is the Sales table empty? Of course, the result of the select query returns when a user suitable for the Seller information comes in.

If you immediately think of how to implement this feature in versions before SQL Server 2016, let’s explain the answer with the example below.

How to Implement Row Level Security before SQL Server 2016?

We have made an example about the use of Row Level Security above, now let’s explain how we can use it before SQL Server 2016 through the same example topic. Let our Sales Representatives and Sales Managers be the same.

Before we start working for the example, let’s drop the Satis table we created in the RLS example. Before we drop, we need to turn off the policy we created.

We’ve done the drop, now we can start working again for our example. For a similar example, let’s create the Sales table and add sample records into it.

Let’s query the sales table.

We need a user mapping table as we cannot use the RLS feature.

Let’s add a record to the mapping table.

Now let’s create a view where we will match the Sales table and the Mapping table and report the sales.

We give our users that we defined before, to be able to select a view named v_satis.

After defining the authorization, let’s query both the Satis table and the view named v_satis with the user SatisRepresentative1.

As we can see in the picture above, our user does not have permission to see the Sales table. Now let’s query for v_satis.

Let’s query v_satis with our Sales Representative2 and SalesManagement users.

 

As can be seen in Picture-7 and Picture-8, the query result was empty, the reason is the mapping definition we made in the view. If you change the view for the Sales Manager, the sales representative1 can see the transactions.

We are questioning again with the Sales Manager.

 

 

About Çağlar Özenç

Leave a Reply

Your email address will not be published. Required fields are marked *