My previous post was focused on controlling read operations. With the latest enhancements to the row-level security, it is now possible to restrict write operations as well. This feature is currently available as a preview now generally available in Azure SQL Database V12. Let’s see how that works.

Filtering and Blocking

The Row-Level Security now supports two types of predicates:

– Filter Predicates silently filter rows in read operations – SELECT, UPDATE and DELETE,

– Block Predicates explicitly block write operations – INSERT, UPDATE and DELETE

Access to table rows is restricted by the rules defined in an in-line user-defined function and enforced by a security policy. In case of data read operations, application is not aware of any filtering at the server side (silent filtering). But for write operations, application will receive an error in case of blocking (explicit blocking).

Using the example from my previous post, here’s the sample code:

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 )
CREATE PROCEDURE SetContextInfo(@TenantId int)
AS
SET CONTEXT_INFO @TenantId;
GO
CREATE SCHEMA Security;
GO
CREATE USER AppUser WITHOUT LOGIN;
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON Inventory TO AppUser;
DENY UPDATE ON Inventory(TenantId) TO AppUser;
GO
CREATE FUNCTION Security.udfSecurityCheck(@Tenant AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS udfSecurityCheck_result
WHERE @Tenant = CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO()));
GO
CREATE SECURITY POLICY Security.TenantPolicy
ADD FILTER PREDICATE Security.udfSecurityCheck(TenantID) ON dbo.Inventory,
ADD BLOCK PREDICATE Security.udfSecurityCheck(TenantID) ON dbo.Inventory AFTER INSERT;

This will restrict writes in the Inventory table so that a tenant cannot INSERT records with other tenant’s ID. Here’s an example:

Tenant 1 inserts his own data:

EXECUTE AS USER = 'AppUser';
SET CONTEXT_INFO 1;
INSERT INTO Inventory VALUES (7, 1, 'Car1', 100000);

Tenant 1 tries to insert data for the tenant 2 which fails throwing an error:

EXECUTE AS USER = 'AppUser';
SET CONTEXT_INFO 1;
INSERT INTO Inventory VALUES (8, 2, 'Car2', 100000);
Msg 33504, Level 16, State 1, Line 57
The attempted operation failed because the target object 'MyDatabase.dbo.Inventory' 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.

Now, let’s try to update a record:

EXECUTE AS USER = 'AppUser';
SET CONTEXT_INFO 1;
UPDATE Inventory SET Product = N'Airplane'
WHERE InventoryID=7;
EXECUTE AS USER = 'AppUser';
SET CONTEXT_INFO 2;
UPDATE Inventory SET Product = N'Airplane'
WHERE InventoryID=7;
(1 row(s) affected)
(0 row(s) affected)

The count of affected records shows that the Tenant 1 was able to update his own record but Tenant 2 was silently denied access to the same record. The same thing happens when tenants try to delete a record:

EXECUTE AS USER = 'AppUser';
SET CONTEXT_INFO 1;
DELETE FROM Inventory
WHERE InventoryID=4;
EXECUTE AS USER = 'AppUser';
SET CONTEXT_INFO 2;
DELETE FROM Inventory
WHERE InventoryID=4;
(0 row(s) affected)
(1 row(s) affected)

BEFORE and AFTER

The following blocking predicates are currently supported: AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE. The following is a concise explanation of their behavior:

AFTER INSERT and AFTER UPDATE prevent changing data to values that would violate the predicate (explicit blocking). In our test example, these don’t allow one tenant to enter data for another tenant or to change the ownership of an item in the table Inventory.

BEFORE UPDATE and BEFORE DELETE prevent users changing/deleting rows that currently violate the predicate (silent filtering). In our multi-tenancy example above, this assures that one tenant cannot change or delete other tenants’ data.

Suggestions for further reading:

MSDN Documentation

RLS at SQL Security Blog