Row-Level Security (RLS) is a new feature of SQL Server 2016 and Azure SQL Database that enables data access control based on the users executing those queries: if a user isn’t authorized to access certain rows in a table then those rows are automatically filtered out by the database engine. This feature promises to simplify design and coding of applications, especially in complex multi-tenancy environments, as the access control logic is moved from the application to the database. In short, instead of writing queries like this:

CREATE VIEW vwInventory AS ... (implements security logic);
SELECT * FROM vwInventory WHERE isVisibleTo = 'Paul'

We write simple queries like this:

SELECT * FROM Inventory;

Isn’t that cool?

RLS Example

Suppose that we have a multi-tenancy database with two users, John and Paul, who store their data in the table called Inventory but they are not allowed to see each other’s data. There is also a system administrator called Adam who has permissions to access any data in the database. The following scripts create database objects and populate the table with sample data:

CREATE TABLE Inventory
(
InventoryID int,
TenantID sysname,
Product nvarchar(255),
UnitPrice MONEY,
CONSTRAINT [PK] PRIMARY KEY CLUSTERED(InventoryID)
);
CREATE USER Adam WITHOUT LOGIN;
CREATE USER John WITHOUT LOGIN;
CREATE USER Paul WITHOUT LOGIN;
GRANT SELECT ON dbo.Inventory TO John;
GRANT SELECT ON dbo.Inventory TO Paul;
GRANT SELECT ON dbo.Inventory TO Adam;
INSERT INTO dbo.Inventory VALUES ( 1, 'John', N'Notebook' , 500 )
INSERT INTO dbo.Inventory VALUES ( 2, 'John', N'Tablet' , 300 )
INSERT INTO dbo.Inventory VALUES ( 3, 'John', N'Smartphone' , 700 )
INSERT INTO dbo.Inventory VALUES ( 4, 'Paul', N'Crane' , 55500 )
INSERT INTO dbo.Inventory VALUES ( 5, 'Paul', N'Truck' , 12300 )
INSERT INTO dbo.Inventory VALUES ( 6, 'Paul', N'Trailer' , 3700 )

In order to define the row-level security, we need to create three database objects:

• a schema,
• an inline function that returns 1 or 0 based on the user and business rules and
• a security policy:

CREATE SCHEMA Security;
CREATE FUNCTION Security.udfSecurityCheck(@Tenant AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS udfSecurityCheck_result
WHERE @Tenant = USER_NAME() OR USER_NAME() = 'Adam';
CREATE SECURITY POLICY TenantPolicy
ADD FILTER PREDICATE Security.udfSecurityCheck(TenantID)
ON dbo.Inventory
WITH (STATE = ON);

We can now run a simple SELECT query to see what results each user sees:

Execute as Paul:

EXECUTE AS USER = 'Paul';
SELECT * FROM Inventory;
REVERT;

Result:

InventoryID TenantID Product UnitPrice
4 Paul Crane 55500,00
5 Paul Truck 12300,00
6 Paul Trailer 3700,00

Execute as John:

EXECUTE AS USER = 'John';
SELECT * FROM Inventory;
REVERT;

Result:

InventoryID TenantID Product UnitPrice
1 John Notebook 500,00
2 John Tablet 300,00
3 John Smartphone 700,00

Execute as Adam (admin):

EXECUTE AS USER = 'Adam';
SELECT * FROM Inventory;
REVERT;

InventoryID TenantID Product UnitPrice
1 John Notebook 500,00
2 John Tablet 300,00
3 John Smartphone 700,00
4 Paul Crane 55500,00
5 Paul Truck 12300,00
6 Paul Trailer 3700,00

From the application perspective, each query is as simple as SELECT * FROM Inventory, there’s no WHERE clauses at all. All the filtering of records is happening behind the scenes through evaluation of each record in the inline function defined as a part of the security policy…. Oh, wait, a UDF is executed for each row in the table? Didn’t they always tell us this was a performance killer?

Great question! As long as it’s a single-statement inline table-valued function, the query optimizer will be able to process it without a performance hit but with the increasing complexity of the security logic, performance might rapidly deteriorate.

RLS in Middle-Tier Applications

The previous scenario assumes that each user connects to the database directly and executes his query in the context of his own credentials. While the simplicity of that example helped us understand the concept, it is time to take a look at a more realistic scenario:

Modern applications connect to the database using its own dedicated credentials so the users/tenants share one and the same database login. Row-Level security is still possible in this case but it is now the application’s responsibility to set the execution context for the session based on the user ID before executing any queries.

Here’s what we need to change in the example with the Inventory table:

CREATE TABLE Inventory
(
InventoryID int,
TenantID int,
Product nvarchar(255),
UnitPrice MONEY,
CONSTRAINT [PK] PRIMARY KEY CLUSTERED(InventoryID)
);
INSERT INTO dbo.Inventory VALUES ( 1, 1, N'Notebook' , 500 )
INSERT INTO dbo.Inventory VALUES ( 2, 1, N'Tablet' , 300 )
INSERT INTO dbo.Inventory VALUES ( 3, 1, N'Smartphone' , 700 )
INSERT INTO dbo.Inventory VALUES ( 4, 2, N'Crane' , 55500 )
INSERT INTO dbo.Inventory VALUES ( 5, 2, N'Truck' , 12300 )
INSERT INTO dbo.Inventory VALUES ( 6, 2, N'Trailer' , 3700 )

Next, we need to create a small stored procedure that will be used for setting the execution context:

CREATE PROCEDURE SetContextInfo(@TenantId int)
AS
SET CONTEXT_INFO @TenantId;

and then we need to change the inline function like this:

CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.udfSecurityCheck(@Tenant AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS udfSecurityCheck_result
WHERE DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('dbo')
AND CONVERT(int, CONVERT(VARBINARY(4), CONTEXT_INFO())) = @Tenant;

finally, we need to create the security policy:

CREATE SECURITY POLICY TenantPolicy
ADD FILTER PREDICATE Security.udfSecurityCheck(TenantID)
ON dbo.Inventory
WITH (STATE = ON);

That’s all that needs to be done. It is now the responsibility of the application to execute the  SetContextInfo stored procedure before each query that’s sent to the database server:

EXEC SetContextInfo 1;
SELECT * FROM Inventory;
1 1 Notebook 500,00
2 1 Tablet 300,00
3 1 Smartphone 700,00
EXEC SetContextInfo 2;
SELECT * FROM Inventory;
4 2 Crane 55500,00
5 2 Truck 12300,00
6 2 Trailer 3700,00

Security and performance considerations

As a new feature, RLS brings a number of potential risks that should be considered before implementation. The database schema we created in our example should be used to restrict access to and prevent modifications to the inline function we use for filtering records. If a user has enough permissions to modify that function than he can gain access to all protected data. In the middle-tier application scenario, special attention is required to the the possibility of SQL injection or ad-hoc queries that would allow a malicious user to set the CONTEXT_INFO and gain access to protected records.

Whatever scenario we implement, performance will be affected, the question is only how bad it will be. There are always pros and cons so RLS should only be implemented where it makes sense and when the server resource can support the workload. My next post will focus on performance tests so stay tuned.

Suggestions for further reading :

MSDN Documentation

RLS at SQL Security Blog

Multi-tenant applications with elastic database tools and row-level security