Delen via

Beveiliging per rij

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL Analytics-eindpunt in Microsoft FabricMagazijn in Microsoft FabricSQL-database in Microsoft Fabric Preview

Decoratieve afbeelding van beveiliging op rijniveau.

Met beveiliging op rijniveau (RLS) kunt u groepslidmaatschap of uitvoeringscontext gebruiken om de toegang tot rijen in een databasetabel te beheren.

Beveiliging op rijniveau vereenvoudigt het ontwerpen en coderen van beveiliging in uw toepassing. RLS (Beveiliging op rijniveau) helpt u beperkingen te implementeren voor toegang tot gegevensrijen. U kunt er bijvoorbeeld voor zorgen dat werknemers alleen toegang hebben tot de gegevensrijen die relevant zijn voor hun afdeling. Een ander voorbeeld is om de gegevenstoegang van klanten te beperken tot alleen de gegevens die relevant zijn voor hun bedrijf.

De toegangsbeperkingslogica bevindt zich in de databaselaag in plaats van de gegevens in een andere toepassingslaag. Het databasesysteem past de toegangsbeperkingen toe telkens wanneer gegevenstoegang wordt geprobeerd vanuit elke laag. Hierdoor is uw beveiligingssysteem betrouwbaarder en robuuster door het oppervlak van uw beveiligingssysteem te verminderen.

Implementeer RLS (beveiliging op rijniveau) met behulp van de CREATE SECURITY POLICY Transact-SQL-instructie en predicaten die zijn gemaakt als inline-tabelwaarde-functies.

Beveiliging op rijniveau is voor het eerst geïntroduceerd in SQL Server 2016 (13.x).

Note

Dit artikel is gericht op SQL Server- en Azure SQL-platforms. Zie Beveiliging op rijniveau in Microsoft Fabric.

Description

Beveiliging op rijniveau (RLS) ondersteunt twee typen beveiligingspredicaten:

  • Filterpredicaten filteren op de achtergrond de rijen die beschikbaar zijn voor leesbewerkingen (SELECT, UPDATE, en DELETE).

  • Blokpredicaten blokkeren expliciet schrijfbewerkingen (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, ) BEFORE DELETEdie het predicaat schenden.

Toegang tot gegevens op rijniveau in een tabel wordt beperkt door een beveiligingspredicaat dat is gedefinieerd als een inline-tabelwaardefunctie. De functie wordt vervolgens aangeroepen en afgedwongen door een beveiligingsbeleid. Voor filterpredicaten is de toepassing niet op de hoogte van rijen die zijn gefilterd uit de resultatenset. Als alle rijen worden gefilterd, wordt er een null-set geretourneerd. Voor blokpredicaten mislukken alle bewerkingen die het predicaat schenden met een fout.

Filterpredicaten worden toegepast tijdens het lezen van gegevens uit de basistabel. Ze zijn van invloed op alle get-bewerkingen: SELECT, DELETEen UPDATE. De gebruikers kunnen geen rijen selecteren of verwijderen die zijn gefilterd. De gebruiker kan geen rijen bijwerken die zijn gefilterd. Maar het is mogelijk om rijen zo bij te werken dat ze later worden gefilterd. Blokpredicaten zijn van invloed op alle schrijfbewerkingen.

  • AFTER INSERT en AFTER UPDATE predicaten kunnen voorkomen dat gebruikers rijen bijwerken naar waarden die het predicaat schenden.

  • BEFORE UPDATE predicaten kunnen voorkomen dat gebruikers rijen bijwerken die momenteel het predicaat schenden.

  • BEFORE DELETE predicaten kunnen verwijderingsbewerkingen blokkeren.

Zowel filter- als blokpredicaten en beveiligingsbeleid hebben het volgende gedrag:

  • U kunt een predicaatfunctie definiëren die wordt samengevoegd met een andere tabel en/of een functie aanroept. Als het beveiligingsbeleid wordt gemaakt met SCHEMABINDING = ON (de standaardinstelling), is de join of functie toegankelijk vanuit de query en werkt het zoals verwacht zonder extra machtigingscontroles. Als het beveiligingsbeleid wordt gemaakt met SCHEMABINDING = OFF, hebben gebruikers machtigingen nodig SELECT voor deze extra tabellen en functies om een query uit te voeren op de doeltabel. Als de predicaatfunctie een CLR schaalwaarde functie uitvoert, is de EXECUTE machtiging daarnaast nodig.

  • U kunt een query uitvoeren voor een tabel waarvoor een beveiligingspredicaat is gedefinieerd, maar die is uitgeschakeld. Rijen die worden gefilterd of geblokkeerd, worden niet beïnvloed.

  • Als een dbo gebruiker, lid van de rol of de eigenaar van de db_owner tabel een query op een tabel met een beveiligingsbeleid heeft gedefinieerd en ingeschakeld, worden de rijen gefilterd of geblokkeerd zoals gedefinieerd door het beveiligingsbeleid.

  • Pogingen om het schema van een tabel te wijzigen die afhankelijk is van een schemagebonden beveiligingsbeleid, resulteert in een fout. Kolommen waarop het predicaat geen betrekking heeft, kunnen echter worden gewijzigd.

  • Pogingen om een predicaat toe te voegen aan een tabel waarvoor al een predicaat is gedefinieerd voor de opgegeven bewerking, resulteert in een fout. Dit gebeurt of het predicaat al dan niet is ingeschakeld.

  • Pogingen om een functie te wijzigen die wordt gebruikt als predicaat voor een tabel in een schemagebonden beveiligingsbeleid, resulteert in een fout.

  • Het definiëren van meerdere actieve beveiligingsbeleidsregels die niet-overlapping predicaten bevatten, slaagt.

Filterpredicaten hebben het volgende gedrag:

  • Definieer een beveiligingsbeleid waarmee de rijen van een tabel worden gefilterd. De toepassing is niet op de hoogte van rijen die zijn gefilterd op SELECT, UPDATEen DELETE bewerkingen. Inclusief situaties waarin alle rijen worden uitgefilterd. De toepassing kan rijen maken INSERT , zelfs als ze tijdens een andere bewerking worden gefilterd.

Blokpredicaten hebben het volgende gedrag:

  • Blokpredicaten voor UPDATE worden gesplitst in afzonderlijke bewerkingen voor BEFORE en AFTER. U kunt bijvoorbeeld niet voorkomen dat gebruikers een rij bijwerken om een waarde hoger te hebben dan de huidige. Als dit soort logica is vereist, moet u triggers gebruiken met de tussenliggende tabellen DELETED en INSERTED om te verwijzen naar de oude en nieuwe waarden.

  • De optimizer controleert een blokpredicaat niet als de kolommen die door de predicaatfunctie worden gebruikt, niet zijn gewijzigd. Bijvoorbeeld: Alice mag een salaris niet meer dan 100.000 wijzigen. Alice kan het adres wijzigen van een werknemer waarvan het salaris al groter is dan 100.000 zolang de kolommen waarnaar wordt verwezen in het predicaat niet zijn gewijzigd.

  • Er zijn geen wijzigingen aangebracht in de bulk-API's, waaronder BULK INSERT. Dit betekent dat blokpredicaten van toepassing zijn op bulk invoegbewerkingen, net zoals op normale invoegbewerkingen.

Gebruikssituaties

Hier volgen ontwerpvoorbeelden van hoe beveiliging op rijniveau (RLS) kan worden gebruikt:

  • Een ziekenhuis kan een beveiligingsbeleid maken waarmee verpleegsters alleen gegevensrijen voor hun patiënten kunnen bekijken.

  • Een bank kan een beleid maken om de toegang tot rijen met financiële gegevens te beperken op basis van de bedrijfsafdeling of rol van een werknemer in het bedrijf.

  • Een multitenant-toepassing kan een beleid maken om een logische scheiding af te dwingen van de gegevensrijen van elke tenant uit de rijen van elke andere tenant. Efficiëntie wordt bereikt door de opslag van gegevens voor veel tenants in één tabel. Elke tenant kan alleen de gegevensrijen zien.

RLS-filterpredicaten zijn functioneel gelijk aan het toevoegen van een WHERE component. Het predicaat kan net zo geavanceerd zijn als het dicteren van bedrijfsprocedures, of de component kan net zo eenvoudig zijn als WHERE TenantId = 42.

In formelere termen introduceert RLS predicaat gebaseerd toegangsbeheer. Het biedt een flexibele, gecentraliseerde evaluatie op basis van predicaat. Het predicaat kan worden gebaseerd op metagegevens of andere criteria die de beheerder naar wens bepaalt. Het predicaat wordt gebruikt als criterium om te bepalen of de gebruiker de juiste toegang heeft tot de gegevens op basis van gebruikerskenmerken. Toegangsbeheer op basis van labels kan worden geïmplementeerd met behulp van op predicaat gebaseerd toegangsbeheer.

Permissions

Voor het maken, wijzigen of verwijderen van beveiligingsbeleid is de ALTER ANY SECURITY POLICY machtiging vereist. Voor het maken of verwijderen van een beveiligingsbeleid is een machtiging voor het schema vereist ALTER .

Daarnaast zijn de volgende machtigingen vereist voor elk predicaat dat wordt toegevoegd:

  • SELECT en REFERENCES machtigingen voor de functie die wordt gebruikt als predicaat.

  • REFERENCES machtiging voor de doeltabel die is gebonden aan het beleid.

  • REFERENCES machtiging voor elke kolom uit de doeltabel die als argumenten wordt gebruikt.

Beveiligingsbeleid is van toepassing op alle gebruikers, inclusief dbo-gebruikers in de database. Dbo-gebruikers kunnen beveiligingsbeleid wijzigen of verwijderen, maar hun wijzigingen in beveiligingsbeleid kunnen worden gecontroleerd. Als gebruikers met hoge bevoegdheden, zoals sysadmin of db_owner, alle rijen moeten zien om problemen met gegevens op te lossen of te valideren, moet het beveiligingsbeleid worden geschreven om dat toe te staan.

Als er een beveiligingsbeleid wordt gemaakt met SCHEMABINDING = OFF, moeten gebruikers over de SELECT of EXECUTE-machtiging beschikken voor de predicaatfunctie en eventuele extra tabellen, weergaven of functies die binnen de predicaatfunctie worden gebruikt, om een query uit te voeren op de doeltabel. Als er een beveiligingsbeleid wordt gemaakt met SCHEMABINDING = ON (de standaardinstelling), worden deze machtigingscontroles overgeslagen wanneer gebruikers query's uitvoeren op de doeltabel.

Beste praktijken

  • Het wordt ten zeerste aanbevolen om een afzonderlijk schema te maken voor de RLS-objecten: predicaatfuncties en beveiligingsbeleid. Dit helpt bij het scheiden van de machtigingen die vereist zijn voor deze speciale objecten van de doeltabellen. Extra scheiding voor verschillende beleidsregels en predicaatfuncties is mogelijk nodig in multitenant-databases, maar niet als standaard voor elk geval.

  • De ALTER ANY SECURITY POLICY machtiging is bedoeld voor gebruikers met hoge bevoegdheden (zoals een beveiligingsbeleidsbeheerder). De beveiligingsbeleidsmanager vereist SELECT geen machtigingen voor de tabellen die ze beveiligen.

  • Vermijd typeconversies in predicaatfuncties om mogelijke runtimefouten te voorkomen.

  • Vermijd recursie in predicaatfuncties waar mogelijk om prestatievermindering te voorkomen. De queryoptimalisatie probeert directe recursies te detecteren, maar is niet gegarandeerd om indirecte recursies te vinden. Een indirecte recursie is de plaats waar een tweede functie de predicaatfunctie aanroept.

  • Vermijd het gebruik van overmatige tabeldeelnames in predicaatfuncties om de prestaties te maximaliseren.

Vermijd predicaatlogica die afhankelijk is van sessiespecifieke SET-opties: hoewel het onwaarschijnlijk is dat deze worden gebruikt in praktische toepassingen, kunnen predicaatfuncties waarvan de logica afhankelijk is van bepaalde sessiespecifieke SET opties informatie lekken als gebruikers willekeurige query's kunnen uitvoeren. Een predicaatfunctie die impliciet een tekenreeks converteert naar datum/tijd , kan bijvoorbeeld verschillende rijen filteren op basis van de SET DATEFORMAT optie voor de huidige sessie. Over het algemeen moeten predicaatfuncties voldoen aan de volgende regels:

  • Predicaatfuncties mogen tekenreeksen niet impliciet converteren naar datum, smalldatetime, datetime, datetime2 of datetimeoffset, of omgekeerd, omdat deze conversies worden beïnvloed door de opties SET DATEFORMAT (Transact-SQL) en SET LANGUAGE (Transact-SQL). Gebruik in plaats daarvan de CONVERT functie en geef expliciet de stijlparameter op.

  • Predicaatfuncties mogen niet afhankelijk zijn van de waarde van de eerste dag van de week, omdat deze waarde wordt beïnvloed door de optie SET DATEFIRST (Transact-SQL).

  • Predicaatfuncties mogen niet afhankelijk zijn van rekenkundige of aggregatie-expressies die een fout opleveren (zoals overloop of delen door nul), omdat dit gedrag wordt beïnvloed door de opties NULL, SET NUMERIC_ROUNDABORT (Transact-SQL) en SET ARITHABORT (Transact-SQL).

  • Predicaatfuncties mogen samengevoegde tekenreeksen niet vergelijken metNULL, omdat dit gedrag wordt beïnvloed door de optie SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).

Opmerking over beveiliging: side-channel-aanvallen

Kwaadwillende beveiligingsbeleidsmanager

Het is belangrijk te beseffen dat een kwaadwillende beveiligingsbeleidsmanager, met voldoende machtigingen om een beveiligingsbeleid te creëren boven op een gevoelige kolom en gemachtigd is om inline tabelwaardefuncties te creëren of te wijzigen, kan samenspannen met een andere gebruiker die selectiemachtigingen op een tabel heeft om gegevens te exfiltreren door kwaadaardige inline tabelwaardefuncties te creëren die zijn ontworpen om zij-kanaalaanvallen te gebruiken om gegevens af te leiden. Dergelijke aanvallen vereisen samenspanning (of overmatige machtigingen die zijn verleend aan een kwaadwillende gebruiker) en vereisen waarschijnlijk verschillende iteraties voor het wijzigen van het beleid (waarvoor toestemming is vereist om het predicaat te verwijderen om de schemabinding te verbreken), het wijzigen van de inline-tabelwaardefuncties en herhaaldelijk select-verklaringen in de doeltabel uit te voeren. U wordt aangeraden de machtigingen zo nodig te beperken en te controleren op verdachte activiteiten. Activiteiten zoals het voortdurend wijzigen van beleidsregels en inline-tabelwaardefuncties met betrekking tot beveiliging op rijniveau moeten worden gecontroleerd.

Zorgvuldig samengestelde query's

Het is mogelijk om informatielekken te veroorzaken door zorgvuldig gemaakte query's te gebruiken die fouten gebruiken om gegevens te exfiltreren. Laat een kwaadwillende gebruiker bijvoorbeeld SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe'; weten dat John Doe's salaris precies $ 100.000 is. Hoewel er een beveiligingspredicaat is ingesteld om te voorkomen dat een kwaadwillende gebruiker rechtstreeks een query uitvoert op het salaris van andere personen, kan de gebruiker bepalen wanneer de query een uitzondering voor delen door nul retourneert.

Compatibiliteit tussen functies

Over het algemeen werkt beveiliging op rijniveau zoals verwacht voor alle functies. Er zijn echter enkele uitzonderingen. In deze sectie worden verschillende notities en opmerkingen beschreven voor het gebruik van beveiliging op rijniveau met bepaalde andere functies van SQL Server.

  • DBCC SHOW_STATISTICS rapporteert statistieken over niet-gefilterde gegevens en kan informatie lekken die anders wordt beveiligd door een beveiligingsbeleid. Daarom is de toegang tot het weergeven van een statistiekenobject voor een tabel met een beveiligingsbeleid op rijniveau beperkt. De gebruiker moet eigenaar zijn van de tabel of de gebruiker moet lid zijn van de sysadmin vaste serverfunctie, de db_owner vaste databaserol of de db_ddladmin vaste databaserol.

  • Filestream: RLS is niet compatibel met Filestream.

  • PolyBase: RLS wordt ondersteund met externe tabellen in Azure Synapse en SQL Server 2019 CU7 of hogere versies.

  • Memory-geoptimaliseerde tabellen: De inline-tabelwaardefunctie die als beveiligingspredicaat wordt gebruikt voor een memory-geoptimaliseerde tabel, moet worden gedefinieerd met de WITH NATIVE_COMPILATION optie. Met deze optie worden taalfuncties die niet worden ondersteund door geheugen-geoptimaliseerde tabellen geblokkeerd, en wordt een passende foutmelding gegenereerd tijdens de creatie. Zie Beveiliging op rijniveau in Memory-Optimized Tabellen voor meer informatie.

  • Geïndexeerde weergaven: Over het algemeen kunnen beveiligingsbeleidsregels worden gemaakt boven op weergaven en weergaven kunnen worden gemaakt boven op tabellen die afhankelijk zijn van beveiligingsbeleid. Geïndexeerde weergaven kunnen echter niet worden gemaakt voor tabellen met een beveiligingsbeleid, omdat rijzoekacties via de index het beleid zouden omzeilen.

  • Change Data Capture: Change Data Capture (CDC) kan volledige rijen lekken die moeten worden gefilterd voor leden van db_owner of voor gebruikers die lid zijn van de opgegeven 'gating'-rol wanneer CDC is ingeschakeld voor een tabel. U kunt deze functie expliciet instellen op NULL om alle gebruikers toegang te geven tot de wijzigingsgegevens. In feite kunnen db_owner en leden van deze gatingsrol alle gegevenswijzigingen in een tabel zien, zelfs als er een beveiligingsbeleid voor de tabel is.

  • Wijzigingen bijhouden: Wijzigingen bijhouden kan de primaire sleutel van rijen lekken die moeten worden gefilterd voor gebruikers met zowel SELECT machtigingen als VIEW CHANGE TRACKING machtigingen. Werkelijke gegevenswaarden worden niet gelekt; alleen het feit dat kolom A is bijgewerkt/ingevoegd/verwijderd voor de rij met een bepaalde primaire sleutel. Dit is problematisch als de primaire sleutel een vertrouwelijk element bevat, zoals een burgerservicenummer. In de praktijk is dit CHANGETABLE echter bijna altijd gekoppeld aan de oorspronkelijke tabel om de meest recente gegevens op te halen.

  • Full-Text zoeken: Er wordt een prestatiedaling verwacht voor query's die gebruikmaken van de volgende Full-text zoeken- en Semantisch zoeken-functies, vanwege een extra join die is geïntroduceerd om beveiliging op rijniveau toe te passen en te voorkomen dat de primaire sleutels van rijen die moeten worden gefilterd, uitlekken: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.

  • Columnstore-indexen: RLS is compatibel met zowel geclusterde als niet-geclusterde columnstore-indexen. Omdat beveiliging op rijniveau echter een functie toepast, is het mogelijk dat de optimizer het queryplan kan wijzigen, zodat er geen batchmodus wordt gebruikt.

  • Gepartitioneerde weergaven: Blokpredicaten kunnen niet worden gedefinieerd voor gepartitioneerde weergaven en gepartitioneerde weergaven kunnen niet worden gemaakt boven op tabellen die gebruikmaken van blokpredicaten. Filterpredicaten zijn compatibel met gepartitioneerde weergaven.

  • Tijdelijke tabellen: Tijdelijke tabellen zijn compatibel met RLS. Beveiligingspredicaten in de huidige tabel worden echter niet automatisch gerepliceerd naar de geschiedenistabel. Als u een beveiligingsbeleid wilt toepassen op zowel de huidige als de geschiedenistabellen, moet u afzonderlijk een beveiligingspredicaat toevoegen aan elke tabel.

Andere beperkingen:

  • Microsoft Fabric en Azure Synapse Analytics ondersteunen alleen filterpredicaten. Blokpredicaten worden momenteel niet ondersteund in Microsoft Fabric en Azure Synapse Analytics.

Examples

A. Scenario voor gebruikers die zich verifiëren bij de database

In dit voorbeeld worden drie gebruikers gemaakt en wordt een tabel met zes rijen gemaakt en gevuld. Vervolgens wordt een inline-tabelwaardefunctie en een beveiligingsbeleid voor de tabel gemaakt. In het voorbeeld wordt vervolgens getoond hoe select-instructies voor de verschillende gebruikers worden gefilterd.

Maak drie gebruikersaccounts die verschillende toegangsmogelijkheden demonstreren.

CREATE USER Manager WITHOUT LOGIN;
CREATE USER SalesRep1 WITHOUT LOGIN;
CREATE USER SalesRep2 WITHOUT LOGIN;
GO

Maak een tabel voor het opslaan van gegevens.

CREATE SCHEMA Sales
GO
CREATE TABLE Sales.Orders
    (
    OrderID int,
    SalesRep nvarchar(50),
    Product nvarchar(50),
    Quantity smallint
    );

Vul de tabel met zes rijen met gegevens, met drie orders voor elke vertegenwoordiger.

INSERT INTO Sales.Orders  VALUES (1, 'SalesRep1', 'Valve', 5);
INSERT INTO Sales.Orders  VALUES (2, 'SalesRep1', 'Wheel', 2);
INSERT INTO Sales.Orders  VALUES (3, 'SalesRep1', 'Valve', 4);
INSERT INTO Sales.Orders  VALUES (4, 'SalesRep2', 'Bracket', 2);
INSERT INTO Sales.Orders  VALUES (5, 'SalesRep2', 'Wheel', 5);
INSERT INTO Sales.Orders  VALUES (6, 'SalesRep2', 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sales.Orders;

Verleen leestoegang tot de tabel aan elke gebruiker.

GRANT SELECT ON Sales.Orders TO Manager;
GRANT SELECT ON Sales.Orders TO SalesRep1;
GRANT SELECT ON Sales.Orders TO SalesRep2;
GO

Maak een nieuw schema en een inline-tabelwaardefunctie. De functie retourneert 1 wanneer een rij in de SalesRep kolom hetzelfde is als de gebruiker die de query uitvoert (@SalesRep = USER_NAME()) of als de gebruiker die de query uitvoert de managergebruiker is (USER_NAME() = 'Manager'). Dit voorbeeld van een door de gebruiker gedefinieerde, tabelwaardefunctie is handig als filter voor het beveiligingsbeleid dat in de volgende stap is gemaakt.

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS tvf_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
GO

Maak een beveiligingsbeleid voor het toevoegen van de functie als filterpredicaat. De STATE moet worden ingesteld op ON om het beleid in te schakelen.

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)
ON Sales.Orders
WITH (STATE = ON);
GO

Sta SELECT machtigingen toe voor de tvf_securitypredicate functie:

GRANT SELECT ON Security.tvf_securitypredicate TO Manager;
GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep1;
GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep2;

Test nu het filterpredicaat door als elke gebruiker uit de Sales.Orders tabel te selecteren.

EXECUTE AS USER = 'SalesRep1';
SELECT * FROM Sales.Orders;
REVERT;

EXECUTE AS USER = 'SalesRep2';
SELECT * FROM Sales.Orders;
REVERT;

EXECUTE AS USER = 'Manager';
SELECT * FROM Sales.Orders;
REVERT;

De manager moet alle zes rijen zien. De Sales1 en Sales2 gebruikers mogen alleen hun eigen verkoop zien.

Wijzig het beveiligingsbeleid om het beleid uit te schakelen.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Nu kunnen Sales1 en Sales2 gebruikers alle zes rijen zien.

Maak verbinding met de SQL-database om resources op te schonen uit deze voorbeeldoefening:

DROP USER SalesRep1;
DROP USER SalesRep2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter;
DROP TABLE Sales.Orders;
DROP FUNCTION Security.tvf_securitypredicate;
DROP SCHEMA Security;
DROP SCHEMA Sales;

B. Scenario's voor het gebruik van beveiliging op rijniveau in een externe Azure Synapse-tabel

In dit korte voorbeeld worden drie gebruikers en een externe tabel met zes rijen gemaakt. Vervolgens wordt een inline-tabelwaardefunctie en een beveiligingsbeleid voor de externe tabel gemaakt. In het voorbeeld wordt getoond hoe select-instructies worden gefilterd voor de verschillende gebruikers.

Prerequisites

  1. U moet een toegewezen SQL-pool hebben. Zie Een toegewezen SQL-pool maken
  2. De server die als host fungeert voor uw toegewezen SQL-pool moet zijn geregistreerd bij Microsoft Entra ID (voorheen Azure Active Directory) en u moet een Azure-opslagaccount met Storage Blog Data Contributor machtigingen hebben. Volg de stappen voor het gebruik van service-eindpunten en regels voor virtuele netwerken voor servers in Azure SQL Database.
  3. Maak een bestandssysteem voor uw Azure Storage-account. Gebruik Azure Storage Explorer om uw opslagaccount weer te geven. Klik met de rechtermuisknop op containers en selecteer Bestandssysteem maken.

Zodra u aan de vereisten voldoet, maakt u drie gebruikersaccounts die verschillende toegangsmogelijkheden demonstreren.

--run in master
CREATE LOGIN Manager WITH PASSWORD = '<user_password>'
GO
CREATE LOGIN Sales1 WITH PASSWORD = '<user_password>'
GO
CREATE LOGIN Sales2 WITH PASSWORD = '<user_password>'
GO

--run in both the master database and in your dedicated SQL pool database
CREATE USER Manager FOR LOGIN Manager;
CREATE USER Sales1  FOR LOGIN Sales1;
CREATE USER Sales2  FOR LOGIN Sales2 ;

Maak een tabel voor het opslaan van gegevens.

CREATE TABLE Sales
    (
    OrderID int,
    SalesRep sysname,
    Product varchar(10),
    Qty int
    );

Vul de tabel met zes rijen met gegevens, met drie orders voor elke vertegenwoordiger.

INSERT INTO Sales VALUES (1, 'Sales1', 'Valve', 5);
INSERT INTO Sales VALUES (2, 'Sales1', 'Wheel', 2);
INSERT INTO Sales VALUES (3, 'Sales1', 'Valve', 4);
INSERT INTO Sales VALUES (4, 'Sales2', 'Bracket', 2);
INSERT INTO Sales VALUES (5, 'Sales2', 'Wheel', 5);
INSERT INTO Sales VALUES (6, 'Sales2', 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sales;

Maak een externe Azure Synapse-tabel op basis van de Sales tabel die u hebt gemaakt.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<user_password>';

CREATE DATABASE SCOPED CREDENTIAL msi_cred WITH IDENTITY = 'Managed Service Identity';

CREATE EXTERNAL DATA SOURCE ext_datasource_with_abfss WITH (TYPE = hadoop, LOCATION = 'abfss://<file_system_name@storage_account>.dfs.core.windows.net', CREDENTIAL = msi_cred);

CREATE EXTERNAL FILE FORMAT MSIFormat  WITH (FORMAT_TYPE=DELIMITEDTEXT);

CREATE EXTERNAL TABLE Sales_ext WITH (LOCATION='<your_table_name>', DATA_SOURCE=ext_datasource_with_abfss, FILE_FORMAT=MSIFormat, REJECT_TYPE=Percentage, REJECT_SAMPLE_VALUE=100, REJECT_VALUE=100)
AS SELECT * FROM sales;

Verleen SELECT aan de drie gebruikers voor de externe tabel Sales_ext die u hebt gemaakt.

GRANT SELECT ON Sales_ext TO Sales1;
GRANT SELECT ON Sales_ext TO Sales2;
GRANT SELECT ON Sales_ext TO Manager;

Maak een nieuw schema en een inline-tabelwaardefunctie. Mogelijk hebt u dit in voorbeeld A voltooid. De functie retourneert 1 wanneer een rij in de SalesRep kolom hetzelfde is als de gebruiker die de query uitvoert (@SalesRep = USER_NAME()) of als de gebruiker die de query uitvoert de Manager gebruiker (USER_NAME() = 'Manager').

CREATE SCHEMA Security;
GO

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

Maak een beveiligingsbeleid voor uw externe tabel met behulp van de inline-tabelwaardefunctie als filterpredicaat. De STATE moet worden ingesteld op ON om het beleid in te schakelen.

CREATE SECURITY POLICY SalesFilter_ext
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales_ext
WITH (STATE = ON);

Test nu het filterpredicaat door een selectie uit de Sales_ext externe tabel te maken. Meld u aan als elke gebruiker, Sales1en Sales2Manager. Voer de volgende opdracht uit als elke gebruiker.

SELECT * FROM Sales_ext;

Het Manager moet alle zes rijen kunnen zien. De Sales1 en Sales2 gebruikers mogen alleen hun verkoop zien.

Wijzig het beveiligingsbeleid om het beleid uit te schakelen.

ALTER SECURITY POLICY SalesFilter_ext
WITH (STATE = OFF);

Nu kunnen de Sales1 en Sales2 gebruikers alle zes rijen zien.

Maak verbinding met de Azure Synapse-database om resources op te schonen uit deze voorbeeldoefening:

DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter_ext;
DROP TABLE Sales;
DROP EXTERNAL TABLE Sales_ext;
DROP EXTERNAL DATA SOURCE ext_datasource_with_abfss ;
DROP EXTERNAL FILE FORMAT MSIFormat;
DROP DATABASE SCOPED CREDENTIAL msi_cred;
DROP MASTER KEY;

Maak verbinding met de database van master de logische server om resources op te schonen:

DROP LOGIN Sales1;
DROP LOGIN Sales2;
DROP LOGIN Manager;

C. Scenario voor gebruikers die verbinding maken met de database via een toepassing in de middelste laag

Note

In dit voorbeeld wordt de functionaliteit voor blokpredicaten momenteel niet ondersteund voor Microsoft Fabric en Azure Synapse, waardoor het invoegen van rijen voor de verkeerde gebruikers-id niet wordt geblokkeerd.

In dit voorbeeld ziet u hoe een toepassing in de middelste laag verbindingsfilters kan implementeren, waarbij toepassingsgebruikers (of tenants) dezelfde SQL Server-gebruiker (de toepassing) delen. De toepassing stelt de huidige gebruikers-id van de toepassing in SESSION_CONTEXT na het maken van verbinding met de database en vervolgens filtert beveiligingsbeleid op transparante wijze rijen die niet zichtbaar moeten zijn voor deze id, en blokkeert ook dat de gebruiker rijen voor de verkeerde gebruikers-id invoegt. Er zijn geen andere app-wijzigingen nodig.

Maak een tabel voor het opslaan van gegevens.

CREATE TABLE Sales (
    OrderId int,
    AppUserId int,
    Product varchar(10),
    Qty int
);

Vul de tabel met zes rijen met gegevens, met drie orders voor elke toepassingsgebruiker.

INSERT Sales VALUES
    (1, 1, 'Valve', 5),
    (2, 1, 'Wheel', 2),
    (3, 1, 'Valve', 4),
    (4, 2, 'Bracket', 2),
    (5, 2, 'Wheel', 5),
    (6, 2, 'Seat', 5);

Maak een gebruiker met beperkte bevoegdheden die door de toepassing wordt gebruikt om verbinding te maken.

-- Without login only for demo
CREATE USER AppUser WITHOUT LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO AppUser;

-- Never allow updates on this column
DENY UPDATE ON Sales(AppUserId) TO AppUser;

Maak een nieuw schema en een predicaatfunctie, waarmee de gebruikers-id van de toepassing wordt gebruikt die is opgeslagen SESSION_CONTEXT() om rijen te filteren.

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.fn_securitypredicate(@AppUserId int)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
    WHERE
        DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser')
        AND CAST(SESSION_CONTEXT(N'UserId') AS int) = @AppUserId;
GO

Maak een beveiligingsbeleid waarmee deze functie wordt toegevoegd als filterpredicaat en een blokpredicaat op Sales. Het blokpredicaat heeft alleen nodig AFTER INSERT, omdat BEFORE UPDATE en BEFORE DELETE al zijn gefilterd, en AFTER UPDATE is niet nodig omdat de AppUserId kolom niet kan worden bijgewerkt naar andere waarden, vanwege de eerder ingestelde kolommachtigingen.

CREATE SECURITY POLICY Security.SalesFilter
    ADD FILTER PREDICATE Security.fn_securitypredicate(AppUserId)
        ON dbo.Sales,
    ADD BLOCK PREDICATE Security.fn_securitypredicate(AppUserId)
        ON dbo.Sales AFTER INSERT
    WITH (STATE = ON);

Nu kunnen we het filteren van de verbinding simuleren door een selectie uit de Sales tabel te maken na het instellen van verschillende gebruikers-id's in SESSION_CONTEXT(). In de praktijk is de toepassing verantwoordelijk voor het instellen van de huidige gebruikers-id SESSION_CONTEXT() na het openen van een verbinding. Als de @read_only parameter wordt ingesteld op 1, wordt voorkomen dat de waarde opnieuw wordt gewijzigd totdat de verbinding wordt gesloten (teruggestuurd naar de verbindingspool).

EXECUTE AS USER = 'AppUser';
EXEC sp_set_session_context @key=N'UserId', @value=1;
SELECT * FROM Sales;
GO

/* Note: @read_only prevents the value from changing again until the connection is closed (returned to the connection pool)*/
EXEC sp_set_session_context @key=N'UserId', @value=2, @read_only=1;

SELECT * FROM Sales;
GO

INSERT INTO Sales VALUES (7, 1, 'Seat', 12); -- error: blocked from inserting row for the wrong user ID
GO

REVERT;
GO

De databasebronnen opschonen.

DROP USER AppUser;

DROP SECURITY POLICY Security.SalesFilter;
DROP TABLE Sales;
DROP FUNCTION Security.fn_securitypredicate;
DROP SCHEMA Security;

D. Scenario voor het gebruik van een opzoektabel voor het beveiligingspredicaat

In dit voorbeeld wordt een opzoektabel gebruikt voor de koppeling tussen de gebruikers-id en de waarde die wordt gefilterd, in plaats van de gebruikers-id in de feitentabel op te geven. Er worden drie gebruikers aangemaakt, een feitentabel met zes rijen aangemaakt en gevuld, en een opzoektabel met twee rijen aangemaakt. Vervolgens wordt er een inline tabelgedefinieerde functie gemaakt die de feitentabel aan de opzoektabel koppelt voor het verkrijgen van de gebruikers-id, en er wordt een beveiligingsbeleid voor de tabel opgesteld. In het voorbeeld wordt vervolgens getoond hoe select-instructies voor de verschillende gebruikers worden gefilterd.

Maak drie gebruikersaccounts die verschillende toegangsmogelijkheden demonstreren.

CREATE USER Manager WITHOUT LOGIN;
CREATE USER Sales1 WITHOUT LOGIN;
CREATE USER Sales2 WITHOUT LOGIN;

Maak een Sample schema en een feitentabel om Sample.Salesgegevens te bewaren.

CREATE SCHEMA Sample;
GO
CREATE TABLE Sample.Sales
    (
    OrderID int,
    Product varchar(10),
    Qty int
    );

Vul Sample.Sales zes rijen met gegevens in.

INSERT INTO Sample.Sales VALUES (1, 'Valve', 5);
INSERT INTO Sample.Sales VALUES (2, 'Wheel', 2);
INSERT INTO Sample.Sales VALUES (3, 'Valve', 4);
INSERT INTO Sample.Sales VALUES (4, 'Bracket', 2);
INSERT INTO Sample.Sales VALUES (5, 'Wheel', 5);
INSERT INTO Sample.Sales VALUES (6, 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sample.Sales;

Maak een tabel voor het opslaan van de opzoekgegevens, in dit geval een relatie tussen Salesrep en Product.

CREATE TABLE Sample.Lk_Salesman_Product
  ( Salesrep sysname,
    Product varchar(10)
  ) ;

Vul de opzoektabel met voorbeeldgegevens en koppel één Product aan elke verkoopvertegenwoordiger.

INSERT INTO Sample.Lk_Salesman_Product VALUES ('Sales1', 'Valve');
INSERT INTO Sample.Lk_Salesman_Product VALUES ('Sales2', 'Wheel');
-- View the 2 rows in the table
SELECT * FROM Sample.Lk_Salesman_Product;

Leestoegang verlenen aan elke gebruiker in de feitentabel.

GRANT SELECT ON Sample.Sales TO Manager;
GRANT SELECT ON Sample.Sales TO Sales1;
GRANT SELECT ON Sample.Sales TO Sales2;

Maak een nieuw schema en een inline-tabelwaardefunctie. De functie retourneert 1 wanneer een gebruiker een query uitvoert op de feitentabel Sample.Sales en de SalesRep kolom van de tabel Lk_Salesman_Product hetzelfde is als de gebruiker die de query uitvoert (@SalesRep = USER_NAME()) wanneer deze is toegevoegd aan de feitentabel in de Product kolom, of als de gebruiker die de query uitvoert de Manager gebruiker is (USER_NAME() = 'Manager').

CREATE SCHEMA Security ;
GO
CREATE FUNCTION Security.fn_securitypredicate
         (@Product AS varchar(10))
RETURNS TABLE
WITH SCHEMABINDING
AS
           RETURN ( SELECT 1 as Result
                     FROM Sample.Sales f
            INNER JOIN Sample.Lk_Salesman_Product s
                     ON s.Product = f.Product
            WHERE ( f.product = @Product
                    AND s.SalesRep = USER_NAME() )
                 OR USER_NAME() = 'Manager'
                   ) ;

Maak een beveiligingsbeleid voor het toevoegen van de functie als filterpredicaat. De STATE moet worden ingesteld op ON om het beleid in te schakelen.

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(Product)
ON Sample.Sales
WITH (STATE = ON) ;

Sta SELECT machtigingen toe voor de fn_securitypredicate functie:

GRANT SELECT ON Security.fn_securitypredicate TO Manager;
GRANT SELECT ON Security.fn_securitypredicate TO Sales1;
GRANT SELECT ON Security.fn_securitypredicate TO Sales2;

Test nu het filterpredicaat door als elke gebruiker uit de Sample.Sales tabel te selecteren.

EXECUTE AS USER = 'Sales1';
SELECT * FROM Sample.Sales;
-- This will return just the rows for Product 'Valve' (as specified for 'Sales1' in the Lk_Salesman_Product table above)
REVERT;

EXECUTE AS USER = 'Sales2';
SELECT * FROM Sample.Sales;
-- This will return just the rows for Product 'Wheel' (as specified for 'Sales2' in the Lk_Salesman_Product table above)
REVERT;

EXECUTE AS USER = 'Manager';
SELECT * FROM Sample.Sales;
-- This will return all rows with no restrictions
REVERT;

Het Manager moet alle zes rijen kunnen zien. De Sales1 en Sales2 gebruikers mogen alleen hun eigen verkoop zien.

Wijzig het beveiligingsbeleid om het beleid uit te schakelen.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Nu kunnen Sales1 en Sales2 gebruikers alle zes rijen zien.

Maak verbinding met de SQL-database om resources op te schonen uit deze voorbeeldoefening:

DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter;
DROP FUNCTION Security.fn_securitypredicate;
DROP TABLE Sample.Sales;
DROP TABLE Sample.Lk_Salesman_Product;
DROP SCHEMA Security;
DROP SCHEMA Sample;

E. Beveiligingsscenario op rijniveau in Microsoft Fabric

We kunnen het eindpunt voor beveiliging op rijniveau en SQL-analyse demonstreren in Microsoft Fabric.

In het volgende voorbeeld worden voorbeeldtabellen gemaakt die werken met Warehouse in Microsoft Fabric, maar in sql Analytics-eindpunten worden bestaande tabellen gebruikt. In het SQL-analyse-eindpunt kunt u het niet gebruiken CREATE TABLE, maar wel gebruiken CREATE SCHEMA, CREATE FUNCTIONen CREATE SECURITY POLICY.

In dit voorbeeld maakt u eerst een schema sales, een tabel sales.Orders.

CREATE SCHEMA sales;
GO

-- Create a table to store sales data
CREATE TABLE sales.Orders (
    SaleID INT,
    SalesRep VARCHAR(100),
    ProductName VARCHAR(50),
    SaleAmount DECIMAL(10, 2),
    SaleDate DATE
);

-- Insert sample data
INSERT INTO sales.Orders (SaleID, SalesRep, ProductName, SaleAmount, SaleDate)
VALUES
    (1, 'Sales1@contoso.com', 'Smartphone', 500.00, '2023-08-01'),
    (2, 'Sales2@contoso.com', 'Laptop', 1000.00, '2023-08-02'),
    (3, 'Sales1@contoso.com', 'Headphones', 120.00, '2023-08-03'),
    (4, 'Sales2@contoso.com', 'Tablet', 800.00, '2023-08-04'),
    (5, 'Sales1@contoso.com', 'Smartwatch', 300.00, '2023-08-05'),
    (6, 'Sales2@contoso.com', 'Gaming Console', 400.00, '2023-08-06'),
    (7, 'Sales1@contoso.com', 'TV', 700.00, '2023-08-07'),
    (8, 'Sales2@contoso.com', 'Wireless Earbuds', 150.00, '2023-08-08'),
    (9, 'Sales1@contoso.com', 'Fitness Tracker', 80.00, '2023-08-09'),
    (10, 'Sales2@contoso.com', 'Camera', 600.00, '2023-08-10');

Maak een Security schema, een functie Security.tvf_securitypredicateen een beveiligingsbeleid SalesFilter.

-- Creating schema for Security
CREATE SCHEMA Security;
GO

-- Creating a function for the SalesRep evaluation
CREATE FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS tvf_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'manager@contoso.com';
GO

-- Using the function to create a Security Policy
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)
ON sales.Orders
WITH (STATE = ON);
GO

Nadat het beveiligingsbeleid is toegepast en de functie is gemaakt, kunnen de gebruikers Sales1@contoso.com alleen Sales2@contoso.com hun eigen gegevens in de sales.Orders tabel zien, waarbij de kolom SalesRep gelijk is aan hun eigen gebruikersnaam die wordt geretourneerd door de ingebouwde functie USER_NAME. De Fabric-gebruiker manager@contoso.com kan alle gegevens in de sales.Orders tabel zien.

Verwante inhoud