Sorszintű biztonság

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseFelügyelt Azure SQL-példányAzure Synapse AnalyticsSQL Analytics-végpont a Microsoft FabricbenRaktár a Microsoft FabricbenSQL-adatbázis a Microsoft Fabricben

A sorszintű biztonság (RLS) lehetővé teszi a csoporttagság vagy a végrehajtási környezet használatát az adatbázistábla soraihoz való hozzáférés szabályozásához.

A sorszintű biztonság leegyszerűsíti a biztonság tervezését és kódolását az alkalmazásban. Az RLS segít az adatsor-hozzáférés korlátozásainak megvalósításában. Gondoskodhat például arról, hogy a dolgozók csak azokat az adatsorokat érjék el, amelyek a részlegükhöz kapcsolódnak. Egy másik példa az ügyfelek adathozzáférésének korlátozása csak a vállalatuk számára releváns adatokra.

A hozzáférés-korlátozási logika az adatbázis szintjén található, nem pedig egy másik alkalmazásréteg adataitól. Az adatbázisrendszer minden alkalommal alkalmazza a hozzáférési korlátozásokat, amikor az adathozzáférést bármilyen szintről megkísérlik. Ez megbízhatóbbá és robusztusabbá teszi a biztonsági rendszert a biztonsági rendszer felületének csökkentésével.

Valósítsa meg az RLS-t a CREATE SECURITY POLICY Transact-SQL utasítással, valamint predikátumokkal, amelyeket in-line táblaértékű függvényekként hoztak létre.

A sorszintű biztonságot először az SQL Server 2016-ban vezették be (13.x).

Note

Ez a cikk az SQL Server és az Azure SQL-platformokra összpontosít. A Microsoft Fabric esetében lásd a Sorszintű biztonság a Microsoft Fabricben című témakört.

Description

A sorszintű biztonság (RLS) kétféle biztonsági predikátumot támogat:

• A szűrési predikátumok csendesen szűrik az olvasási műveletekhez elérhető sorokat (SELECTés UPDATEDELETE).

• A blokk predikátumok kifejezetten blokkolják az írási műveleteket (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, ), BEFORE DELETEamelyek megsértik a predikátumot.

A tábla sorszintű adataihoz való hozzáférést egy beágyazott táblaértékű függvényként definiált biztonsági predikátum korlátozza. A függvényt ezután egy biztonsági szabályzat hívja meg és érvényesíti. Szűrési predikátumok esetén az alkalmazás nem tud az eredményhalmazból szűrt sorokról. Ha az összes sor szűrve van, akkor a rendszer nullhalmazt ad vissza. Blokk predikátumok esetén a predikátumot megsértő műveletek hiba miatt meghiúsulnak.

A szűrő predikátumok az alaptáblából származó adatok beolvasása során kerülnek alkalmazásra. Ezek az összes lekérési műveletre hatással vannak: SELECT, DELETEés UPDATE. A felhasználók nem jelölhetnek ki és nem törölhetnek szűrt sorokat. A felhasználó nem tudja frissíteni a szűrt sorokat. A sorokat azonban úgy is frissítheti, hogy azokat később szűrni lehessen. A blokk predikátumok minden írási műveletet érintenek.

• AFTER INSERT és AFTER UPDATE predikátumok megakadályozhatják, hogy a felhasználók olyan értékekre frissítsenek sorokat, amelyek megsértik a predikátumot.

• BEFORE UPDATE predikátumok megakadályozhatják, hogy a felhasználók olyan sorokat frissítsenek, amelyek jelenleg megsértik a predikátumot.

• BEFORE DELETE predikátumok blokkolhatják a törlési műveleteket.

A szűrési és blokk-predikátumok és a biztonsági szabályzatok viselkedése a következő:

• Meghatározhat egy predikátumfüggvényt, amely egy másik táblához csatlakozik, és/vagy meghív egy függvényt. Ha a biztonsági szabályzat SCHEMABINDING = ON (alapértelmezett) használatával van létrehozva, akkor az illesztés vagy a függvény elérhető marad a lekérdezésből és a várt módon működik, anélkül, hogy további engedélyellenőrzésekre lenne szükség. Ha a biztonsági szabályzatot ezzel SCHEMABINDING = OFFhozza létre, akkor a felhasználóknak engedélyekre lesz szükségük SELECT ezen további táblákhoz és függvényekhez a céltábla lekérdezéséhez. Ha a predikátum függvény egy CLR skaláris értékű függvényt hív meg, az EXECUTE engedélyre is szükség van.

• Lekérdezést megadhatsz egy olyan táblára, amely biztonsági predikátummal rendelkezik, de le van tiltva. A szűrt vagy letiltott sorokat nem érinti.

• Ha egy dbo felhasználó, a db_owner szerepkör tagja vagy a tábla tulajdonosa lekérdez egy olyan táblát, amely rendelkezik definiált és engedélyezett biztonsági szabályzattal, a rendszer a biztonsági szabályzatban meghatározottak szerint szűri vagy letiltja a sorokat.

• A séma által kötött biztonsági házirend által kötött tábla sémájának módosítására tett kísérletek hibát eredményeznek. A predikátum által nem hivatkozott oszlopok azonban módosíthatók.

• Ha olyan táblához próbál predikátumot hozzáadni, amely már rendelkezik a megadott művelethez definiálttal, hibát eredményez. Ez akkor fordul elő, ha a predikátum engedélyezve van vagy sem.

• Ha egy sémakötött biztonsági házirend tábláján predikátumként használt függvényt próbál módosítani, az hibát eredményez.

• Több olyan aktív biztonsági szabályzat definiálása, amelyek nem átfedő predikátumokat tartalmaznak, sikeresek.

A szűrési predikátumok viselkedése a következő:

• Adjon meg egy olyan biztonsági szabályzatot, amely szűri a tábla sorait. Az alkalmazás nem ismeri azokat a sorokat, amelyek szűrve vannak a SELECT, UPDATE és DELETE műveletekhez. Beleértve azokat a helyzeteket is, amikor az összes sor ki van szűrve. Az alkalmazás akkor is INSERT sorokat állíthat be, ha bármely más művelet során szűrni fogja őket.

A blokk predikátumok viselkedése a következő:

• A UPDATE blokkok predikátumai külön műveletekké vannak felosztva a BEFORE és AFTER számára. Nem akadályozhatja meg például, hogy a felhasználók a jelenleginél magasabb értékre frissítsenek egy sort. Ha ilyen típusú logikára van szükség, akkor a TÖRÖLT és az INSERTED köztes táblákkal együtt kell használnia az eseményindítókat a régi és az új értékek együttes hivatkozásához.

• Az optimalizáló nem ellenőrzi a AFTER UPDATE blokk predikátumot, ha a predikátum által használt oszlopokon nem történt változás. Például: Alice nem módosíthatja a 100 000-nél nagyobb fizetést. Alice módosíthatja egy olyan alkalmazott címét, akinek a fizetése már meghaladja a 100 000-et, ha a predikátumban hivatkozott oszlopok nem változtak.

• Nem történt módosítás a tömeges API-kban, beleértve a BULK INSERT. Ez azt jelenti, hogy a blokk predikátumok ugyanúgy vonatkoznak AFTER INSERT a tömeges beszúrási műveletekre, mint a normál beszúrási műveletekre.

Használati esetek

Az alábbi tervezési példák a sorszintű biztonság (RLS) használatára szolgálnak:

• A kórházak olyan biztonsági szabályzatot hozhatnak létre, amely lehetővé teszi az ápolók számára, hogy csak a betegeik adatsorait tekintsék meg.

• A bank létrehozhat egy szabályzatot a pénzügyi adatsorokhoz való hozzáférés korlátozására egy alkalmazott üzleti részlege vagy a vállalatban betöltött szerepköre alapján.

• A több-bérlős alkalmazások létrehozhatnak egy szabályzatot, amely kényszeríti az egyes bérlők adatsorainak logikai elkülönítését minden más bérlő soraitól. A hatékonyságot számos bérlő adatainak egyetlen táblában való tárolásával érheti el. Minden bérlő csak az adatsorait látja.

Az RLS-szűrő predikátumai funkcionálisan egyenértékűek egy WHERE záradék hozzáfűzésével. A predikátum lehet olyan kifinomult, mint az üzleti gyakorlat diktálja, vagy a záradék lehet olyan egyszerű, mint WHERE TenantId = 42.

Formálisabb értelemben az RLS predikátumalapú hozzáférés-vezérlést vezet be. Rugalmas, központosított, predikátumalapú értékeléssel rendelkezik. A predikátum metaadatokon vagy a rendszergazda által meghatározott egyéb feltételeken alapulhat. A predikátumot a rendszer feltételként használja annak meghatározására, hogy a felhasználó rendelkezik-e a felhasználói attribútumok alapján megfelelő hozzáféréssel az adatokhoz. A címkealapú hozzáférés-vezérlés predikátumalapú hozzáférés-vezérléssel implementálható.

Permissions

A biztonsági szabályzatok létrehozásához, módosításához vagy elvetéséhez engedély ALTER ANY SECURITY POLICY szükséges. A biztonsági szabályzat létrehozásához vagy elvetéséhez engedélyre van szükség ALTER a sémán.

Emellett a következő engedélyekre van szükség minden hozzáadott predikátumhoz:

• SELECT és REFERENCES a predikátumként használt függvény engedélyeit.

• REFERENCES a szabályzathoz kötött céltáblára vonatkozó engedély.

• REFERENCES az argumentumként használt céltábla minden oszlopára vonatkozó engedély.

A biztonsági szabályzatok az összes felhasználóra vonatkoznak, beleértve az adatbázis dbo-felhasználóit is. A Dbo-felhasználók módosíthatják vagy elvethetik a biztonsági szabályzatokat, de a biztonsági szabályzatok módosításait naplózhatják. Ha a magas jogosultságú felhasználóknak, például a sysadminnak vagy a db_owner, látniuk kell az összes sort az adatok hibaelhárításához vagy ellenőrzéséhez, a biztonsági szabályzatot meg kell írni, hogy engedélyezve legyen.

Ha biztonsági szabályzatot hoz létre SCHEMABINDING = OFF, akkor a céltábla lekérdezéséhez a felhasználóknak rendelkezniük kell a SELECT vagy EXECUTE engedéllyel a predikátumfüggvényre, valamint bármely további táblára, nézetre vagy függvényre, amelyet a predikátumfüggvény használ. Ha biztonsági szabályzatot hoz létre SCHEMABINDING = ON (az alapértelmezett), akkor ezeket az engedélyellenőrzéseket a rendszer megkerüli, amikor a felhasználók lekérdezik a céltáblát.

Ajánlott eljárások

• Erősen ajánlott külön sémát létrehozni az RLS-objektumokhoz: predikátumfüggvényeket és biztonsági szabályzatokat. Ez segít elkülöníteni az ezeken a speciális objektumokon szükséges engedélyeket a céltábláktól. A különböző szabályzatok és predikátumfüggvények további elkülönítésére lehet szükség a több-bérlős adatbázisokban, de nem minden esetben.

• Az ALTER ANY SECURITY POLICY engedély a kiemelt jogosultságokkal rendelkező felhasználók (például biztonsági házirend-kezelők) számára készült. A biztonsági házirend-kezelőnek nincs szüksége engedélyre SELECT az általuk védett táblákon.

• Kerülje a típusátalakításokat a predikátumfüggvényekben a lehetséges futásidejű hibák elkerülése érdekében.

• A teljesítménycsökkenés elkerülése érdekében lehetőleg kerülje a predikátumfüggvények rekurzióját. A lekérdezésoptimalizáló megpróbálja észlelni a közvetlen rekurziókat, de nem garantált, hogy közvetett rekurziókat talál. A közvetett rekurzió az, ahol egy második függvény meghívja a predikátumfüggvényt.

• A teljesítmény maximalizálása érdekében kerülje a predikátumfüggvények túlzott táblázatbeillesztését.

Kerülje a munkamenet-specifikus SET-beállításoktól függő predikátumlogikát: Bár gyakorlati alkalmazásokban nem valószínű, hogy a predikátum olyan függvényeket használ, amelyek logikája bizonyos munkamenet-specifikus SET beállításoktól függ, információkat szivárogtathat ki, ha a felhasználók tetszőleges lekérdezéseket hajthatnak végre. Például egy predikátumfüggvény, amely implicit módon konvertál egy sztringet datetime típusra, az aktuális munkamenet beállításai alapján, szűrhet különböző sorokat. A predikátumfüggvények általában a következő szabályok szerint működnek:

• A predikátumfüggvények nem konvertálhatnak implicit módon karaktersztringeket dátum, smalldatetime, datetime, datetime2 vagydatetimeoffset értékre, vagy fordítva, mivel ezeket az átalakításokat a SET DATEFORMAT (Transact-SQL) és a SET LANGUAGE (Transact-SQL) beállítások befolyásolják. Ehelyett használja a függvényt CONVERT , és explicit módon adja meg a stílusparamétert.

• A predikátumfüggvények nem támaszkodhatnak a hét első napjának értékére, mert ezt az értéket a SET DATEFIRST (Transact-SQL) beállítás befolyásolja.

• A predikátumfüggvények nem támaszkodhatnak olyan aritmetikai vagy összesítési kifejezésekre, amelyek hiba esetén adnak vissza NULL (például túlcsordulás vagy osztás nullával), mert ezt a viselkedést befolyásolja a SET ANSI_WARNINGS (Transact-SQL), a SET NUMERIC_ROUNDABORT (Transact-SQL) és a SET ARITHABORT (Transact-SQL) beállítás.

• A predikátumfüggvények nem hasonlíthatók össze összefűzött sztringekkel NULL, mert ezt a viselkedést a SET CONCAT_NULL_YIELDS_NULL (Transact-SQL) beállítás befolyásolja.

Biztonsági megjegyzés: oldalcsatornás támadások

Rosszindulatú biztonsági házirend-kezelő

Fontos megjegyezni, hogy egy rosszindulatú biztonsági házirend-kezelő, amely megfelelő engedélyekkel rendelkezik ahhoz, hogy biztonsági szabályzatot hozzon létre egy bizalmas oszlop tetején, és engedéllyel rendelkezik a beágyazott táblaértékkel rendelkező függvények létrehozására vagy módosítására, ütközhet egy másik felhasználóval, aki egy táblára vonatkozó engedélyekkel rendelkezik az adatszűrés végrehajtásához, mivel rosszindulatúan hoz létre beágyazott, táblázatértékes függvényeket, amelyek oldalcsatorna-támadások használatával következtetnek az adatokra. Az ilyen támadásokhoz összejátszásra (vagy egy rosszindulatú felhasználónak adott túlzott engedélyekre) lenne szükség, és valószínűleg több iterációra lenne szükség a szabályzat módosításához (engedélyre van szükség a predikátum eltávolításához a sémakötés megszakításához), módosítani kell a beágyazott táblaértékű függvényeket, és a céltáblán ismétlődően futtatni a választó utasításokat. Javasoljuk, hogy szükség szerint korlátozza az engedélyeket, és figyelje a gyanús tevékenységeket. Figyelni kell az olyan tevékenységeket, mint a szabályzatok folyamatos módosítása és a sorszintű biztonsághoz kapcsolódó beágyazott táblaértékelt függvények.

Gondosan összeállított lekérdezések

Az adatok kiszivárgását olyan gondosan kialakított lekérdezések használatával okozhatja, amelyek hibákat használnak az adatok kiszűréséhez. Például tudatná egy rosszindulatú felhasználóval, SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe'; hogy John Doe fizetése pontosan 100 000 dollár. Annak ellenére, hogy létezik biztonsági predikátum, amely megakadályozza, hogy egy rosszindulatú felhasználó közvetlenül lekérdezze mások fizetését, a felhasználó felismerheti, amikor a lekérdezés nullával való osztás kivételt ad vissza.

Funkciók közötti kompatibilitás

Általánosságban elmondható, hogy a sorszintű biztonság a várt módon fog működni a funkciók között. Van azonban néhány kivétel. Ez a szakasz számos megjegyzést és kikötést tartalmaz a sorszintű biztonság és az SQL Server bizonyos egyéb funkcióival való használatához.

• DBCC SHOW_STATISTICS a szűretlen adatokra vonatkozó statisztikákat jelent, és kiszivároghatnak a biztonsági szabályzat által egyébként védett információk. Emiatt a sorszintű biztonsági szabályzattal rendelkező táblák statisztikai objektumainak megtekintéséhez való hozzáférés korlátozott. A felhasználónak rendelkeznie kell a táblával, vagy a felhasználónak tagja kell lennie a sysadmin rögzített kiszolgálói szerepkörnek, a db_owner rögzített adatbázis-szerepkörnek vagy a db_ddladmin rögzített adatbázis-szerepkörnek.

• Fájlstream: Az RLS nem kompatibilis a Filestreamdel.

• PolyBase: Az RLS-t külső táblák támogatják az Azure Synapse és az SQL Server 2019 CU7 vagy újabb verzióiban.

• Memory-Optimized táblák: A memóriaoptimalizált táblán biztonsági predikátumként használt beágyazott táblaértékes függvényt a WITH NATIVE_COMPILATION beállítással kell definiálni. Ezzel a beállítással a memóriaoptimalizált táblák által nem támogatott nyelvi funkciók be lesznek tiltva, és a megfelelő hiba a létrehozáskor jelenik meg. További információ: Sorszintű biztonság Memory-Optimized táblákban.

• Indexelt nézetek: A biztonsági szabályzatok általában a nézetek tetején hozhatók létre, és a nézetek a biztonsági szabályzatok által kötött táblákra is létrehozhatók. Az indexelt nézetek azonban nem hozhatók létre a biztonsági szabályzattal rendelkező táblák tetején, mert az indexen keresztüli sorkeresések megkerülnék a szabályzatot.

• Adatrögzítés módosítása: Az Adatrögzítés módosítása (CDC) teljes sorokat szivárogtathat ki, amelyeket szűrni kell azon tagok db_owner vagy felhasználók számára, akik a CDC tábla engedélyezésekor megadott "gating" szerepkör tagjai. Ezt a függvényt explicit módon úgy állíthatja be, hogy NULL az összes felhasználó hozzáférhessen a módosítási adatokhoz. Valójában db_owner és e szerepkör tagjai láthatják az összes adatváltozást egy táblán, még akkor is, ha a táblán biztonsági szabályzat van érvényben.

• Változáskövetés: A változáskövetés kiszivárogtathatja a sorok elsődleges kulcsát, amelyet szűrni kell a mindkettővel SELECT és VIEW CHANGE TRACKING engedélyekkel rendelkező felhasználókra. A tényleges adatértékek nem szivárognak ki; csak az a tény, hogy az A oszlop frissült/beszúrva/törölve lett egy bizonyos elsődleges kulccsal rendelkező sorhoz. Ez akkor problémás, ha az elsődleges kulcs bizalmas elemet, például társadalombiztosítási számot tartalmaz. A gyakorlatban azonban ez CHANGETABLE szinte mindig az eredeti táblához van csatlakoztatva a legújabb adatok lekérése érdekében.

• Full-Text Keresés: A következő Full-Text keresési és szemantikai keresési függvényeket használó lekérdezések esetében teljesítménybeli találat várható, mivel a sorszintű biztonság alkalmazásához bevezetett extra illesztésnek köszönhetően elkerülhető a szűrni kívánt sorok elsődleges kulcsainak kiszivárgása: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstablesemanticsimilaritytable.

• Oszlopcentrikus indexek: Az RLS kompatibilis a fürtözött és a nemclustered oszlopcentrikus indexekkel is. Mivel azonban a sorszintű biztonság egy függvényt alkalmaz, lehetséges, hogy az optimalizáló úgy módosítja a lekérdezéstervet, hogy ne használjon kötegelt módot.

• Particionált nézetek: A blokk-predikátumok nem definiálhatók particionált nézetekben, és a particionált nézetek nem hozhatók létre a blokk predikátumokat használó táblákon. A szűrési predikátumok kompatibilisek a particionált nézetekkel.

• Temporális táblák: A temporális táblák kompatibilisek az RLS-vel. Az aktuális tábla biztonsági predikátumai azonban nem replikálódnak automatikusan az előzmények táblába. Ha biztonsági szabályzatot szeretne alkalmazni az aktuális és az előzménytáblákra is, minden táblához külön biztonsági predikátumot kell hozzáadnia.

Egyéb korlátozások:

• A Microsoft Fabric és az Azure Synapse Analytics csak a szűrési predikátumokat támogatja. A blokk predikátumok jelenleg nem támogatottak a Microsoft Fabricben és az Azure Synapse Analyticsben.

Examples

A. Forgatókönyv az adatbázisba hitelesítést végző felhasználók számára

Ez a példa három felhasználót hoz létre, és hat sorból álló táblát hoz létre és tölt fel. Ezután létrehoz egy beágyazott táblaértékű függvényt és egy biztonsági szabályzatot a táblához. A példa ezután bemutatja, hogyan szűrhetők a SELECT utasítások a különböző felhasználók számára.

Hozzon létre három felhasználói fiókot, amelyek különböző hozzáférési képességeket mutatnak be.

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

Hozzon létre egy táblát az adatok tárolásához.

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

Töltse fel a táblázatot hat adatsorsal, amelyek három megrendelést mutatnak az egyes értékesítési képviselők számára.

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;

Olvasási hozzáférés biztosítása a táblán minden felhasználónak.

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

Hozzon létre egy új sémát és egy beágyazott táblaértékű függvényt. A függvény akkor ad vissza 1 , ha az SalesRep oszlop egy sora megegyezik a lekérdezést végrehajtó felhasználóval (@SalesRep = USER_NAME()), vagy ha a lekérdezést végrehajtó felhasználó a Kezelő felhasználója (USER_NAME() = 'Manager'). Ez a példa egy felhasználó által definiált, táblaértékű függvényre hasznos, ha szűrőként szolgál a következő lépésben létrehozott biztonsági szabályzathoz.

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

Hozzon létre egy biztonsági szabályzatot, amely szűrő-predikátumként hozzáadja a függvényt. A házirend engedélyezéséhez STATE-t ON-re kell állítani.

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

Engedélyek engedélyezése SELECT a tvf_securitypredicate függvényhez:

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

Most tesztelje a szűrési predikátumot úgy, hogy a táblából kiválasztja az Sales.Orders egyes felhasználókat.

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;

A vezetőnek mind a hat sort látnia kell. A Sales1 felhasználók és Sales2 felhasználók csak a saját értékesítésüket láthatják.

Módosítsa a biztonsági szabályzatot a szabályzat letiltásához.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Most Sales1 a Sales2 felhasználók mind a hat sort láthatják.

Csatlakozzon az SQL-adatbázishoz, hogy megtisztítsa az erőforrásokat a mintagyakorlatból:

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. Forgatókönyvek a Row Level Security azure Synapse külső táblán való használatára

Ez a rövid példa három felhasználót és egy hat sorból álló külső táblát hoz létre. Ezután létrehoz egy beágyazott táblaértékű függvényt és egy biztonsági szabályzatot a külső táblához. A példa bemutatja, hogyan szűrhetők a választó utasítások a különböző felhasználókra.

Prerequisites

1. Dedikált SQL-készletre van szüksége. Lásd : Dedikált SQL-készlet létrehozása
2. A dedikált SQL-készletet futtató kiszolgálót Microsoft Entra-azonosítóval (korábban Azure Active Directory) kell regisztrálni, és rendelkeznie kell egy engedélyekkel rendelkező Storage Blog Data Contributor Azure Storage-fiókkal. Kövesse az Azure SQL Database-beli kiszolgálók virtuális hálózati szolgáltatásvégpontjainak és szabályainak használatát.
3. Hozzon létre egy fájlrendszert az Azure Storage-fiókhoz. A tárfiók megtekintéséhez használja az Azure Storage Explorert . Kattintson a jobb gombbal a tárolókra , és válassza a Fájlrendszer létrehozása lehetőséget.

Ha már rendelkezik az előfeltételekkel, hozzon létre három felhasználói fiókot, amelyek különböző hozzáférési képességeket mutatnak be.

--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 ;

Hozzon létre egy táblát az adatok tárolásához.

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

Töltse fel a táblázatot hat adatsorsal, amelyek három megrendelést mutatnak az egyes értékesítési képviselők számára.

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;

Hozzon létre egy Külső Azure Synapse-táblát a Sales létrehozott táblából.

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;

Adja meg SELECT a létrehozott külső tábla Sales_ext három felhasználójának.

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

Hozzon létre egy új sémát és egy beágyazott táblaértékű függvényt, ezt az A példában fejezhette be. A függvény akkor ad vissza 1 , ha az SalesRep oszlop egy sora megegyezik a lekérdezést végrehajtó felhasználóval (@SalesRep = USER_NAME()), vagy ha a lekérdezést végrehajtó felhasználó a Manager felhasználó (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';

Hozzon létre egy biztonsági szabályzatot a külső táblán a beágyazott táblaértékelt függvény használatával szűrő-predikátumként. A házirend engedélyezéséhez STATE-t ON-re kell állítani.

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

Most tesztelje a szűrési predikátumot a Sales_ext külső tábla kiválasztásával. Jelentkezzen be minden felhasználóként, Sales1Sales2és Manager. Futtassa a következő parancsot minden felhasználóként.

SELECT * FROM Sales_ext;

A(z) Manager-nak/-nek mind a hat sort látnia kell. A Sales1 és Sales2 felhasználóknak csak az értékesítésüket kell látniuk.

Módosítsa a biztonsági szabályzatot a szabályzat letiltásához.

ALTER SECURITY POLICY SalesFilter_ext
WITH (STATE = OFF);

Most a Sales1 és Sales2 felhasználók láthatják mind a hat sort.

Csatlakozzon az Azure Synapse-adatbázishoz, hogy megtisztítsa az erőforrásokat a mintagyakorlatból:

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;

Csatlakozzon a logikai kiszolgáló adatbázisához master az erőforrások törléséhez:

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

C. Forgatókönyv olyan felhasználók számára, akik egy középső szintű alkalmazáson keresztül csatlakoznak az adatbázishoz

Note

Ebben a példában a blokk predikátumok funkció jelenleg nem támogatott a Microsoft Fabric és az Azure Synapse esetében, ezért a helytelen felhasználói azonosítóhoz tartozó sorok beszúrása nem lesz letiltva.

Ez a példa bemutatja, hogyan valósíthat meg egy középső rétegbeli alkalmazás kapcsolatszűrést, ahol az alkalmazás felhasználói (vagy bérlői) ugyanazzal az SQL Server-felhasználóval (az alkalmazással) osztoznak. Az alkalmazás az adatbázishoz való csatlakozás után beállítja az aktuális alkalmazásfelhasználó-azonosítót SESSION_CONTEXT, majd a biztonsági szabályzatok transzparensen szűrik azokat a sorokat, amelyeknek nem kellene láthatónak lenniük ezen az azonosítón, és azt is letiltják, hogy a felhasználó helytelen felhasználói azonosítójú sorokat szúrjon be. Nincs szükség más alkalmazásmódosításra.

Hozzon létre egy táblát az adatok tárolásához.

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

Töltse fel a táblázatot hat adatsorsal, amelyek három megrendelést mutatnak az egyes alkalmazásfelhasználók számára.

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);

Hozzon létre egy alacsony jogosultságú felhasználót, amelyet az alkalmazás használni fog a csatlakozáshoz.

-- 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;

Hozzon létre egy új sémát és predikátumfüggvényt, amely a sorok szűréséhez a benne tárolt SESSION_CONTEXT() alkalmazásfelhasználói azonosítót használja.

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

Hozzon létre egy biztonsági szabályzatot, amely ezt a függvényt szűrő-predikátumként és blokk-predikátumként adja hozzá.Sales A blokk predikátumnak csak azért van szüksége AFTER INSERT, mert BEFORE UPDATEBEFORE DELETE már szűrve van, és AFTER UPDATE szükségtelen, mert az AppUserId oszlop nem frissíthető más értékekre a korábban beállított oszlopengedély miatt.

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);

Most már szimulálhatjuk a kapcsolatszűrést úgy, hogy a Sales különböző felhasználói azonosítók beállítása után kiválasztjuk a SESSION_CONTEXT() táblából. A gyakorlatban az alkalmazás feladata az aktuális felhasználói azonosító SESSION_CONTEXT() beállítása a kapcsolat megnyitása után. @read_only paramétert úgy állítja be, hogy a 1 megakadályozza az érték ismételt módosítását, amíg a kapcsolat be nem záródik és vissza nem kerül a kapcsolatkészletbe.

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

Adatbázis-erőforrások törlése.

DROP USER AppUser;

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

D. Forgatókönyv a biztonsági predikátum keresési táblájának használatához

Ez a példa egy keresési táblát használ a felhasználói azonosító és a szűrt érték közötti hivatkozáshoz ahelyett, hogy meg kellene adnia a felhasználói azonosítót a ténytáblában. Három felhasználót hoz létre, és három felhasználót hoz létre és feltölt egy ténytáblát, Sample.Sales, amely hat sorból áll, valamint egy keresőtáblát, amely két sorból áll. Ezután létrehoz egy beágyazott táblaértékű függvényt, amely összekapcsolja a ténytáblát a keresőtáblával a felhasználó azonosítójának megkapása érdekében, és létrehoz egy biztonsági szabályzatot a táblához. A példa ezután bemutatja, hogyan szűrhetők a SELECT utasítások a különböző felhasználók számára.

Hozzon létre három felhasználói fiókot, amelyek különböző hozzáférési képességeket mutatnak be.

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

Hozzon létre egy sémát Sample és egy ténytáblát az Sample.Salesadatok tárolásához.

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

Töltse fel Sample.Sales hat sornyi adattal.

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;

Hozzon létre egy táblát a keresési adatok tárolásához – ebben az esetben a kapcsolat Salesrep és Product között.

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

Töltse ki a keresési táblát mintaadatokkal, és rendeljen hozzá egy Product-t minden értékesítési képviselőhöz.

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;

Olvasási hozzáférés biztosítása a ténytáblán minden felhasználónak.

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

Hozzon létre egy új sémát és egy beágyazott táblaértékű függvényt. A függvény akkor ad vissza, 1 ha egy felhasználó lekérdezi a ténytáblát Sample.Sales , és a SalesRep tábla Lk_Salesman_Product oszlopa megegyezik a lekérdezést végrehajtó felhasználóval (@SalesRep = USER_NAME()) az oszlop ténytáblához Product való csatlakozáskor, vagy ha a lekérdezést végrehajtó felhasználó a Manager felhasználó (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'
                   ) ;

Hozzon létre egy biztonsági szabályzatot, amely szűrő-predikátumként hozzáadja a függvényt. A házirend engedélyezéséhez STATE-t ON-re kell állítani.

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

Engedélyek engedélyezése SELECT a fn_securitypredicate függvényhez:

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

Most tesztelje a szűrési predikátumot úgy, hogy a táblából kiválasztja az Sample.Sales egyes felhasználókat.

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;

A(z) Manager-nak/-nek mind a hat sort látnia kell. A Sales1 felhasználók és Sales2 felhasználók csak a saját értékesítésüket láthatják.

Módosítsa a biztonsági szabályzatot a szabályzat letiltásához.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Most Sales1 a Sales2 felhasználók mind a hat sort láthatják.

Csatlakozzon az SQL-adatbázishoz, hogy megtisztítsa az erőforrásokat a mintagyakorlatból:

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. Sorszintű biztonsági forgatókönyv a Microsoft Fabricben

A Sorszintű biztonsági raktár és AZ SQL Analytics végpontot a Microsoft Fabricben mutatjuk be.

Az alábbi példa mintatáblákat hoz létre, amelyek a Microsoft Fabric warehouse-jával fognak működni, de az SQL Analytics-végponton meglévő táblákat használnak. Az SQL Analytics-végponton nem használhatja CREATE TABLE, de használhatja CREATE SCHEMAa , CREATE FUNCTIONés CREATE SECURITY POLICY.

Ebben a példában először hozzon létre egy sémát sales, egy táblát 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');

Security Séma, függvény Security.tvf_securitypredicateés biztonsági szabályzat SalesFilterlétrehozása.

-- 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

A biztonsági szabályzat alkalmazása és a függvény létrehozása után a felhasználók Sales1@contoso.comSales2@contoso.com csak a saját adataikat láthatják a sales.Orders táblában, ahol az oszlop SalesRep megegyezik a beépített függvény által visszaadott saját felhasználónévvel USER_NAME. A Háló felhasználó manager@contoso.com az összes adatot láthatja a sales.Orders táblában.

Kapcsolódó tartalom

• BIZTONSÁGI SZABÁLYZAT LÉTREHOZÁSA (Transact-SQL)
• ALTER SECURITY POLICY (Transact-SQL)
• DROP SECURITY POLICY (Transact-SQL)
• CREATE függvény (Transact-SQL)
• SESSION_CONTEXT (Transact-SQL)
• sp_set_session_context (Transact-SQL)
• sys.security_policies (Transact-SQL)
• sys.security_predicates (Transact-SQL)
• Felhasználó által definiált függvények (adatbázismotor) létrehozása
• GRANT objektumengedélyek (Transact-SQL)