Row-Level Security in Azure SQL > Block Predicates

Part 1: Row-Level Security in Azure SQL > Filter Predicates

Block predicates enable security policies to prevent inserting, updating or delete rows.

As with filter predicates Azure SQL v12 is required for RLS, you can check this by running the query select @@version

Whereas filter predicates apply to select operations, block predicates can apply to write operations and can have an additional operation that defines when the predicate is applied:

AFTER INSERT and AFTER UPDATE, checks a new row value against the predicate
BEFORE UPDATE and BEFORE DELETE, checks an existing row value against the predicate

If no operation is specified it will apply to all operations.

To have a look at how RLS Block predicates works we will create a simple example that restricts deletes on a ‘Project’ table. First we grant all users delete permissions.

GRANT DELETE ON Project TO Manager;
GRANT DELETE ON Project TO PM1;
GRANT DELETE ON Project TO PM2;

Next we create the predicate function on a new ‘Security’ schema that returns true if the current user name is ‘Manager’.

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.projectSecurityDeletePredicate()
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS myResult
      WHERE USER_NAME() = 'Manager';

Next we need to create a new security policy that takes the block function we created above and map it to the Project table before a row is deleted.

CREATE SECURITY POLICY projectSecurityPolicy
ADD BLOCK PREDICATE Security.projectSecurityDeletePredicate()
ON dbo.Project BEFORE DELETE
WITH (STATE = ON);

Now we can test the block predicate by executing some queries as the various users we created.

EXECUTE AS USER = 'PM1';
DELETE TOP 1 FROM Poject; -- blocked from deleting
REVERT;

EXECUTE AS USER = 'Pm2';
DELETE TOP 1 FROM Poject; -- blocked from deleting
REVERT;

EXECUTE AS USER = 'Manager';
DELETE TOP 1 FROM Poject; -- one row deleted
REVERT;