Row-Level Security in Azure SQL > Block Predicates

A new feature in SQL Azure is RLS (Row-level security). It allows control of access to rows in a database table based on details about the user executing the query. Azure SQL v12 is required for RLS, you can check this by running the query select @@version

To have a look at how RLS works we will create a table in an Azure SQL database and populate it with some data. The table will contain information about projects and include a ProjectManager column that contains the user that can view a particular project.

CREATE TABLE Project
    (
    Id int NOT NULL PRIMARY KEY,
    ProjectManager sysname,
    Name varchar(100)
    );


INSERT Project VALUES
(1, 'PM1', 'Project ABC'),
(2, 'PM1', 'Project XYZ'),
(3, 'PM1', 'Project 1'),
(4, 'PM2', 'Project 2'),
(5, 'PM2', 'Some other project')

Next, we will create some users and provide them full read access to the projects table we have created. We will create two project manager users also add a user called ‘Manager’ that will be able to view all projects.

CREATE USER Manager WITHOUT LOGIN;
CREATE USER PM1 WITHOUT LOGIN;
CREATE USER PM2 WITHOUT LOGIN;

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

Next, we need to create a function on a separate schema. The function will return TRUE if the function parameter (that will in the situation be the ProjectManager column) is the same as the current user’s name or if the user’s name is ‘Manager’.

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.projectSecurityPredicate(@ProjectManager AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
      WHERE @ProjectManager = USER_NAME() OR USER_NAME() = 'Manager';

The last thing we need to do is create a new security policy that takes the function we have created above and maps it to the Project table.

CREATE SECURITY POLICY ProjectFilter
ADD FILTER PREDICATE Security.projectSecurityPredicate(ProjectManager)
ON dbo.Project
WITH (STATE = ON);

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

EXECUTE AS USER = 'PM1';
SELECT * FROM Project; -- returns 3 rows
REVERT;

EXECUTE AS USER = 'Pm2';
SELECT * FROM Project; -- returns 2 rows
REVERT;

EXECUTE AS USER = 'Manager';
SELECT * FROM Project; -- returns all rows
REVERT;

A more complex and common scenario is where multiple users have access to a project. This can be achieved by the using a simple mapping table.

CREATE TABLE Project
    (
    Id int NOT NULL PRIMARY KEY,
    Name varchar(100)
    );

CREATE TABLE Mapping
    (
    ProjectId int,
    ProjectManager sysname,
    );  

INSERT Project VALUES
(1, 'Project ABC'),
(2, 'Project XYZ'),
(3, 'Project 1'),
(4, 'Project 2'),
(5, 'Some other project')

INSERT Mapping VALUES
(1, 'PM1' ),
(2, 'PM1' ),
(3, 'PM1' ),
(4, 'PM2' ),
(5, 'PM1' ),
(5, 'PM2' )

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.projectSecurityPredicate(@ProjectId AS int)
  RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN SELECT 1 AS fn_securitypredicate_result FROM dbo.Mapping
    WHERE ProjectId = @ProjectId AND ProjectManager = USER_NAME() OR USER_NAME() = 'Manager';  

CREATE SECURITY POLICY ProjectFilter
ADD FILTER PREDICATE Security.projectSecurityPredicate(Id)
ON dbo.Project
WITH (STATE = ON);

This blog post was written by

Craig Pickles

Software developer, team leader and technologist.