Row-level Security Solutions for MSSQL

By Feng Yi.

Consider this. If different users access the same table, how can different users only access data belonging to themselves and the lower levels? For example, a company has three roles: CEO, Manager, and Employee. The CEO user can view data belonging to the CEO, Managers, and Employees. Managers can only view the data pertaining to Managers and Employees; they cannot view the data belonging to the CEO. Last, Employees can only view the data pertaining to Employees and cannot view the data belonging to the CEO and Managers.

Today, in this blog, we take the scenario further, requiring users to operate a data manipulation language (DML) on only their own and subordinate data, rather than data at different levels.

Background Principle

Microsoft’s SQL Server 2016 introduced a new feature, which is Row Level Security (RLS). This feature does not encrypt or decrypt table data in the database. Instead, it restricts and filters row-level data in the table based on user-defined security policies so that the database engine does not expose too many data rows, thus implementing a simple access control means, which is completely transparent to user applications and clients. Therefore, the row-level data security access control of the table can be easily implemented in two simple steps, without any code modification by the user.

  1. Create a RLS filter function: It is used to implement the access control logic of “which users can view which data”.
  2. Create a table-level security policy: It is used to implement row-level security access control for data in the table.

Procedure

According to the requirements we mentioned above, we’ll need to implement data query access control and data operation access control. Below are the specific steps to implement all of this.

Preparing the Test Environment

We still use test scenario data to build the test environment as follows:

Without permission control, users with CEO, Manager or Employee roles can all view all of the data, as follows:

Image for post
Image for post

In this way, row-level data security of the tb_Test_ViewPermission table cannot be implemented, nor can the requirements for row-level query and operation access control of data be met.

Implementing Data Query Access Control

Let’s take a look at the three steps to implement row-level security access control for data query:

  1. Create an RLS filter function
  2. Create a table-level security policy
  3. Verify the query access control

Create an RLS Filter Function

First, create an RLS filter function to implement the “which users can view which data” access control logic. The implementation code is as follows:

The above code implementation means that level_no is used to control the level of data accessed by users. The smaller the level_no value, the higher the level and the greater the permissions. That is, if level_no is 0 (corresponding to the CEO user), data with level_no of 0, 1 (corresponding to the Manager user) and 2 (corresponding to the common Employee user) can be viewed. If level_no is 1, data with level_no of 1 and 2 can be viewed. If level_no is 2, only data with level_no of 2 can be viewed. When the user executing the query is found to match the corresponding title, it can be deemed that the user has corresponding permissions, that is, the return value of the function is 1. Otherwise, it is deemed that the user does not have permissions to access the corresponding row.

Create a Table-level Security Policy

Next, we create a table-level security policy based on the previous filter function, and make this security policy take effect. The code is as follows:

Verify the Query Access Control

Finally, we need to query the data, to verify and test the row-level security access control:

The result is shown in the figure below:

Image for post
Image for post

From the figure, CEO users can view all data rows, Manager users can view the data rows of their own and Employee users, while Employee users can only view data rows of their own. It indicates that the row-level query access control has been implemented, and our goal has been achieved.

Implementing Data Operation Access Control

After successfully completing the practice of row-level access control for data query, we can further implement row-level data operation access control. That is, users can only operate on the data of their own and lower levels (the level_no value is greater than or equal to that of their own), but cannot operate on the data above their own levels (the level_no value is smaller than that of their own). Without any access control, any user with permissions can operate on the data in this table, as follows:

After executing the above statements, we find that the Employee user inserts DD, updates it to EE, and then deletes the data. As shown in the following figure:

Image for post
Image for post

It indicates that, without row-level security access control, any user with permissions can operate on all the data in this table, and row-level security cannot be implemented.

Create an RLS Filter Function

Similarly, we first create the RLS filter function to implement the access control logic of “which users can operate on which data”:

Create a Table-level Security Policy

Create a table-level operation control security policy based on the RLS filter function, and enable it to take effect:

Verify Operation Access Control

Next, verify the access control for table-level operations. Manager users can operate on their own data:

The Manager user first inserts DD, then updates it to EE, and finally deletes it. No error is reported throughout the process and the operation is successful, as shown in the following figure:

Image for post
Image for post

Similarly, Manager users can operate on the data of Employee users, that is, the data below their own level (the level_no value is greater than that of their own):

The Manager user inserts EE, for an Employee user:

Image for post
Image for post

However, Manager users cannot operate data above their own level (the level_no value is smaller than that of their own), such as the CEO user data, as shown in the following code:

If the Manager user tries to operate on the CEO user data, the following error will be reported:

The error figure is as follows:

Image for post
Image for post

After completing the data row-level security policy, we can implement the row-level operation access security control for user data, and achieve our established security goals.

Summary

In this tutorial, you have learned how to implement data access control solutions using the new Row Level Security feature introduced by SQL Server 2016. The row-level data query and operation access control for tables can be implemented without any changes to the application by the user, so as to maximize the row-level data security of tables.

Original Source

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store