Microsoft SQL Server Row-level security Preventing updated using RLS block predicate
Example
Row-level security enables you to define some predicates that will control who could update rows in the table. First you need to define some table-value function that represents predicate that wll control access policy.
CREATE FUNCTION
dbo.pUserCanAccessProduct(@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 we can create security policy with the predicate that will block updates on product table if CompanyID column in table do not satisfies predicate.
CREATE SECURITY POLICY dbo.ProductAccessPolicy ADD BLOCK PREDICATE dbo.pUserCanAccessProduct(CompanyID) ON dbo.Product
This predicate will be applied on all operations. If you want to apply predicate on some operation you can write something like:
CREATE SECURITY POLICY dbo.ProductAccessPolicy ADD BLOCK PREDICATE dbo.pUserCanAccessProduct(CompanyID) ON dbo.Product AFTER INSERT
Possible options that you can add after block predicate definition are:
[ { AFTER { INSERT | UPDATE } }
| { BEFORE { UPDATE | DELETE } } ]
