SQL Server Row-Level Security (RLS)

In this post, I will explain how to setup and use SQL Server Row-Level Security (RLS). RLS is available from SQL Server 2016 and also on Azure SQL database. RLS is used 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.

There are two types of security predicates supported by RLS:

  • Filter predicates which filter rows from a table
  • Block predicates which blocks write operations.

See below a couple of best practice when you are working with RLS:

  • Create a separate schema for your RLS objects;
  • Try to minimize the 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 46 rows:

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.

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security

Thanks,

Sergio

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: