SQL Server Row-Level Security (RLS)

In this post, I will explain how to setup and use Row-Level Security (RLS) which is available from SQL Server 2016 and on Azure SQL database. RLS enables you to control access to rows (records) in your database table, restricting employees to ONLY access rows related to their department, region or any other criteria required by your business.

There are two types of security predicates supported by RLS, the first type is called Filter predicates which filter rows from a table and the second type is called Block predicates which blocks write operations.

There are a couple of best practice when you are working with RLS and I have listed some below:

• Create a separate schema for your RLS objects;
• Try to minimize number of joins, as this will help performance;
• Avoid data conversion, cast and recursion;
• Restrict user access to ALTER ANY SECURITY POLICY permission;

Sample data

In this example, I will create 3 users and one table with 10 records as below:

CREATE USER HRManager WITHOUT LOGIN;
CREATE USER MarketingManager WITHOUT LOGIN;
CREATE USER FinanceManager WITHOUT LOGIN;

CREATE TABLE EmployeePosition
    (
    EmployeeID int,
    EmployeeManager sysname,
    Position varchar(30),
    Salary decimal(15,3)
    );

INSERT EmployeePosition VALUES
(1, 'MarketingManager', 'Manager', 130000),
(2, 'MarketingManager', 'Coordinator', 100000),
(3, 'MarketingManager', 'Senior Analyst', 90000),
(4, 'MarketingManager', 'Analyst', 80000),
(5, 'MarketingManager', 'Analyst', 78000),
(6, 'MarketingManager', 'Graduated', 38000),
(7, 'FinanceManager', 'CFO', 180000),
(8, 'FinanceManager', 'Senior Accountant', 12000),
(9, 'FinanceManager', 'Accountant', 75000),
(10, 'FinanceManager', 'Graduated', 40000);

Give SELECT access to all users to EmployeePosition table.

GRANT SELECT ON EmployeePosition TO HRManager;
GRANT SELECT ON EmployeePosition TO MarketingManager;
GRANT SELECT ON EmployeePosition TO FinanceManager;

SELECT * FROM EmployeePosition;

EmployeeID	EmployeeManager	Position	Salary
1	MarketingManager	Manager	$130,000.00
2	MarketingManager	Coordinator	$100,000.00
3	MarketingManager	Senior Analyst	$90,000.00
4	MarketingManager	Analyst	$80,000.00
5	MarketingManager	Analyst	$78,000.00
6	MarketingManager	Graduated	$38,000.00
7	FinanceManager	CFO	$180,000.00
8	FinanceManager	Senior Accountant	$12,000.00
9	FinanceManager	Accountant	$75,000.00
10	FinanceManager	Graduated	$40,000.00

Filter Predicates

Filter predicates filter rows when you use SELECT, UPDATE and DELETE.

Create schema SEC as per best practice:

CREATE SCHEMA SEC;
GO

Create a table value function, that returns 1 when EmployeeManager is the same as the user running the query or the User running the query is an HRManager user.

CREATE FUNCTION SEC.fn_securitypredicate(@employeeManager AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS

RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @employeeManager = USER_NAME() OR USER_NAME() = 'HRManager';

Create a Security Policy to add the function as a Filter Predicate.

CREATE SECURITY POLICY EmployeePositionFilter
       ADD FILTER PREDICATE SEC.fn_securitypredicate(EmployeeManager)
                   ON dbo.EmployeePosition
WITH (STATE = ON);

Run a Select as an HR Manager user and return all 10 rows:

EXECUTE AS USER = 'HRManager';
SELECT * FROM EmployeePosition;
REVERT;

EmployeeID	EmployeeManager	Position	Salary
1	MarketingManager	Manager	$130,000.00
2	MarketingManager	Coordinator	$100,000.00
3	MarketingManager	Senior Analyst	$90,000.00
4	MarketingManager	Analyst	$80,000.00
5	MarketingManager	Analyst	$78,000.00
6	MarketingManager	Graduated	$38,000.00
7	FinanceManager	CFO	$180,000.00
8	FinanceManager	Senior Accountant	$12,000.00
9	FinanceManager	Accountant	$75,000.00
10	FinanceManager	Graduated	$40,000.00

Run a Select as a Finance Manager user and return 4 rows:

EXECUTE AS USER = 'FinanceManager';
SELECT * FROM EmployeePosition;
REVERT;

EmployeeID	EmployeeManager	Position	Salary
7	FinanceManager	CFO	$180,000.00
8	FinanceManager	Senior Accountant	$12,000.00
9	FinanceManager	Accountant	$75,000.00
10	FinanceManager	Graduated	$40,000.00

Run a Select as a Marketing Manager user and return 46rows:

EXECUTE AS USER = 'MarketingManager';
SELECT * FROM EmployeePosition;
REVERT;

EmployeeID	EmployeeManager	Position	Salary
1	MarketingManager	Manager	$130,000.00
2	MarketingManager	Coordinator	$100,000.00
3	MarketingManager	Senior Analyst	$90,000.00
4	MarketingManager	Analyst	$80,000.00
5	MarketingManager	Analyst	$78,000.00
6	MarketingManager	Graduated	$38,000.00

Block Predicates

Block predicates block write operations on AFTER INSERT, AFTER UPDATE, BEFORE UPDATE and BEFORE DELETE.

Give SELECT, UPDATE and INSERT access to all users to EmployeePosition table.

GRANT SELECT, UPDATE, INSERT ON EmployeePosition TO HRManager;
GRANT SELECT, UPDATE, INSERT ON EmployeePosition TO FinanceManager;
GRANT SELECT, UPDATE, INSERT ON EmployeePosition TO MarketingManager;

DENY update on EmployeeManager column.

DENY UPDATE ON EmployeePosition(EmployeeManager) TO MarketingManager;
DENY UPDATE ON EmployeePosition(EmployeeManager) TO FinanceManager;

Alter Security Policy adding a Block Predicate on AFTER INSERT, this is will not let Marketing Manager user to insert a new Employee with FinanceManager as EmployeeManager.

ALTER SECURITY POLICY EmployeePositionFilter
    ADD BLOCK PREDICATE SEC.fn_securitypredicate(EmployeeManager)
        ON dbo.EmployeePosition AFTER INSERT;

With the Block Predicate, MarketingManager user is only able to insert a new record to EmployeePosition table when the EmployeeManager is MarketingManager, otherwise will get the error below.

EXECUTE AS USER = 'MarketingManager';
INSERT EmployeePosition VALUES
(11, 'FinanceManager', 'Business Analyst', 130000);
REVERT;

Msg 33504, Level 16, State 1, Line 77

The attempted operation failed because the target object ‘Sales.dbo.EmployeePosition’ 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.

Thanks,

Sergio