Zabezpečení na úrovni řádků

Platí pro:SQL ServerAzure SQL DatabaseSpravovaná instance Azure SQLAzure Synapse AnalyticsKoncový bod analýzy SQL v Microsoft FabricSklad v Microsoft FabricDatabáze SQL v Microsoft Fabric

Zabezpečení na úrovni řádků (RLS) umožňuje řídit přístup k řádkům v tabulce databáze pomocí členství ve skupině nebo kontextu spuštění.

Zabezpečení na úrovni řádků zjednodušuje návrh a kódování zabezpečení ve vaší aplikaci. RLS pomáhá implementovat omezení přístupu k datovým řádkům. Můžete například zajistit, aby pracovníci přistupovali pouze k těmto datovým řádkům, které jsou relevantní pro jejich oddělení. Dalším příkladem je omezení přístupu zákazníků k datům relevantním pro jejich společnost.

Logika omezení přístupu se nachází v databázové vrstvě, nikoli mimo data v jiné aplikační vrstvě. Databázový systém použije omezení přístupu při každém pokusu o přístup k datům z libovolné vrstvy. Díky tomu je systém zabezpečení spolehlivější a robustnější tím, že sníží plochu systému zabezpečení.

Implementujte zabezpečení na úrovni řádků pomocí příkazu CREATE SECURITY POLICY Transact-SQL a predikáty vytvořené jako vložené funkce hodnotné tabulkou.

Zabezpečení na úrovni řádků bylo poprvé zavedeno pro SQL Server 2016 (13.x).

Note

Tento článek se zaměřuje na platformy SQL Server a Azure SQL. Informace o Microsoft Fabric najdete v tématu Zabezpečení na úrovni řádků v Microsoft Fabric.

Description

Zabezpečení na úrovni řádků (RLS) podporuje dva typy predikátů zabezpečení:

• Predikáty filtru nepozorovaně filtrují řádky, které jsou k dispozici pro operace čtení (SELECT, UPDATE a DELETE).

• Predikáty bloku explicitně blokují operace zápisu (AFTER INSERT, AFTER UPDATEBEFORE UPDATE, , BEFORE DELETE), které porušují predikát.

Přístup k datům na úrovni řádků v tabulce je omezený predikátem zabezpečení definovaným jako vložená funkce s hodnotou tabulky. Funkce se pak vyvolá a vynucuje zásadami zabezpečení. U predikátů filtru aplikace neví o řádcích filtrovaných ze sady výsledků. Pokud jsou všechny řádky filtrované, vrátí se sada null. U blokových predikátů všechny operace, které porušují predikát, selžou s chybou.

Predikáty filtru se použijí při čtení dat ze základní tabulky. Ovlivňují všechny operace získání: SELECT, DELETEa UPDATE. Uživatelé nemůžou vybrat nebo odstranit filtrované řádky. Uživatel nemůže aktualizovat řádky filtrované. Je ale možné aktualizovat řádky takovým způsobem, že se následně vyfiltrují. Predikáty bloku ovlivňují všechny operace zápisu.

• AFTER INSERT a AFTER UPDATE predikáty můžou uživatelům zabránit v aktualizaci řádků na hodnoty, které porušují predikát.

• BEFORE UPDATE predikáty můžou uživatelům zabránit v aktualizaci řádků, které aktuálně porušují predikát.

• BEFORE DELETE predikáty mohou blokovat operace odstranění.

Predikáty filtru i blokování a zásady zabezpečení mají následující chování:

• Můžete definovat predikátovou funkci, která se spojí s jinou tabulkou nebo vyvolá funkci. Pokud je zásada zabezpečení vytvořená pomocí SCHEMABINDING = ON (výchozí), je spojení nebo funkce přístupná z dotazu a funguje podle očekávání bez dalších kontrol oprávnění. Pokud se zásady zabezpečení vytvoří pomocí SCHEMABINDING = OFF, budou uživatelé potřebovat SELECT oprávnění k těmto dalším tabulkám a funkcím pro dotazování na cílovou tabulku. Pokud predikátová funkce vyvolá skalární funkci CLR, je zapotřebí také příslušné oprávnění EXECUTE.

• Můžete provést dotaz na tabulku, která má definovaný predikát zabezpečení, ale je deaktivovaný. Všechny řádky filtrované nebo blokované nejsou ovlivněny.

• dbo Pokud uživatel, člen db_owner role nebo vlastník tabulky dotazuje tabulku, která má definovanou a povolenou zásadu zabezpečení, jsou řádky filtrované nebo blokované podle definice zásad zabezpečení.

• Výsledkem pokusů o změnu schématu tabulky vázané na zásadu zabezpečení vázaného schématem je chyba. Sloupce, na které predikát neodkazuje, se ale dají změnit.

• Pokusy o přidání predikátu v tabulce, která už má definovaný predikát pro zadanou operaci, způsobí chybu. K tomu dochází bez ohledu na to, jestli je predikát povolený nebo ne.

• Pokusy o úpravu funkce, která se používá jako predikát v tabulce v rámci zásad zabezpečení vázaného schématem, způsobí chybu.

• Definování několika aktivních zásad zabezpečení, které obsahují nepřekryvné predikáty, proběhne úspěšně.

Predikáty filtru mají následující chování:

• Definujte zásady zabezpečení, které filtruje řádky tabulky. Aplikace nezná žádné řádky, které jsou filtrovány pro SELECT, UPDATEa DELETE operace. Zahrnutí situací, kdy se vyfiltrují všechny řádky. Aplikace může INSERT řádky vyfiltrovat i v případě, že budou filtrovány během jakékoli jiné operace.

Predikáty bloků mají následující chování:

• Predikáty bloku pro UPDATE jsou rozděleny do samostatných operací pro BEFORE a AFTER. Uživatelům například nemůžete blokovat aktualizaci řádku, aby měli hodnotu vyšší než aktuální. Pokud je tento druh logiky vyžadovaný, musíte použít triggery s mezilehlými tabulkami DELETED a INSERTED, abyste společně odkazovali na staré a nové hodnoty.

• Optimalizátor nekontroluje AFTER UPDATE predikát bloku, pokud se nezměnily sloupce používané funkcí predikátu. Například: Alice by neměla mít možnost změnit plat na vyšší než 100 000. Alice může změnit adresu zaměstnance, jehož plat je již větší než 100 000, pokud se sloupce odkazované v predikátu nezměnily.

• V hromadných rozhraních API nebyly provedeny žádné změny, včetně BULK INSERT. To znamená, že predikáty AFTER INSERT bloku platí pro operace hromadného vkládání stejně jako běžné operace vkládání.

Případy použití

Tady jsou příklady návrhu použití zabezpečení na úrovni řádků (RLS):

• Nemocnice může vytvořit zásady zabezpečení, které zdravotní sestram umožňují zobrazit pouze řádky dat pro své pacienty.

• Banka může vytvořit zásadu, která omezí přístup k řádkům finančních dat na základě obchodní divize nebo role zaměstnance ve společnosti.

• Víceklientská aplikace může vytvořit zásadu, která vynutí logické oddělení řádků dat každého tenanta od řádků všech ostatních tenantů. Efektivita se dosahuje úložištěm dat pro mnoho tenantů v jedné tabulce. Každý tenant může zobrazit pouze své datové řádky.

Predikáty filtru RLS jsou funkčně ekvivalentní připojení WHERE klauzule. Predikát může být stejně sofistikovaný jako obchodní postupy, nebo klauzule může být jednoduchá jako WHERE TenantId = 42.

Formálně řečeno, RLS zavádí řízení přístupu na základě predikátů pomocí zabezpečení na úrovni řádků. Nabízí flexibilní, centralizované a predikátové vyhodnocení. Predikát může být založený na metadatech nebo jakýchkoli jiných kritériích, která správce určí podle potřeby. Predikát se používá jako kritérium k určení, jestli má uživatel odpovídající přístup k datům na základě atributů uživatele. Řízení přístupu na základě popisků je možné implementovat pomocí řízení přístupu na základě predikátu.

Permissions

Vytvoření, změna nebo vyřazení zásad zabezpečení vyžaduje ALTER ANY SECURITY POLICY oprávnění. Vytvoření nebo vyřazení zásad zabezpečení vyžaduje ALTER oprávnění ke schématu.

Kromě toho jsou pro každý přidaný predikát vyžadována následující oprávnění:

• SELECT a REFERENCES oprávnění k funkci, která se používá jako predikát.

• REFERENCES oprávnění k cílové tabulce, která je svázaná se zásadou.

• REFERENCES oprávnění ke každému sloupci z cílové tabulky použité jako argumenty.

Zásady zabezpečení platí pro všechny uživatele, včetně uživatelů dbo v databázi. Uživatelé Dbo můžou měnit nebo odstraňovat zásady zabezpečení, ale jejich změny zásad zabezpečení je možné auditovat. Pokud uživatelé s vysokými oprávněními, jako je správce systému nebo db_owner, potřebují zobrazit všechny řádky pro řešení potíží nebo ověření dat, musí být zásady zabezpečení zapsány, aby to bylo možné.

Pokud je vytvořena zásada zabezpečení s SCHEMABINDING = OFF, pak pro dotaz na cílovou tabulku musí mít uživatelé oprávnění SELECT nebo EXECUTE k predikátové funkci a ke všem dalším tabulkám, zobrazením nebo funkcím použitým v predikátové funkci. Pokud se vytvoří zásada zabezpečení s SCHEMABINDING = ON (výchozí), tyto kontroly oprávnění se při dotazování na cílovou tabulku uživatelům nepovolí.

Osvědčené postupy

• Důrazně doporučujeme vytvořit samostatné schéma pro objekty RLS: predikátové funkce a zásady zabezpečení. To pomáhá oddělit oprávnění vyžadovaná u těchto speciálních objektů od cílových tabulek. V databázích s více tenanty může být potřeba další oddělení pro různé zásady a predikátové funkce, ale ne jako standard pro každý případ.

• Oprávnění ALTER ANY SECURITY POLICY je určené pro vysoce privilegované uživatele (například správce zásad zabezpečení). Správce zásad zabezpečení nevyžaduje SELECT oprávnění k tabulkám, které chrání.

• Vyhněte se převodům typů v predikátech funkcí, abyste se vyhnuli potenciálním chybám za běhu.

• Vyhněte se rekurzi v predikátech funkcí, pokud je to možné, abyste se vyhnuli snížení výkonu. Optimalizátor dotazů se pokusí rozpoznat přímé rekurze, ale nezaručuje nalezení nepřímých rekurzí. Nepřímá rekurze je místo, kde druhá funkce volá predikátovou funkci.

• Nepoužívejte nadměrné spojení tabulek v predikátových funkcích, abyste maximalizovali výkon.

Vyhněte se predikátové logice, která závisí na možnostech SET specifických pro relaci: Je nepravděpodobné, že by se v praktických aplikacích používaly predikátové funkce, jejichž logika závisí na určitých možnostech specifických pro relaci. Tyto funkce však mohou uniknout informace, pokud jsou uživatelé schopni spouštět libovolné dotazy. Například predikátová funkce, která implicitně převede řetězec na datetime, může filtrovat různé řádky podle možnosti SET DATEFORMAT aktuální relace. Obecně platí, že funkce predikátu by měly dodržovat následující pravidla:

• Predikátové funkce by neměly implicitně převádět řetězce znaků na datum, smalldatetime, datetime, datetime2 nebo datetimeoffset nebo naopak, protože tyto převody jsou ovlivněny možnostmi SET DATEFORMAT (Transact-SQL) a SET LANGUAGE (Transact-SQL). Místo toho použijte CONVERT funkci a explicitně zadejte parametr stylu.

• Funkce predikátu by neměly spoléhat na hodnotu prvního dne v týdnu, protože tato hodnota je ovlivněna možností SET DATEFIRST (Transact-SQL).

• Predikátové funkce by neměly spoléhat na to, že aritmetické nebo agregační výrazy vrátí hodnotu \ v případě chyby (například přetečení nebo dělení nulou), protože toto chování je ovlivněno možnostmi \SET ANSI_WARNINGS (Transact-SQL)\, \SET NUMERIC_ROUNDABORT (Transact-SQL)\ a \SET ARITHABORT (Transact-SQL)\.

• Predikátové funkce by neměly porovnávat zřetězené řetězce s NULL, protože toto chování je ovlivněno možností SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).

Poznámka k zabezpečení: útoky na boční kanál

Škodlivý správce zásad zabezpečení

Je důležité si uvědomit, že správce zásad zabezpečení se zlými úmysly s dostatečnými oprávněními k vytvoření zásady zabezpečení nad citlivým sloupcem a s oprávněním k vytvoření nebo úpravě vložených funkcí s hodnotami v tabulce, může spolupracovat s jiným uživatelem, který má oprávnění SELECT nad tabulkou, aby provedl exfiltraci dat tím, že zlovolně vytvoří vložené funkce s hodnotami v tabulce, které jsou navrženy tak, aby používaly útoky na vedlejší kanály k odvození dat. Takové útoky by vyžadovaly kolaci (nebo nadměrná oprávnění udělená uživateli se zlými úmysly) a pravděpodobně by vyžadovaly několik iterací úprav zásad (vyžadujících oprávnění k odebrání predikátu, aby bylo možné přerušit vazbu schématu), úpravu vložených funkcí s hodnotami tabulky a opakované spuštění výběrových příkazů v cílové tabulce. Doporučujeme podle potřeby omezit oprávnění a monitorovat případné podezřelé aktivity. Je třeba monitorovat aktivity, jako jsou neustále se měnící zásady a tabulkové funkce s vloženými hodnotami související s zabezpečením na úrovni řádků.

Pečlivě vytvořené dotazy

Únik informací je možné způsobit pomocí pečlivě vytvořených dotazů, které používají chyby k exfiltraci dat. Dejte například uživateli se zlými úmysly vědět, SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe'; že plat Johna Doea je přesně 100 000 Kč. I když existuje bezpečnostní predikát, který brání škodlivému uživateli v přímém dotazování platu jiných lidí, může uživatel určit, kdy dotaz vrátí výjimku dělicí nulou.

Kompatibilita mezi funkcemi

Obecně platí, že zabezpečení na úrovni řádků bude fungovat podle očekávání napříč funkcemi. Existuje však několik výjimek. Tato část dokumentuje několik poznámek a upozornění pro použití zabezpečení na úrovni řádků s určitými dalšími funkcemi SQL Serveru.

• DBCC SHOW_STATISTICS hlásí statistiky o nefiltrovaných datech a může v opačném případě uniknout informace chráněné zásadami zabezpečení. Z tohoto důvodu je přístup k zobrazení objektu statistiky tabulky se zásadami zabezpečení na úrovni řádků omezen. Uživatel musí vlastnit tabulku nebo uživatel musí být členem sysadmin pevné role serveru, db_owner pevné databázové role nebo db_ddladmin pevné databázové role.

• Filestream: RLS není kompatibilní s Filestream.

• PolyBase: Zabezpečení na úrovni řádků (RLS) je podporováno u externích tabulek v Azure Synapse a v SQL Serveru 2019 CU7 nebo vyšších verzích.

• Tabulky optimalizované pro paměť: Vložená funkce vracející hodnoty tabulky použitá jako predikát zabezpečení v tabulce optimalizované pro paměť musí být definována pomocí možnosti WITH NATIVE_COMPILATION. Díky této možnosti budou jazykové funkce, které nejsou podporovány tabulkami optimalizovanými pro paměť, zakázány a příslušná chyba bude vydána při vytváření. Další informace naleznete v části Zabezpečení na úrovni řádků v paměťově optimalizovaných tabulkách.

• Indexovaná zobrazení: Obecně lze říci, že zásady zabezpečení mohou být aplikovány na zobrazení, a zobrazení mohou být vytvořena na základech tabulek, které jsou vázány zásadami zabezpečení. Indexovaná zobrazení se ale nelze vytvořit nad tabulkami, které mají zásady zabezpečení, protože vyhledávání řádků prostřednictvím indexu by zásady obešlo.

• Change Data Capture: Funkce Change Data Capture (CDC) může odhalit celé řádky, které by měly být filtrovány členům db_owner nebo uživatelům, kteří jsou členy role 'gating', určené při povolení funkce CDC pro tabulku. Můžete explicitně nastavit tuto funkci na NULL, aby měli všichni uživatelé přístup k datům změn. V důsledku toho db_owner můžou členové této role zobrazit všechny změny dat v tabulce, i když v tabulce existují zásady zabezpečení.

• Change Tracking: Change Tracking může způsobit únik primárního klíče řádků, které by měly být filtrovány uživatelům s oběma SELECT a VIEW CHANGE TRACKING oprávněními. Skutečné hodnoty dat neunikají; pouze skutečnost, že sloupec A byl aktualizován / vložen / odstraněn pro řádek s určitým primárním klíčem. To je problematické, pokud primární klíč obsahuje důvěrný prvek, například číslo sociálního pojištění. V praxi je to CHANGETABLE ale téměř vždy spojené s původní tabulkou, aby bylo možné získat nejnovější data.

• Full-Text hledání: U dotazů používajících následující funkce pro Full-Text hledání a sémantické vyhledávání se očekává snížení výkonu, protože se zavádí další spojení, které aplikuje zabezpečení na úrovni řádků a zabraňuje úniku primárních klíčů řádků, které mají být filtrovány: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.

• Indexy columnstore: RLS je kompatibilní s clusterovanými i neclusterovanými indexy columnstore. Vzhledem k tomu, že zabezpečení na úrovni řádků používá funkci, je možné, že optimalizátor může plán dotazu upravit tak, aby nevyužíl dávkový režim.

• Dělené zobrazení: Predikáty bloků nelze definovat v rozdělených zobrazeních a dělené zobrazení nelze vytvořit nad tabulkami, které používají predikáty bloků. Predikáty filtru jsou kompatibilní s dělenými zobrazeními.

• Dočasné tabulky: Dočasné tabulky jsou kompatibilní s RLS. Predikáty zabezpečení v aktuální tabulce se ale automaticky nereplikují do tabulky historie. Pokud chcete použít zásady zabezpečení pro aktuální i historii tabulek, musíte do každé tabulky přidat predikát zabezpečení jednotlivě.

Další omezení:

• Microsoft Fabric a Azure Synapse Analytics podporují pouze predikáty filtru. Predikáty bloků se v současné době nepodporují v Microsoft Fabric a Azure Synapse Analytics.

Examples

A. Scénář pro uživatele, kteří se ověřují v databázi

Tento příklad vytvoří tři uživatele a vytvoří a naplní tabulku šesti řádky. Pak vytvoří vloženou funkci s hodnotou tabulky a zásadu zabezpečení pro tabulku. Příklad pak ukazuje, jak jsou příkazy select filtrovány pro různé uživatele.

Vytvořte tři uživatelské účty, které demonstrují různé možnosti přístupu.

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

Vytvořte tabulku pro uložení dat.

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

Naplňte tabulku šesti řádky dat a zobrazte tři objednávky pro každého obchodního zástupce.

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;

Udělte každému uživateli přístup pro čtení v tabulce.

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

Vytvořte nové schéma a řádkovou funkci s návratovou hodnotou tabulky. Funkce se vrátí 1 , když je řádek ve SalesRep sloupci stejný jako uživatel, který spouští dotaz (@SalesRep = USER_NAME()) nebo pokud je uživatel, který dotaz spouští, uživatel správce (USER_NAME() = 'Manager'). Tento příklad uživatelem definované funkce s hodnotou tabulky je užitečný jako filtr pro zásady zabezpečení vytvořené v dalším kroku.

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

Vytvořte zásadu zabezpečení, která funkci přidá jako predikát filtru. STATE musí být nastaveno na ON, aby byla zásada povolena.

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

Povolte oprávnění SELECT pro funkci tvf_securitypredicate:

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

Teď otestujte predikát filtrování výběrem z Sales.Orders tabulky pro každého uživatele.

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;

Nadřízený by měl vidět všech šest řádků. Uživatelé Sales1 a Sales2 by měli vidět jen své vlastní prodeje.

Upravte zásady zabezpečení tak, aby zásady zakázaly.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Teď uživatelé Sales1 a Sales2 mohou vidět všech šest řádků.

Připojte se k databázi SQL a vyčistěte prostředky z tohoto ukázkového cvičení:

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. Scénáře použití zabezpečení na úrovni řádků v externí tabulce Azure Synapse

Tento krátký příklad vytvoří tři uživatele a externí tabulku se šesti řádky. Potom vytvoří vloženou funkci s hodnotou tabulky a zásadu zabezpečení pro externí tabulku. Příklad ukazuje, jak jsou příkazy select filtrovány pro různé uživatele.

Prerequisites

1. Musíte mít vyhrazený fond SQL. Viz Vytvoření vyhrazeného fondu SQL
2. Server hostující vyhrazený fond SQL musí být zaregistrovaný v Microsoft Entra ID (dříve Azure Active Directory) a musíte mít účet úložiště Azure s oprávněními Storage Blog Data Contributor . Postupujte podle kroků pro použití koncových bodů služby virtuální sítě a pravidel pro servery ve službě Azure SQL Database.
3. Vytvořte systém souborů pro účet Azure Storage. Pomocí Průzkumníka služby Azure Storage zobrazte svůj účet úložiště. Klikněte pravým tlačítkem na kontejnery a vyberte Vytvořit systém souborů.

Jakmile budete mít splněné požadavky, vytvořte tři uživatelské účty, které demonstrují různé možnosti přístupu.

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

Vytvořte tabulku pro uložení dat.

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

Naplňte tabulku šesti řádky dat a zobrazte tři objednávky pro každého obchodního zástupce.

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;

Z tabulky, kterou jste vytvořili, vytvořte externí tabulku Sales Azure Synapse.

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;

Udělte SELECT třem uživatelům na externí tabulce Sales_ext, kterou jste vytvořili.

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

Vytvořte nové schéma a vloženou funkci vracející hodnotu tabulky; je možné, že jste to dokončili v příkladu A. Funkce vrátí 1, když je řádek ve sloupci SalesRep stejný jako uživatel provádějící dotaz (@SalesRep = USER_NAME()) nebo pokud je uživatel provádějící dotaz uživatelem Manager (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';

Vytvořte zásadu zabezpečení pro externí tabulku pomocí vložené funkce hodnotné tabulky jako predikátu filtru. STATE musí být nastaveno na ON, aby byla zásada povolena.

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

Teď otestujte predikát filtrování výběrem z Sales_ext externí tabulky. Přihlaste se jako každý uživatel, Sales1Sales2, a Manager. Jako každý uživatel spusťte následující příkaz.

SELECT * FROM Sales_ext;

Mělo by se zobrazit všech šest řádků. Uživatelé Sales1 a Sales2 by měli vidět jenom svoje prodeje.

Upravte zásady zabezpečení tak, aby zásady zakázaly.

ALTER SECURITY POLICY SalesFilter_ext
WITH (STATE = OFF);

Teď uvidí všichni Sales1Sales2 uživatelé všech šest řádků.

Připojte se k databázi Azure Synapse a vyčistěte prostředky z tohoto ukázkového cvičení:

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;

Připojte se k databázi logického master serveru a vyčistěte prostředky:

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

C. Scénář pro uživatele, kteří se připojují k databázi prostřednictvím aplikace střední vrstvy

Note

V tomto příkladu se funkce predikátů bloků v současné době nepodporuje pro Microsoft Fabric a Azure Synapse, takže vkládání řádků pro nesprávné ID uživatele není blokované.

Tento příklad ukazuje, jak může aplikace střední vrstvy implementovat filtrování připojení, kde uživatelé aplikace (nebo tenanti) sdílejí stejného uživatele SQL Serveru (aplikace). Aplikace nastaví aktuální ID uživatele aplikace v SESSION_CONTEXT po připojení k databázi a zásady zabezpečení transparentně filtrují řádky, které by neměly být viditelné pro toto ID, a také zablokují uživateli vkládání řádků pro nesprávné ID uživatele. Nejsou potřeba žádné jiné změny aplikace.

Vytvořte tabulku pro uložení dat.

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

Naplňte tabulku šesti řádky dat a zobrazte tři objednávky pro každého uživatele aplikace.

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

Vytvořte uživatele s nízkou úrovní oprávnění, kterého bude aplikace používat pro připojení.

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

Vytvořte nové schéma a predikátovou funkci, která použije ID uživatele aplikace uložené v SESSION_CONTEXT() pro filtrování řádků.

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

Vytvořte zásadu zabezpečení, která tuto funkci přidá jako filtrační predikát a blokovací predikát na Sales. Predikát bloku potřebuje pouze AFTER INSERT, protože BEFORE UPDATE a BEFORE DELETE jsou již filtrovány a AFTER UPDATE není nutné, protože sloupec AppUserId nelze aktualizovat na jiné hodnoty kvůli dříve nastavenému oprávnění ke sloupci.

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

Teď můžeme simulovat filtrování připojení výběrem z Sales tabulky po nastavení různých ID uživatelů v SESSION_CONTEXT(). V praxi je aplikace zodpovědná za nastavení aktuálního ID SESSION_CONTEXT() uživatele po otevření připojení. Nastavení parametru @read_only tak, aby 1 zabránilo opětovné změně hodnoty, dokud se připojení neuzavře (vrátí se do fondu připojení).

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

Vyčištění databázových prostředků

DROP USER AppUser;

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

D. Scénář použití vyhledávací tabulky pro predikát zabezpečení

Tento příklad používá vyhledávací tabulku pro propojení mezi identifikátorem uživatele a hodnotou, která se filtruje, a nemusíte zadávat identifikátor uživatele v tabulce faktů. Vytvoří tři uživatele a vytvoří a naplní tabulku faktů se Sample.Salesšesti řádky a vyhledávací tabulkou se dvěma řádky. Potom vytvoří vloženou funkci s hodnotou tabulky, která spojí tabulku faktů s vyhledávacím objektem, aby získala identifikátor uživatele, a zásadu zabezpečení pro tabulku. Příklad pak ukazuje, jak jsou příkazy select filtrovány pro různé uživatele.

Vytvořte tři uživatelské účty, které demonstrují různé možnosti přístupu.

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

Vytvořte Sample schéma a tabulku faktů pro Sample.Salesuložení dat.

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

Naplňte Sample.Sales šesti řádky dat.

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;

Vytvořte tabulku pro uložení vyhledávacích dat – v tomto případě relace mezi Salesrep a Product.

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

Vyplňte vyhledávací tabulku ukázkovými daty a propojte každé Product s každým obchodním zástupcem.

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;

Udělte každému uživateli přístup pro čtení v tabulce faktů.

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

Vytvořte nové schéma a vloženou funkci s hodnotou tabulky. Funkce vrátí 1, když se uživatel dotazuje na tabulku faktů Sample.Sales a SalesRep sloupec tabulky Lk_Salesman_Product odpovídá uživateli provádějícímu dotaz (@SalesRep = USER_NAME()) při spojení s tabulkou faktů přes sloupec Product, nebo pokud uživatel spouštějící dotaz je Manager uživatelem (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'
                   ) ;

Vytvořte zásadu zabezpečení, která funkci přidá jako predikát filtru. STATE musí být nastaveno na ON, aby byla zásada povolena.

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

Povolte oprávnění SELECT pro funkci fn_securitypredicate:

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

Teď otestujte predikát filtrování výběrem z Sample.Sales tabulky pro každého uživatele.

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;

Mělo by se zobrazit všech šest řádků. Uživatelé Sales1 a Sales2 by měli vidět jen své vlastní prodeje.

Upravte zásady zabezpečení tak, aby zásady zakázaly.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Teď uživatelé Sales1 a Sales2 mohou vidět všech šest řádků.

Připojte se k databázi SQL a vyčistěte prostředky z tohoto ukázkového cvičení:

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. Scénář zabezpečení na úrovni řádků v Microsoft Fabric

Můžeme si předvést koncový bod zabezpečení na úrovni řádků a koncový bod analýzy SQL v Microsoft Fabric.

Následující příklad vytvoří ukázkové tabulky, které budou fungovat se službou Warehouse v Microsoft Fabric, ale v koncovém bodu analýzy SQL používají existující tabulky. V koncovém bodu analýzy SQL nemůžete použít CREATE TABLE, ale můžete použít CREATE SCHEMA, CREATE FUNCTIONa CREATE SECURITY POLICY.

V tomto příkladu nejprve vytvořte schéma sales, tabulku 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');

Vytvoření schématu Security , funkce Security.tvf_securitypredicatea zásad SalesFilterzabezpečení

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

Po použití zásad zabezpečení a vytvoření funkce uživatelé Sales1@contoso.com a Sales2@contoso.com uvidí v tabulce jenom svá vlastní data sales.Orders , kde se sloupec SalesRep rovná vlastnímu uživatelskému jménu vráceným předdefinované funkce USER_NAME. Uživatel manager@contoso.com Fabric má přístup ke všem datům v tabulce sales.Orders.

Související obsah

• VYTVOŘTE ZÁSADY ZABEZPEČENÍ (Transact-SQL)
• ALTER SECURITY POLICY (Transact-SQL)
• DROP SECURITY POLICY (Transact-SQL) (Zrušit bezpečnostní politiku)
• VYTVOŘIT FUNKCI (Transact-SQL)
• SESSION_CONTEXT (Transact-SQL)
• sp_set_session_context (Transact-SQL)
• sys.security_policies (Transact-SQL)
• sys.security_predicates (Transact-SQL)
• vytvoření uživatelem definovaných funkcí (databázový stroj)
• udělení oprávnění k objektu (Transact-SQL)