MSSQL row-level security

MSSQL_RLS.sql

-- if I have the bellow table in a multi-tenant application, how do i prevent data-leaks 
-- if in the future, one or more sql-queries on this table forget to where tenant_id = x 
-- https://www.sqlshack.com/introduction-to-row-level-security-in-sql-server/
-- https://learn.microsoft.com/de-de/sql/relational-databases/security/row-level-security?view=sql-server-ver16

DROP FUNCTION IF EXISTS dbo.generate_series;
GO 

CREATE FUNCTION dbo.generate_series
(
    @start INT,
    @end INT
)
RETURNS TABLE
AS
RETURN
(
    WITH Numbers AS
    (
        SELECT @start AS generate_series 
        UNION ALL
        SELECT generate_series + 1
        FROM Numbers
        WHERE generate_series + 1 <= @end
    )
    SELECT generate_series
    FROM Numbers
);

GO 

-- Test: Generate a series of numbers from 1 to 10
SELECT generate_series FROM generate_series(1, 10);
SELECT generate_series FROM generate_series(11, 20);


GO


CREATE TABLE information_under_dataprotection_law
(
     id int not null constraint pk_information_under_dataprotection_law primary key 
	 ,tenant_id int not null
	 ,txt national character varying(4000) not null
	 ,val float not null
); 

GO


IF EXISTS
(
	SELECT 
		 istu.* 
		,cci.CHECK_CLAUSE 
	FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS istu 
	OUTER APPLY 
		(
			SELECT 
				CHECK_CLAUSE 
			FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS iscc 
			WHERE istu.CONSTRAINT_SCHEMA = iscc.CONSTRAINT_SCHEMA 
			AND istu.CONSTRAINT_NAME = iscc.CONSTRAINT_NAME 
		) AS cci 
	WHERE istu.TABLE_SCHEMA = N'dbo' 
	AND istu.TABLE_NAME = N'information_under_dataprotection_law' 
	AND istu.CONSTRAINT_NAME = N'chk_information_under_dataprotection_law_tenant_id' 
)
BEGIN
	EXECUTE(N'ALTER TABLE information_under_dataprotection_law DROP CONSTRAINT chk_information_under_dataprotection_law_tenant_id; '); 
END 


GO 


ALTER TABLE information_under_dataprotection_law
WITH NOCHECK ADD CONSTRAINT chk_information_under_dataprotection_law_tenant_id 
CHECK  (tenant_id = ISNULL(CAST(SESSION_CONTEXT(N'tenant_id') AS INT), 0))


GO 



-- Test predicate 
EXEC sp_set_session_context N'tenant_id', '1';
SELECT TOP 1 fn_securitypredicate_result FROM dbo.fn_tenant_predicate(1)


GO


-- Test check-constraint 
EXEC sp_set_session_context N'tenant_id', '1';
INSERT INTO information_under_dataprotection_law(id, tenant_id, txt, val) 
SELECT 
	 (SELECT MAX(ID) FROM information_under_dataprotection_law) + 1 AS id -- int
	,9999 AS tenant_id -- int
	,N'' AS txt -- nvarchar(4000)
	,0.1e AS val --  float 
;


GO




ALTER DATABASE multi_tenant SET ENABLE_ROW_LEVEL_SECURITY = ON;

GO

DROP FUNCTION IF EXISTS dbo.fn_tenant_predicate;

GO

CREATE FUNCTION dbo.fn_tenant_predicate(@tenant_id INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
-- RETURN SELECT 1 AS access_granted WHERE @tenant_id = 1; 
RETURN 
SELECT 
	 1 AS fn_securitypredicate_result 
WHERE @tenant_id = CAST(SESSION_CONTEXT(N'tenant_id') AS int) 
-- OR @tenant_id = 0 -- all 
;


GO


CREATE SECURITY POLICY TenantRowLevelSecurity
ADD FILTER PREDICATE dbo.fn_tenant_predicate(tenant_id) ON dbo.information_under_dataprotection_law
WITH (STATE = ON);

GO


-- DROP SECURITY POLICY TenantRowLevelSecurity; 

GO 




IF EXISTS
(
	SELECT 
		 istu.* 
		,cci.CHECK_CLAUSE 
	FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS istu 
	OUTER APPLY 
		(
			SELECT 
				CHECK_CLAUSE 
			FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS iscc 
			WHERE istu.CONSTRAINT_SCHEMA = iscc.CONSTRAINT_SCHEMA 
			AND istu.CONSTRAINT_NAME = iscc.CONSTRAINT_NAME 
		) AS cci 
	WHERE istu.TABLE_SCHEMA = N'dbo' 
	AND istu.TABLE_NAME = N'information_under_dataprotection_law' 
	AND istu.CONSTRAINT_NAME = N'chk_information_under_dataprotection_law_tenant_id' 
)
BEGIN
	EXECUTE(N'ALTER TABLE information_under_dataprotection_law DROP CONSTRAINT chk_information_under_dataprotection_law_tenant_id; '); 
END 


GO 


ALTER TABLE information_under_dataprotection_law
WITH NOCHECK ADD CONSTRAINT chk_information_under_dataprotection_law_tenant_id 
CHECK  (tenant_id = ISNULL(CAST(SESSION_CONTEXT(N'tenant_id') AS INT), 0))


GO 


-- Test check-constraint: 
EXEC sp_set_session_context N'tenant_id', '1';
SELECT TOP 1 fn_securitypredicate_result FROM dbo.fn_tenant_predicate(1)



INSERT INTO information_under_dataprotection_law(id, tenant_id, txt, val) 
SELECT 
	 (SELECT MAX(ID) FROM information_under_dataprotection_law) + 1 AS id -- int
	,9999 AS tenant_id -- int
	,N'' AS txt -- nvarchar(4000)
	,0.1e AS val --  float 
;



-- ======================================== Data ========================================


EXEC sp_set_session_context N'tenant_id', '3';
SELECT SESSION_CONTEXT(N'tenant_id');



EXEC sp_set_session_context N'tenant_id', '4';
SELECT SESSION_CONTEXT(N'tenant_id');




EXEC sp_set_session_context N'tenant_id', '1';
SELECT SESSION_CONTEXT(N'tenant_id') AS session_tenant_id;
SELECT * FROM information_under_dataprotection_law; 


-- SELECT * FROM information_under_dataprotection_law; 
-- DELETE FROM information_under_dataprotection_law; 

EXEC sp_set_session_context N'tenant_id', '1';

INSERT INTO information_under_dataprotection_law(id, tenant_id, txt, val) 
SELECT 
     generate_series AS id -- int not null constraint pk_information_under_dataprotection_law primary key 
	 ,1 AS tenant_id -- int not null
	 ,N'' AS txt -- national character varying(4000) not null
	 ,generate_series*5.0 AS float -- not null
FROM generate_series(1,10);


EXEC sp_set_session_context N'tenant_id', '2';

INSERT INTO information_under_dataprotection_law(id, tenant_id, txt, val) 
SELECT 
     generate_series AS id -- int not null constraint pk_information_under_dataprotection_law primary key 
	 ,1 AS tenant_id -- int not null
	 ,N'' AS txt -- national character varying(4000) not null
	 ,generate_series*5.0 AS float -- not null
FROM generate_series(11,20); 


EXEC sp_set_session_context N'tenant_id', '3';

INSERT INTO information_under_dataprotection_law(id, tenant_id, txt, val) 
SELECT 
     generate_series AS id -- int not null constraint pk_information_under_dataprotection_law primary key 
	 ,3 AS tenant_id -- int not null
	 ,N'' AS txt -- national character varying(4000) not null
	 ,generate_series*5.0 AS float -- not null
FROM generate_series(21,30);




EXEC sp_set_session_context N'tenant_id', '4';
INSERT INTO information_under_dataprotection_law(id, tenant_id, txt, val) 
SELECT 
     generate_series AS id -- int not null constraint pk_information_under_dataprotection_law primary key 
	 ,4 AS tenant_id -- int not null
	 ,N'' AS txt -- national character varying(4000) not null
	 ,generate_series*5.0 AS float -- not null
FROM generate_series(31,40);





-- SET app.current_tenant_id = '3'; -- Replace 'X' with the actual tenant ID
EXEC sp_set_session_context N'tenant_id', 'X';
SELECT * FROM information_under_dataprotection_law; 



EXEC sp_set_session_context N'tenant_id', '1';
SELECT * FROM information_under_dataprotection_law; 


-- ======================================== DELETE ========================================


EXEC sp_set_session_context N'tenant_id', '4';
DELETE FROM information_under_dataprotection_law


SELECT * FROM information_under_dataprotection_law

-- ======================================== TEST ========================================


EXEC sp_set_session_context N'tenant_id', '1';
-- SELECT SESSION_CONTEXT(N'tenant_id') AS session_tenant_id;

-- SELECT * FROM dbo.fn_tenant_predicate(tenant_id)

SELECT 
	 information_under_dataprotection_law.id
	,information_under_dataprotection_law.tenant_id AS x 
	,t.*
FROM information_under_dataprotection_law 
OUTER APPLY 
(
	SELECT * FROM dbo.fn_tenant_predicate(information_under_dataprotection_law.tenant_id)
) AS t