Microsoft SQL Server Row-level security RLS filter predicate
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.
