rls.sql

-- https://stackoverflow.com/questions/53141968/row-level-security-not-working-as-expected
USE master
GO

DROP DATABASE IF EXISTS rls

CREATE DATABASE rls
GO
ALTER DATABASE rls SET RECOVERY SIMPLE
GO

USE rls
GO

-- Create the users
CREATE USER tester1 WITHOUT LOGIN;
CREATE USER tester2 WITHOUT LOGIN;
GO

-- Create a table to test RLS against
CREATE TABLE dbo.testOrder
    (
	rowId		INT IDENTITY PRIMARY KEY,
	someData	UNIQUEIDENTIFIER DEFAULT NEWID(),
	groupId		INT NOT NULL,
	regionId	INT NOT NULL
    );
GO


-- Add some dummy data
INSERT dbo.testOrder ( groupId, regionId )
VALUES
	( 10, 1 ),
	( 20, 2 ),
	( 20, 2 ),
	( 30, 3 ),
	( 30, 3 ),
	( 30, 3 );
GO

-- View all rows in the table before any security is applied
SELECT 'test 0' s, * FROM dbo.testOrder;
GO

GRANT SELECT ON dbo.testOrder TO tester1;
GRANT SELECT ON dbo.testOrder TO tester2;
GO


CREATE FUNCTION dbo.fn_securitypredicate( @regionId INT )
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN

	SELECT 1 AS fn_securitypredicate_result
	WHERE @regionId = 1
	   OR IS_SRVROLEMEMBER( N'sysadmin' ) = 1;

GO


CREATE SECURITY POLICY userFilter
ADD FILTER PREDICATE dbo.fn_securitypredicate( regionId )
ON dbo.testOrder
WITH ( STATE = ON );
GO

-- Tests
EXECUTE AS USER = 'tester1';
SELECT 'test 1' s, * FROM dbo.testOrder;
REVERT;

EXECUTE AS USER = 'tester2';
SELECT 'test 2' s, * FROM dbo.testOrder;
REVERT;

-- Switch off the policy ...
ALTER SECURITY POLICY userFilter
WITH ( STATE = OFF );
GO