RLS (Row Level Security)

Laat een reactie achter / Door /

SQL RLS is een functie die wordt gebruikt om de toegang tot rijen in een database te beperken op basis van bepaalde criteria. Met behulp van SQL RLS kunnen beheerders bepalen welke gebruikers of groepen toegang hebben tot bepaalde rijen in een tabel.

Deze functie is met name handig in situaties waarin een organisatie gevoelige gegevens in een database heeft opgeslagen en wil voorkomen dat onbevoegde gebruikers toegang hebben tot deze gegevens. SQL RLS zorgt ervoor dat alleen geautoriseerde gebruikers toegang hebben tot de gegevens die ze nodig hebben om hun werk te doen.

De werking van SQL RLS is gebaseerd op het toepassen van bepaalde filters op de resultaten van SQL-query’s. Deze filters beperken de resultaten tot alleen de rijen die de gebruiker mag zien, terwijl andere rijen worden verborgen. De filters worden gedefinieerd in beleidsregels die zijn gekoppeld aan een bepaalde tabel.

Het implementeren van SQL RLS kan complex zijn en vereist een goede planning en ontwerp van de database. Het is belangrijk om de beleidsregels zorgvuldig te definiëren en te testen om ervoor te zorgen dat ze correct werken en de gewenste beveiligingsniveaus bieden.

Al met al biedt SQL RLS een effectieve manier om de toegang tot gevoelige gegevens in een database te beheren en te beperken, waardoor organisaties een hoger niveau van beveiliging kunnen bereiken.

Het was lastig om de listing te vinden op het internet. Dus moest het maar worden overgetypt. De listing gaat over het onderdeel Ik zal er later wat meer over schrijven.

Hieronder creëren wij eerst de database die wij gaan gebruiken:

CREATE DATABASE Hospital;
GO

USE Hospital;

-- Create database schema
CREATE TABLE Patients (
     PatientID INT PRIMARY KEY,
     PatientName NVARCHAR(256),
     Room INT,
     WardID INT,
     StartTime DATETIME,
     EndTime DATETIME
);
CREATE TABLE Staff (
     StaffID INT PRIMARY KEY,
     StaffName NVARCHAR(256),
     DatabasePrincipalID INT
);
CREATE TABLE StaffDuties (
     StaffID INT,
     WardID INT,
     StartTime DATETIME,
     EndTime DATETIME
);
CREATE TABLE Wards (
     WardID INT PRIMARY KEY,
     Ward NVARCHAR(128)
);
GO

Vervolgens gaan wij het schema instellen en maken wij de tabellen aan:

-- Create database schema
CREATE TABLE Patients (
     PatientID INT PRIMARY KEY,
     PatientName NVARCHAR(256),
     Room INT,
     WardID INT,
     StartTime DATETIME,
     EndTime DATETIME
);
CREATE TABLE Staff (
     StaffID INT PRIMARY KEY,
     StaffName NVARCHAR(256),
     DatabasePrincipalID INT
);
CREATE TABLE StaffDuties (
     StaffID INT,
     WardID INT,
     StartTime DATETIME,
     EndTime DATETIME
);
CREATE TABLE Wards (
     WardID INT PRIMARY KEY,
     Ward NVARCHAR(128)
);
GO

Dan maken wij de rollen en gebruikers aan:

-- Create roles for nurses and doctors
CREATE ROLE Nurse;
CREATE ROLE Doctor;

-- Grant permissions to nursus and doctors
GRANT SELECT, UPDATE ON Patients TO Nurse;
GRANT SELECT, UPDATE ON Patients TO Doctor;
GO

-- Create a user for each dotor and nurse
CREATE USER NurseMarcus WITHOUT LOGIN;
ALTER ROLE Nurse ADD MEMBER NurseMarcus;
INSERT Staff VALUES ( 100, N'Nurse Marcus', DATABASE_PRINCIPAL_ID('NurseMarcus'));
GO

CREATE USER NurseIsabelle WITHOUT LOGIN;
ALTER ROLE Nurse ADD MEMBER NurseIsabelle;
INSERT Staff VALUES ( 101, N'Nurse Isabelle', DATABASE_PRINCIPAL_ID('NurseIsabelle'));
GO

CREATE USER DotorChristopher WITHOUT LOGIN;
ALTER ROLE Doctor ADD MEMBER DotorChristopher;
INSERT Staff VALUES ( 200, N'Dotor Christopher', DATABASE_PRINCIPAL_ID('DotorChristopher'));
GO

CREATE USER DotorSofia WITHOUT LOGIN;
ALTER ROLE Doctor ADD MEMBER DotorSofia;
INSERT Staff VALUES ( 201, N'Dotor Sofia', DATABASE_PRINCIPAL_ID('DotorSofia'));
GO

Daarna vullen wij de tabellen met de juiste data:

--Insert ward data
INSERT Wards VALUES ( 1, N'Emergency');
INSERT Wards VALUES ( 2, N'Maternity');
INSERT Wards VALUES ( 3, N'Pediatrics');
GO

-- Insert patient data
INSERT Patients VALUES ( 1001, N'Victor', 101, 1, '20171217', '20180326')
INSERT Patients VALUES ( 1002, N'Maria', 102, 1, '20170227', '20180527')
INSERT Patients VALUES ( 1003, N'Nick', 107, 1, '20170507', '20171106')
INSERT Patients VALUES ( 1004, N'Nina', 203, 2, '20170308', '20171214')
INSERT Patients VALUES ( 1005, N'Larissa', 205, 2, '20170127', '20170512')
INSERT Patients VALUES ( 1006, N'Marc', 301, 3, '20170131', NULL)
INSERT Patients VALUES ( 1007, N'Sofia', 308, 3, '20170615', '20170904')
GO

-- Insert nurses duties
INSERT StaffDuties VALUES ( 101, 1, '20170101', '20171231')
INSERT StaffDuties VALUES ( 101, 2, '20180101', '20181231')
INSERT StaffDuties VALUES ( 102, 1, '20170101', '20170630')
INSERT StaffDuties VALUES ( 102, 2, '20170101', '20171231')
INSERT StaffDuties VALUES ( 102, 3, '20180101', '20181231')
INSERT StaffDuties VALUES ( 200, 1, '20170101', '20171231')
INSERT StaffDuties VALUES ( 200, 3, '20180101', '20181231')
INSERT StaffDuties VALUES ( 201, 1, '20170101', '20181231')
GO

-- Insert doctors duties
INSERT StaffDuties VALUES ( 200, 1, '20170101', '20171231')
INSERT StaffDuties VALUES ( 200, 3, '20180101', '20181231')
INSERT StaffDuties VALUES ( 201, 1, '20170101', '20181231')
GO

Hier laten wij een aantal query’s op los:

 -- Query patients
SELECT * FROM Patients;
 
-- Query assignments
SELECT d.staffID, StaffName, USER_NAME(DatabasePrincipalID) as DatabaseUser, WardID, StartTime, 
            EndTime
FROM StaffDuties d
INNER JOIN Staff s ON (s.StaffID = d.StaffID)
ORDER BY StaffID;
GO

Nu gaan wij op deze tabellen de RLS activeren:

-- Implement row level security
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).
-- RLS predicate allows data access based on role and staff duties.

CREATE FUNCTION RLS.AccessPredicate(@Ward INT, @StartTime DATETIME, @EndTime DATETIME)
     RETURNS TABLE
     WITH SCHEMABINDING
AS
RETURN SELECT 1 AS Access
FROM dbo.StaffDuties AS d JOIN dbo.Staff AS s ON d.StaffID = s.StaffID
WHERE ( -- Nurses can only see patients who overlap with their wing assignments
     IS_MEMBER('Nurse') = 1
     AND s.DatabasePrincipalID = DATABASE_PRINCIPAL_ID()
     AND @Ward =  d.WardID
     AND (d.EndTime >= @StartTime AND d.StartTime <= ISNULL(@EndTime, GETDATE()))
) OR ( -- Doctors can see all patients IS_MEMBER('Doctor') = 1
);
GO

Nu gaan wij een een aantal testen doen:

-- RLS filter predicate filters which data is seen by SELECT and UPDATE queries
-- RLS block predicate after update prevents updating data outside of visible range
CREATE SECURITY POLICY RLS.PatientsSecurityPolicy
ADD FILTER PREDICATE RLS.AccessPredicate(WardID, StartTime, EndTime) ON dbo.Patients,
ADD BLOCK PREDICATE RLS.AccessPredicate(WardID, StartTime, EndTime) ON dbo.Patients
AFTER UPDATE;
GO

-- Test RLS
-- Impersonate a nurse
EXECUTE ('SELECT * FROM patients;') AS USER = 'NurseIsabelle';
-- Only 3 patient records seen
GO

-- Impersonate a doctor
EXECUTE ('SELECT * FROM patients;') AS USER = 'DotorChristopher';
-- All 7 patient records returned
GO

-- Attempt by nurse to move patient to another ward
EXECUTE ('UPDATE patients SET WardID = 111 WHERE patientId = 1006;') AS USER = 'NurseIsabelle'
-- Filtered, consequently 0 rows affected

EXECUTE ('UPDATE patients SET WardID = 3 WHERE patientId = 1001;') AS USER = 'NurseIsabelle'
-- Blocked from changing wing, with following error:
/*
MSG 33504, level 16, State 1, Line 156
The attempted operation failed because teh target object 'Hospital.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.
*/

Hieronder nog een voorbeeld en de stappen die moet doorlopen:

Als je een SQL RLS-beleid wilt invoeren, zijn er verschillende stappen die je moet volgen:

  1. Bepaal de criteria voor toegang: Definieer welke criteria moeten worden gebruikt om de toegang tot rijen in de database te beperken. Dit kunnen bijvoorbeeld gebruikersrollen, afdelingen of locaties zijn.
  2. Maak beleidsregels: Maak beleidsregels die specifieke criteria voor toegang definiëren en koppelen deze aan de betreffende tabellen. Bijvoorbeeld, een beleidsregel kan bepalen dat alleen gebruikers met de rol “manager” toegang hebben tot bepaalde rijen in een tabel.
  3. Implementeer het beleid: Implementeer het beleid in de database en zorg ervoor dat de beleidsregels correct worden toegepast bij het uitvoeren van query’s op de betreffende tabellen.
  4. Test het beleid: Test het beleid om ervoor te zorgen dat het correct werkt en de gewenste beveiligingsniveaus biedt.
  5. Onderhoud het beleid: Het is belangrijk om het beleid regelmatig te onderhouden en te updaten om ervoor te zorgen dat het nog steeds aan de vereisten voldoet.

Het is raadzaam om de implementatie van het SQL RLS-beleid te plannen en uit te voeren als onderdeel van een bredere beveiligingsstrategie voor de database. Dit kan omvatten het regelmatig bijwerken van wachtwoorden, het beperken van toegang tot de database en het monitoren van de activiteit in de database om ongeautoriseerde toegang te detecteren.

-- Stap 1: Bepaal de criteria voor toegang
CREATE FUNCTION fn_securitypredicate (@SalesPersonID AS int)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
    WHERE @SalesPersonID = USER_NAME();

-- Stap 2: Maak beleidsregels
CREATE SECURITY POLICY SalesFilterPolicy
    ADD FILTER PREDICATE dbo.fn_securitypredicate(SalesPersonID) 
    ON dbo.SalesData
    WITH (STATE = ON);

-- Stap 3: Implementeer het beleid
ALTER TABLE dbo.SalesData ENABLE ROW LEVEL SECURITY;

-- Stap 4: Test het beleid
-- Voer een SELECT-query uit en controleer of alleen de rijen worden geretourneerd waarvoor de gebruiker gemachtigd is

SELECT * FROM dbo.SalesData;

-- Stap 5: Onderhoud het beleid
-- Werk het beleid bij indien nodig en test opnieuw

Laat een reactie achter

Je e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll naar boven