How to implement Row-Level Security in Azure SQL Database or SQL Server

Some one of you prefer view-based workarounds for row-level filtering. The benefits of using Row-Level Security (RLS) instead of views include:

• RLS reduces code complexity by centralizing access logic in a security policy and eliminating the need for an extra view on top of every base table
• RLS avoids common runtime errors by requiring schemabinding and performing all permission checks when the policy is created, rather than when users query
• RLS simplifies application maintenance by allowing users and applications to query base tables directly

Moreover there are 3 way to implement RLS, in our case #3 I have implemented and if we can make #2 or #1 without join it will be much faster.

1. Rows assigned directly to users

The simplest way to use RLS is to assign each row directly to a user ID. A security policy can then ensure that rows can only be accessed by the assigned user.

2. Row assignments in a lookup table

A slightly more complex way to use RLS is to filter rows by looking up assignments in a helper table.

3. Row assignments from a JOIN

A more complicated RLS pattern is to look up row assignments by joining multiple helper tables in the filtering logic. In our case, I have implemented this we have one helper table (StaffDuties) mapping staff to multiple wing, and another (Employees) mapping staff to a Emp id. To filter so that users can only see rows in their assigned to staff wing. Follow below to get more details.

First you need to create an empty database 'RLS_Demo'.

This demo uses SQL Server's built-in security system (users and roles) to limit access to rows of patient data for nurses and doctors in a hospital. 

-- Create an empty tables, insert some dummy data, and create users and roles   

CREATE TABLE [patients] (   

    patientId INT PRIMARY KEY,  

    name nvarchar(256),   

    room int,   

    wing int,   

    startTime datetime,   

    endTime datetime   

)  

CREATE TABLE [employees] (   

    empId int PRIMARY KEY,   

    name nvarchar(256),   

    databasePrincipalId int   

)  

CREATE TABLE [staffDuties] (   

    empId int,   

    wing int,   

    startTime datetime,   

    endTime datetime   

)  

CREATE TABLE [wings] (   

    wingId int PRIMARY KEY,   

    name nvarchar(128)   

)  

go  

  

CREATE ROLE [nurse]  

CREATE ROLE [doctor]  

go  

  

GRANT SELECT, UPDATE ON [patients] to [nurse]  

GRANT SELECT, UPDATE ON [patients] to [doctor]  

go  

  

-- Create a user for each nurse & doctor (without logins to simplify demo)  

-- Add to corresponding role (in practice, these could also be Windows Groups)  

-- Add to employees table  

CREATE USER [nurse_BartonC] WITHOUT LOGIN  

ALTER ROLE [nurse] ADD MEMBER [nurse_BartonC]  

INSERT INTO [employees] VALUES ( 1001, N'Clara Barton', DATABASE_PRINCIPAL_ID('nurse_BartonC'));  

go  

  

CREATE USER [nurse_AllenM] WITHOUT LOGIN  

ALTER ROLE [nurse] ADD MEMBER [nurse_AllenM]  

INSERT INTO [employees] VALUES ( 1002, N'Moyra Allen', DATABASE_PRINCIPAL_ID('nurse_AllenM') );

go  

  

CREATE USER [nurse_NightingaleF] WITHOUT LOGIN  

ALTER ROLE [nurse] ADD MEMBER [nurse_NightingaleF]  

INSERT INTO [employees] VALUES ( 1003, N'Florence Nightingale', DATABASE_PRINCIPAL_ID('nurse_NightingaleF'));  

go  

  

CREATE USER [doctor_ApgarV] WITHOUT LOGIN  

ALTER ROLE [doctor] ADD MEMBER [doctor_ApgarV]  

INSERT INTO [employees] VALUES ( 2001, N'Virginia Apgar', DATABASE_PRINCIPAL_ID('doctor_ApgarV'));  

go  

  

CREATE USER [doctor_CharcotJ] WITHOUT LOGIN  

ALTER ROLE [doctor] ADD MEMBER [doctor_CharcotJ]  

INSERT INTO [employees] VALUES ( 2002, N'Jean-Martin Charcot', DATABASE_PRINCIPAL_ID('doctor_CharcotJ'));  

go  

  

INSERT INTO wings VALUES( 1, N'North');  

INSERT INTO wings VALUES( 2, N'South');  

INSERT INTO wings VALUES( 3, N'Emergency');  

go  

  

INSERT INTO [patients] VALUES ( 01, N'Ludwig van Beethoven', 101, 1, '12-17-2015',  '03-26-2016')  

INSERT INTO [patients] VALUES ( 02, N'Niccolo Paganini', 102, 1, '10-27-2015',  '05-27-2016')  

INSERT INTO [patients] VALUES ( 05, N'Pyotr Ilyich Tchaikovsky', 107, 1, '5-7-2015',  '11-6-2015')  

INSERT INTO [patients] VALUES ( 03, N'Carl Philipp Emanuel Bach', 203, 2, '3-8-2015',  '12-14-2015')  

INSERT INTO [patients] VALUES ( 04, N'Wolfgang Amadeus Mozart', 205, 2, '1-27-2015',  '12-5-2015')  

INSERT INTO [patients] VALUES ( 06, N'Philip Morris Glass', 301, 3, '1-31-2015',  null)  

INSERT INTO [patients] VALUES ( 07, N'Edvard Hagerup Grieg', 308, 3, '6-15-2015',  '9-4-2015')  

  

INSERT INTO [staffDuties] VALUES ( 1001, 1, '01-01-2015', '12-31-2015' )  

INSERT INTO [staffDuties] VALUES ( 1001, 2, '01-01-2016', '12-31-2016' )  

INSERT INTO [staffDuties] VALUES ( 1002, 1, '01-01-2015', '06-30-2015' )  

INSERT INTO [staffDuties] VALUES ( 1002, 2, '07-01-2015', '12-31-2015' )  

INSERT INTO [staffDuties] VALUES ( 1002, 3, '01-01-2016', '12-31-2016' )  

INSERT INTO [staffDuties] VALUES ( 1003, 3, '01-01-2015', '12-31-2016' )  

INSERT INTO [staffDuties] VALUES ( 2001, 1, '01-01-2015', '12-31-2015' )  

INSERT INTO [staffDuties] VALUES ( 2001, 3, '01-01-2016', '12-31-2016' )  

INSERT INTO [staffDuties] VALUES ( 2002, 1, '01-01-2015', '12-31-2016' )  

go  

  

-- END SETUP  

-- Quick look at existing schema  

SELECT * FROM patients;  

go  

-- Flatten employees and staffDuties to easily view assignments  

SELECT s.empId, name, user_name(databasePrincipalId) as SqlUserName, wing, startTime, endTime   

    FROM staffDuties s   

    INNER JOIN employees e ON (e.empId = s.empId)   

ORDER BY empId;  

go  

-- ENABLE ROW-LEVEL SECURITY  

  

-- Create separate schema for RLS objects (this is best practice to separate RLS objects)  

CREATE SCHEMA rls  

go  

  

-- RLS predicate allows access to rows based on a user's role and assigned staff duties.  

-- Because users have both SELECT and UPDATE permissions, we will use this function as a  

-- filter predicate (filter which rows are accessible by SELECT and UPDATE queries) and a   

-- block predicate after update (prevent user from updating rows to be outside of visible range).  

CREATE FUNCTION rls.accessPredicate(@wing int, @startTime datetime, @endTime datetime)  

    RETURNS TABLE   

    WITH SCHEMABINDING  

AS  

    RETURN SELECT 1 AS accessResult FROM  

        dbo.StaffDuties d INNER JOIN dbo.Employees e ON (d.EmpId = e.EmpId)   

    WHERE   

    (  

        -- nurses can only access patients who overlap with their wing assignments  

        IS_MEMBER('nurse') = 1  

        AND e.databasePrincipalId = DATABASE_PRINCIPAL_ID()   

        AND @wing = d.Wing  

        AND   

        (   

            d.endTime >= @startTime AND d.startTime <= ISNULL(@endTime, GETDATE())  

        )  

    )   

    OR   

    (  

        -- doctors can see all patients  

        IS_MEMBER('doctor') = 1  

    )  

go  

  

CREATE SECURITY POLICY rls.PatientsSecurityPolicy   

    ADD FILTER PREDICATE rls.accessPredicate(wing, startTime, endTime) ON dbo.patients,  

    ADD BLOCK PREDICATE rls.accessPredicate(wing, startTime, endTime) ON dbo.patients AFTER UPDATE  

go  

  

-- Impersonate various users in the system (for demo purposes)  

EXECUTE ('SELECT * FROM patients;') AS USER = 'nurse_BartonC';       --3  results

EXECUTE ('SELECT * FROM patients;') AS USER = 'nurse_AllenM';        --4  results

EXECUTE ('SELECT * FROM patients;') AS USER = 'nurse_NightingaleF';  --2  results

EXECUTE ('SELECT * FROM patients;') AS USER = 'doctor_ApgarV';       --7  results

EXECUTE ('SELECT * FROM patients;') AS USER = 'doctor_CharcotJ';     --7  results

EXECUTE ('UPDATE patients SET Wing = 1 WHERE patientId = 6;') AS USER = 'nurse_BartonC' -- filtered, 0 rows affected  

go  

EXECUTE ('UPDATE patients SET Wing = 3 WHERE patientId = 1;') AS USER = 'nurse_BartonC' 

go

-- blocked from changing Wing to an unassigned one, error is shown below

ERROR:

Msg 33504, Level 16, State 1, Line 3

The attempted operation failed because the target object 'RLS_Demo.dbo.patients' has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.

The statement has been terminated.

Completion time: 2020-06-05T23:58:56.7724555+08:00

-- Click 'Include Actual Execution Plan' in SSMS (Ctrl + M)  

-- This will show how the query plan changes when RLS is enabled  

ALTER SECURITY POLICY rls.PatientsSecurityPolicy WITH (STATE=OFF)  

SELECT * FROM patients  

go  

Query plan before security policy:

ALTER SECURITY POLICY rls.PatientsSecurityPolicy WITH (STATE=ON)  

SELECT * FROM patients  

go  

Query plan after security policy, having filter predicate push down, note the cost of extra operations:

-- Monitor security policies and predicates using these system views  

SELECT * FROM sys.security_policies  

SELECT * FROM sys.security_predicates  

go