Row-level Security Solutions for MSSQL

In this tutorial, you will learn how to implement data access control solutions using the new Row Level Security feature introduced by SQL Server 2016.

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.

Create a RLS filter function: It is used to implement the access control logic of "which users can view which data".
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:

-- Create Test Database
IF DB_ID('Test') IS NULL
    CREATE DATABASE Test;
GO

USE Test
GO

--create three logins(CEO, manager, employee)

--create login CEO
IF EXISTS(
            SELECT *
            FROM sys.syslogins
            WHERE name = 'CEO')
BEGIN
      DROP LOGIN CEO;
END
GO
CREATE LOGIN CEO with password='CEODbo',check_policy = off;
GO

--create user CEO
IF USER_ID('CEO') is not null
        DROP USER CEO;
GO
CREATE USER CEO FOR LOGIN CEO;
GO

--create login Manager
IF EXISTS(
            SELECT *
            FROM sys.syslogins
            WHERE name = 'Manager')
BEGIN
      DROP LOGIN Manager;
END
GO
CREATE LOGIN Manager with password='ManagerDbo',check_policy = off;
GO

--create user manager
IF USER_ID('Manager') is not null
    DROP USER Manager;
GO
CREATE USER Manager FOR LOGIN Manager;
GO
       
--create login employee
IF EXISTS(
            SELECT *
            FROM sys.syslogins
            WHERE name = 'employee')
BEGIN
    DROP LOGIN employee;
END
GO
CREATE LOGIN employee with password='employeeDbo',check_policy = off;
GO

--create user employee
IF USER_ID('employee') is not null
        DROP USER employee
GO
CREATE USER employee FOR LOGIN employee;
GO

--create basic TABLE
IF OBJECT_ID('dbo.tb_Test_ViewPermission','u')is not null
    DROP TABLE dbo.tb_Test_ViewPermission
;
GO
CREATE TABLE dbo.tb_Test_ViewPermission
(
        id int identity(1,1) not null primary key
        ,name varchar(20) not null
        ,level_no int not null
        ,title varchar(20) null
        ,viewByCEO char(1) not null
        ,viewByManager char(1) not null
        ,viewByEmployee char(1) not null
        ,salary decimal(9,2) not null
);

--data init.
INSERT INTO dbo.tb_Test_ViewPermission
SELECT 'AA',0,'CEO','Y','N','N',1000000.0
union all
SELECT 'BB',1,'Manager','Y','Y','N',100000.0
union all
SELECT 'CC',2,'employee','Y','Y','Y',10000.0
;
GO

select * from dbo.tb_Test_ViewPermission

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

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:

Create an RLS filter function
Create a table-level security policy
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:

USE Test
GO
CREATE SCHEMA RLSFilterDemo;  
GO

-- Create filter title function
CREATE FUNCTION RLSFilterDemo.fn_getTitle(@title AS varchar(20))  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
RETURN
    SELECT 1 AS result   
    WHERE USER_NAME() IN (
    SELECT A.title 
    FROM dbo.tb_Test_ViewPermission AS A
        INNER JOIN  dbo.tb_Test_ViewPermission AS B
        ON a.level_no <= B.level_no
    WHERE B.title = @title)
GO

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:

USE Test
GO
-- create security policy base on the filter function
CREATE SECURITY POLICY TitleFilter  
ADD FILTER PREDICATE RLSFilterDemo.fn_getTitle(title)   
ON dbo.tb_Test_ViewPermission  
WITH (STATE = ON);
GO

Verify the Query Access Control

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

USE Test
GO
-- grant permissions to three users.
GRANT SELECT ON dbo.tb_Test_ViewPermission TO CEO;  
GRANT SELECT ON dbo.tb_Test_ViewPermission TO Manager;  
GRANT SELECT ON dbo.tb_Test_ViewPermission TO employee;  

USE Test
GO

--CEO can read all of the data
EXECUTE AS USER='CEO'
SELECT WhoAmI = USER_NAME()
SELECT * FROM dbo.tb_Test_ViewPermission
REVERT;
GO

USE Test
GO
--Manager can read manager and employee's data, but except CEO's.
EXECUTE AS USER='Manager'
SELECT WhoAmI = USER_NAME()
SELECT * FROM dbo.tb_Test_ViewPermission
REVERT;
GO

USE Test
GO
--employee just can read employee's data, couldn't query CEO and Manger's.
EXECUTE AS USER='employee'
SELECT WhoAmI = USER_NAME()
SELECT * FROM dbo.tb_Test_ViewPermission
REVERT;
GO

The result is shown in the figure below:

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:

-- First, take the security policy off.
ALTER SECURITY POLICY TitleFilter
WITH (STATE = OFF);
-- Try to perform the DML action to see the DML permission control
USE Test
GO
-- grant permissions to all users.
GRANT UPDATE, INSERT, DELETE ON dbo.tb_Test_ViewPermission TO CEO;
GRANT UPDATE, INSERT, DELETE ON dbo.tb_Test_ViewPermission TO Manager;
GRANT UPDATE, INSERT, DELETE ON dbo.tb_Test_ViewPermission TO employee;


USE Test
GO
--try to test INSERT by user employee
EXECUTE AS USER='employee'
SELECT WhoAmI = USER_NAME()

INSERT INTO dbo.tb_Test_ViewPermission
SELECT 'DD',2,'employee','Y','Y','Y',100.0;
SELECT * FROM dbo.tb_Test_ViewPermission;

UPDATE TOP(1) dbo.tb_Test_ViewPermission
SET name = 'EE'
WHERE name = 'DD';
SELECT * FROM dbo.tb_Test_ViewPermission;

DELETE TOP (1)
FROM dbo.tb_Test_ViewPermission
WHERE name = 'EE'
;
SELECT * FROM dbo.tb_Test_ViewPermission;

REVERT
GO
;

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:

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":

-- Here we go to show how to allow manager and restrict employee dml operation
USE Test
GO

CREATE SCHEMA RLSBlockDemo;
GO

CREATE FUNCTION RLSBlockDemo.fn_getTitle(@title AS varchar(20))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN 
    SELECT 1 AS result
    WHERE USER_NAME() IN (
    SELECT A.title 
    FROM dbo.tb_Test_ViewPermission AS A
        INNER JOIN  dbo.tb_Test_ViewPermission AS B
        ON a.level_no <= B.level_no
    WHERE B.title = @title)
GO

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:

USE Test
GO
ALTER SECURITY POLICY TitleFilter
ADD BLOCK PREDICATE RLSBlockDemo.fn_getTitle(title)
ON dbo.tb_Test_ViewPermission AFTER INSERT;

ALTER SECURITY POLICY TitleFilter
WITH (STATE = ON);

Verify Operation Access Control

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

USE Test
GO
--try to test INSERT by user Manager
EXECUTE AS USER='Manager'
SELECT WhoAmI = USER_NAME()

INSERT INTO dbo.tb_Test_ViewPermission
SELECT 'DD',1,'Manager','Y','Y','Y',100.0;
SELECT * FROM dbo.tb_Test_ViewPermission;

UPDATE TOP(1) dbo.tb_Test_ViewPermission
SET name = 'EE'
WHERE name = 'DD';
SELECT * FROM dbo.tb_Test_ViewPermission;

DELETE TOP (1)
FROM dbo.tb_Test_ViewPermission
WHERE name = 'EE'
;
SELECT * FROM dbo.tb_Test_ViewPermission;

REVERT
GO

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:

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):

USE Test
GO
--It's OK  to INSERT manger record by user Manager
EXECUTE AS USER='Manager'
SELECT WhoAmI = USER_NAME()

INSERT INTO dbo.tb_Test_ViewPermission
SELECT 'EE',2,'employee','Y','Y','N',100.0;
SELECT * FROM dbo.tb_Test_ViewPermission;
REVERT
GO
;

The Manager user inserts EE, for an Employee user:

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:

USE Test
GO
--Failed to INSERT CEO record by user Manager
EXECUTE AS USER='Manager'
SELECT WhoAmI = USER_NAME()

INSERT INTO dbo.tb_Test_ViewPermission
SELECT 'DD',0,'CEO','Y','Y','Y',100.0;

REVERT
GO
;

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

(1 row affected)
Msg 33504, Level 16, State 1, Line 286
The attempted operation failed because the target object 'Test.dbo.tb_Test_ViewPermission' has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.
The statement has been terminated.

The error figure is as follows:

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.

Community

Row-level Security Solutions for MSSQL

Background Principle

Procedure

Preparing the Test Environment

Implementing Data Query Access Control

Create an RLS Filter Function

Create a Table-level Security Policy

Verify the Query Access Control

Implementing Data Operation Access Control

Create an RLS Filter Function

Create a Table-level Security Policy

Verify Operation Access Control

Summary

Read previous post:

Cherish Wang

You may also like

Comments

Cherish Wang

Related Products

Database Overview

ApsaraDB RDS for SQL Server

Database Security Solutions

Web Hosting Solution