Microsoft SQL Server Row-level security RLS filter predicate

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Example

Sql Server 2016+ and Azure Sql database enables you to automatically filter rows that are returned in select statement using some predicate. This feature is called Row-level security.

First, you need a table-valued function that contains some predicate that describes what it the condition that will allow users to read data from some table:

DROP FUNCTION IF EXISTS dbo.pUserCanAccessCompany
GO
CREATE FUNCTION

dbo.pUserCanAccessCompany(@CompanyID int)

    RETURNS TABLE
    WITH SCHEMABINDING
AS RETURN (
    SELECT 1 as canAccess WHERE 

    CAST(SESSION_CONTEXT(N'CompanyID') as int) = @CompanyID

)

In this example, the predicate says that only users that have a value in SESSION_CONTEXT that is matching input argument can access the company. You can put any other condition e.g. that checks database role or database_id of the current user, etc.

Most of the code above is a template that you will copy-paste. The only thing that will change here is the name and arguments of predicate and condition in WHERE clause. Now you create security policy that will apply this predicate on some table.

Now you can create security policy that will apply predicate on some table:

CREATE SECURITY POLICY dbo.CompanyAccessPolicy
    ADD FILTER PREDICATE dbo.pUserCanAccessCompany(CompanyID) ON dbo.Company
    WITH (State=ON)

This security policy assigns predicate to company table. Whenever someone tries to read data from Company table , security policy will apply predicate on each row, pass CompanyID column as a parameter of the predicate, and predicate will evaluate should this row be returned in the result of SELECT query.



Got any Microsoft SQL Server Question?