Udostępnij za pomocą

Zabezpieczenia na poziomie wiersza

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsEndpoint analityki SQL w Microsoft FabricMagazyn w Microsoft FabricBaza danych SQL w Microsoft Fabric

Dekoracyjna grafika dotycząca zabezpieczeń na poziomie wiersza.

Zabezpieczenia na poziomie wiersza umożliwiają kontrolowanie dostępu do wierszy w tabeli bazy danych za pomocą członkostwa w grupie lub kontekstu wykonywania.

Zabezpieczenia na poziomie wiersza upraszczają projektowanie i kodowanie zabezpieczeń w aplikacji. RLS pomaga w implementacji ograniczeń dostępu do wierszy danych. Można na przykład upewnić się, że pracownicy uzyskują dostęp tylko do tych wierszy danych, które są odpowiednie dla ich działu. Innym przykładem jest ograniczenie dostępu do danych klientów tylko do danych istotnych dla ich firmy.

Logika ograniczeń dostępu znajduje się w warstwie bazy danych, a nie z dala od danych w innej warstwie aplikacji. System bazy danych stosuje ograniczenia dostępu za każdym razem, gdy jest podejmowana próba uzyskania dostępu do danych z dowolnej warstwy. Dzięki temu system zabezpieczeń jest bardziej niezawodny i niezawodny, zmniejszając obszar powierzchni systemu zabezpieczeń.

Zaimplementuj zabezpieczenia na poziomie wiersza przy użyciu instrukcji CREATE SECURITY POLICY Transact-SQL i predykatów utworzonych jako wbudowane funkcje wartości tabeli.

Zabezpieczenia na poziomie wiersza zostały po raz pierwszy wprowadzone do programu SQL Server 2016 (13.x).

Note

Ten artykuł koncentruje się na platformach SQL Server i Azure SQL. W przypadku usługi Microsoft Fabric zobacz Zabezpieczenia na poziomie wiersza w usłudze Microsoft Fabric.

Description

Zabezpieczenia na poziomie wiersza obsługują dwa typy predykatów zabezpieczeń:

  • Predykaty filtrujące niezauważalnie filtrują wiersze dostępne do operacji odczytu (SELECT, UPDATE, i DELETE).

  • Predykaty blokujące jawnie uniemożliwiają operacje zapisu (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE), które naruszają predykat.

Dostęp do danych na poziomie wiersza w tabeli jest ograniczony przez predykat zabezpieczeń zdefiniowany jako wbudowana funkcja wartości tabeli. Funkcja jest następnie wywoływana i wymuszana przez zasady zabezpieczeń. W przypadku predykatów filtru aplikacja nie zna wierszy filtrowanych z zestawu wyników. Jeśli wszystkie wiersze są filtrowane, zwracany jest zestaw wartości null. W przypadku predykatów blokowych wszystkie operacje, które naruszają predykat, kończą się niepowodzeniem z powodu błędu.

Predykaty filtru są stosowane podczas odczytywania danych z tabeli podstawowej. Mają wpływ na wszystkie operacje pobierania: SELECT, DELETE, i UPDATE. Użytkownicy nie mogą wybierać ani usuwać wierszy, które są filtrowane. Użytkownik nie może zaktualizować wierszy, które są filtrowane. Możliwe jest jednak zaktualizowanie wierszy tak, aby zostały następnie przefiltrowane. Predykaty blokowania wpływają na wszystkie operacje zapisu.

  • AFTER INSERT i AFTER UPDATE mogą uniemożliwić użytkownikom aktualizację wierszy do wartości, które naruszają predykat.

  • BEFORE UPDATE Predykaty mogą uniemożliwić użytkownikom aktualizowanie wierszy, które obecnie naruszają predykat.

  • BEFORE DELETE predykaty mogą blokować operacje usuwania.

Zarówno predykaty filtrowania i blokowania, jak i zasady zabezpieczeń, mają następujące zachowanie:

  • Możesz zdefiniować funkcję predykatu, która łączy się z inną tabelą i/lub wywołuje funkcję. Jeśli zasady zabezpieczeń są tworzone z SCHEMABINDING = ON (ustawienie domyślne), wtedy łączenie lub funkcja jest dostępna wewnątrz zapytania i działa zgodnie z oczekiwaniami bez dodatkowych kontroli uprawnień. Jeśli zasady zabezpieczeń są tworzone za pomocą SCHEMABINDING = OFF polecenia, użytkownicy będą potrzebować SELECT uprawnień dla tych dodatkowych tabel i funkcji do wykonywania zapytań dotyczących tabeli docelowej. Jeśli funkcja predykatu wywołuje funkcję skalarną CLR, potrzebne są także uprawnienia EXECUTE.

  • Możesz wydać zapytanie względem tabeli, która ma zdefiniowany predykat zabezpieczeń, ale wyłączony. Nie ma to wpływu na wszystkie wiersze, które są filtrowane lub blokowane.

  • dbo Jeśli użytkownik, członek db_owner roli lub właściciel tabeli wysyła zapytanie do tabeli, która ma zdefiniowane i włączone zasady zabezpieczeń, wiersze są filtrowane lub blokowane zgodnie z definicją zasad zabezpieczeń.

  • Próby zmiany schematu tabeli powiązanej przez powiązane ze schematem zasady zabezpieczeń powodują błąd. Jednak kolumny, do których nie odwołuje się predykat, mogą być zmieniane.

  • Próba dodania predykatu do tabeli, która ma już jedną zdefiniowaną dla określonej operacji, powoduje wystąpienie błędu. Dzieje się tak, czy predykat jest włączony, czy nie.

  • Próby zmodyfikowania funkcji, która jest używana jako predykat w tabeli w ramach zasad zabezpieczeń powiązanych ze schematem, powoduje wystąpienie błędu.

  • Definiowanie wielu aktywnych zasad zabezpieczeń, które zawierają predykaty nienawracania, kończy się powodzeniem.

Predykaty filtrów mają następujące zachowanie:

  • Zdefiniuj zasady zabezpieczeń, które filtrują wiersze tabeli. Aplikacja nie jest świadoma żadnych wierszy, które są filtrowane dla operacji SELECT, UPDATE i DELETE. Uwzględnianie sytuacji, w których wszystkie wiersze są odfiltrowane. Aplikacja może wyświetlać INSERT wiersze, nawet jeśli będą filtrowane podczas każdej innej operacji.

Predykaty blokowe mają następujące zachowanie:

  • Blokowe predykaty dla UPDATE są podzielone na oddzielne operacje dla BEFORE i AFTER. Nie można na przykład zablokować użytkownikom aktualizowania wiersza, aby mieć wartość wyższą niż bieżąca. Jeśli ten rodzaj logiki jest wymagany, należy użyć wyzwalaczy z tabelami pośrednimi DELETED i INSERTED, aby odwoływać się do starych i nowych wartości razem.

  • Optymalizator nie będzie sprawdzać AFTER UPDATE predykatu blokowego, jeśli kolumny używane przez funkcję predykatu nie zostały zmienione. Na przykład: Alicja nie powinna być w stanie zmienić wynagrodzenia na wyższą niż 100 000. Alicja może zmienić adres pracownika, którego wynagrodzenie jest już większe niż 100 000, o ile kolumny, do których odwołuje się predykat, nie zostały zmienione.

  • Nie wprowadzono żadnych zmian w zbiorczych interfejsach API, w tym BULK INSERT. Oznacza to, że predykaty blokowe AFTER INSERT mają zastosowanie do operacji wstawiania zbiorczego, tak jak w przypadku zwykłych operacji wstawiania.

Przypadki użycia

Poniżej przedstawiono przykłady projektowania sposobu użycia zabezpieczeń na poziomie wiersza:

  • Szpital może utworzyć zasady zabezpieczeń, które umożliwiają pielęgniarkom wyświetlanie wierszy danych tylko dla swoich pacjentów.

  • Bank może utworzyć zasady ograniczające dostęp do wierszy danych finansowych na podstawie działu biznesowego lub roli pracownika w firmie.

  • Aplikacja wielodostępna może utworzyć politykę, aby wymusić logiczne rozdzielenie wierszy danych każdego najemcy od wierszy każdego innego najemcy. Wydajność jest osiągana przez przechowywanie danych dla wielu dzierżaw w jednej tabeli. Każdy klient może wyświetlać tylko swoje wiersze danych.

Predykaty filtrów zabezpieczeń na poziomie wiersza są funkcjonalnie równoważne dodaniu klauzuli WHERE. Predykat może być tak wyrafinowany, jak dyktują praktyki biznesowe, lub klauzula może być tak prosta, jak WHERE TenantId = 42.

W bardziej formalnych terminach, RLS wprowadza kontrolę dostępu opartą na predykatach. Oferuje ona elastyczną, scentralizowaną, opartą na predykacie ocenę. Predykat może być oparty na metadanych lub innych kryteriach, które administrator określi zgodnie z potrzebami. Predykat jest używany jako kryterium w celu określenia, czy użytkownik ma odpowiedni dostęp do danych na podstawie atrybutów użytkownika. Kontrolę dostępu opartą na etykietach można zaimplementować przy użyciu kontroli dostępu opartej na predykacie.

Permissions

Tworzenie, zmienianie lub usuwanie zasad zabezpieczeń wymaga ALTER ANY SECURITY POLICY uprawnień. Tworzenie lub usuwanie zasad zabezpieczeń wymaga posiadania ALTER uprawnienia do schematu.

Ponadto dla każdego dodanego predykatu są wymagane następujące uprawnienia:

  • SELECT i REFERENCES uprawnienia do funkcji używanej jako predykat.

  • REFERENCES uprawnienie do tabeli docelowej powiązanej z zasadami.

  • REFERENCES uprawnienia do każdej kolumny z tabeli docelowej używanej jako argumenty.

Zasady zabezpieczeń dotyczą wszystkich użytkowników, w tym użytkowników dbo w bazie danych. Użytkownicy dbo mogą zmieniać lub usuwać zasady zabezpieczeń, jednak zmiany zasad zabezpieczeń mogą być poddawane inspekcji. Jeśli użytkownicy z wysokimi uprawnieniami, tacy jak sysadmin lub db_owner, muszą zobaczyć wszystkie wiersze do rozwiązywania problemów lub weryfikowania danych, należy zapisać zasady zabezpieczeń, aby to umożliwić.

Jeśli zasady zabezpieczeń są tworzone przy użyciu SCHEMABINDING = OFF, to aby wysłać zapytanie do tabeli docelowej, użytkownicy muszą mieć uprawnienia SELECT lub EXECUTE do funkcji predykatu oraz wszelkich dodatkowych tabel, widoków lub funkcji używanych w tej funkcji. Jeśli zasady zabezpieczeń są tworzone przy SCHEMABINDING = ON użyciu (ustawienie domyślne), te kontrole uprawnień są pomijane, gdy użytkownicy wysyłają zapytania do tabeli docelowej.

Najlepsze rozwiązania

  • Zdecydowanie zaleca się utworzenie oddzielnego schematu dla obiektów RLS: funkcje predykatów i zasady zabezpieczeń. Pomaga to oddzielić uprawnienia wymagane dla tych specjalnych obiektów od tabel docelowych. Dodatkowe rozdzielenie różnych zasad i funkcji predykcyjnych może być potrzebne w wielodostępnych bazach danych, ale nie jako standard dla każdego przypadku.

  • Uprawnienie ALTER ANY SECURITY POLICY jest przeznaczone dla wysoce uprzywilejowanych użytkowników (takich jak menedżer zasad zabezpieczeń). Menedżer zasad zabezpieczeń nie wymaga SELECT uprawnień do chronionych tabel.

  • Unikaj konwersji typów w funkcjach predykatów, aby uniknąć potencjalnych błędów środowiska uruchomieniowego.

  • Unikaj rekursji w funkcjach predykatu wszędzie tam, gdzie jest to możliwe, aby uniknąć obniżenia wydajności. Optymalizator zapytań spróbuje wykryć bezpośrednie rekursje, ale nie ma gwarancji, że znajdziesz rekursje pośrednie. Rekursja pośrednia polega na tym, że druga funkcja wywołuje funkcję predykatu.

  • Unikaj używania nadmiernych sprzężeń tabeli w funkcjach predykatów, aby zmaksymalizować wydajność.

Unikaj logiki predykatywnej, która zależy od specyficznych dla sesji opcji SET: Chociaż jest mało prawdopodobne, aby były stosowane w praktycznych aplikacjach, funkcje predykatywne, których logika zależy od pewnych specyficznych dla sesji opcji, mogą wyciekać informacje, jeśli użytkownicy mogą wykonywać dowolne zapytania. Na przykład funkcja predykatu, która niejawnie konwertuje ciąg na DateTime, może filtrować różne wiersze na podstawie SET DATEFORMAT ustawienia dla bieżącej sesji. Ogólnie rzecz biorąc, funkcje predykatu powinny przestrzegać następujących reguł:

Uwaga dotycząca zabezpieczeń: ataki typu side-channel

Menedżer złośliwych zasad zabezpieczeń

Ważne jest, aby zauważyć, że złośliwy menedżer zasad zabezpieczeń z wystarczającymi uprawnieniami do tworzenia zasad zabezpieczeń dotyczących poufnej kolumny i uprawnienia do tworzenia lub modyfikowania wbudowanych funkcji tabelarycznych może zmówić się z innym użytkownikiem, który ma uprawnienia do wyboru na tabeli, do przeprowadzenia eksfiltracji danych poprzez złośliwe tworzenie wbudowanych funkcji tabelarycznych zaprojektowanych do używania ataków kanału bocznego w celu wnioskowania danych. Takie ataki wymagają zmowy (lub nadmiernych uprawnień przyznanych złośliwemu użytkownikowi) i prawdopodobnie będą wymagały kilku iteracji modyfikowania zasad (wymagając uprawnień do usunięcia predykatu w celu przerwania powiązania schematu), modyfikowania wbudowanych funkcji tabel wartościowych i wielokrotnego uruchamiania instrukcji select w tabeli docelowej. Zalecamy ograniczenie uprawnień zgodnie z potrzebami i monitorowanie pod kątem wszelkich podejrzanych działań. Należy monitorować działania, takie jak stale zmieniające się zasady i wbudowane funkcje tabel związane z zabezpieczeniami na poziomie wiersza.

Starannie spreparowane zapytania

Istnieje możliwość spowodowania wycieku informacji przy użyciu starannie spreparowanych zapytań, które używają błędów do eksfiltrowania danych. Na przykład może poinformować złośliwego użytkownika, SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe'; że wynagrodzenie Johna Doe'a wynosi dokładnie 100 000 USD. Mimo że istnieje mechanizm zabezpieczający, który uniemożliwia złośliwemu użytkownikowi bezpośrednie wykonywanie zapytań dotyczących wynagrodzeń innych osób, użytkownik może określić, kiedy zapytanie zwraca wyjątek z powodu dzielenia przez zero.

Zgodność między funkcjami

Ogólnie rzecz biorąc, zabezpieczenia na poziomie wiersza będą działać tak, jak się tego oczekuje we wszystkich funkcjach. Istnieje jednak kilka wyjątków. Ta sekcja zawiera kilka uwag i zastrzeżeń dotyczących używania zabezpieczeń na poziomie wiersza z niektórymi innymi funkcjami programu SQL Server.

  • DBCC SHOW_STATISTICS raportuje statystyki dotyczące niefiltrowanych danych i może powodować wyciek informacji chronionych w inny sposób przez zasady zabezpieczeń. Z tego powodu dostęp do wyświetlania obiektu statystyk dla tabeli z zasadami zabezpieczeń na poziomie wiersza jest ograniczony. Użytkownik musi być właścicielem tabeli lub użytkownik musi być członkiem sysadmin stałej roli serwera, db_owner stałej roli bazy danych lub stałej db_ddladmin roli bazy danych.

  • Strumień plików: RLS jest niezgodne z funkcją Filestream.

  • PolyBase: RLS jest obsługiwane dla tabel zewnętrznych w Azure Synapse i SQL Server 2019 CU7 lub nowszych wersjach.

  • Tabele zoptymalizowane pod kątem pamięci: Funkcja tabeli wartości w linii, używana jako predykat zabezpieczeń w tabeli zoptymalizowanej pod kątem pamięci, musi być zdefiniowana przy użyciu opcji WITH NATIVE_COMPILATION. W przypadku tej opcji funkcje językowe nieobsługiwane przez tabele zoptymalizowane pod kątem pamięci zostaną zakazane, a odpowiedni błąd zostanie zgłoszony w czasie tworzenia. Aby uzyskać więcej informacji, zobacz Zabezpieczenia na poziomie wiersza w tabelach zoptymalizowanych pod kątem pamięci.

  • Indeksowane widoki: Ogólnie rzecz biorąc, zasady zabezpieczeń można tworzyć na podstawie widoków, a widoki można tworzyć na podstawie tabel, które są powiązane przez zasady zabezpieczeń. Nie można jednak utworzyć indeksowanych widoków na podstawie tabel z zasadami zabezpieczeń, ponieważ wyszukiwanie wierszy za pośrednictwem indeksu spowoduje obejście zasad.

  • Przechwytywanie zmian danych: Funkcja przechwytywania zmian danych (CDC) może wyciekać całe wiersze, które powinny być filtrowane do członków db_owner lub użytkowników będących członkami roli "gating" określonej, gdy usługa CDC jest włączona dla tabeli. Tę funkcję można jawnie ustawić na NULL, aby umożliwić wszystkim użytkownikom dostęp do danych zmiany. W rezultacie db_owner członkowie tej roli kontrolnej mogą wyświetlać wszystkie zmiany danych w tabeli, pomimo istniejącej polityki bezpieczeństwa.

  • Śledzenie zmian: Śledzenie zmian może ujawniać klucz podstawowy wierszy, które powinny być filtrowane, aby były dostępne tylko dla użytkowników z uprawnieniami SELECT i VIEW CHANGE TRACKING. Rzeczywiste wartości danych nie są ujawniane; tylko fakt, że w kolumnie A dokonano aktualizacji/dodania/usunięcia dla wiersza z określonym kluczem głównym. Jest to problematyczne, jeśli klucz podstawowy zawiera element poufny, taki jak numer ubezpieczenia społecznego. Jednak w praktyce jest to CHANGETABLE prawie zawsze połączone z oryginalną tabelą w celu uzyskania najnowszych danych.

  • Wyszukiwanie pełnotekstowe: Spodziewane obniżenie wydajności zapytań przy użyciu następujących funkcji wyszukiwania pełnotekstowego i semantycznego, ponieważ wprowadzono dodatkowe łączenie w celu zastosowania zabezpieczeń na poziomie wiersza i uniknięcia wycieku kluczy podstawowych wierszy, które powinny być filtrowane: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.

  • Indeksy magazynu kolumn: RLS jest zgodne zarówno z klastrowanym, jak i nieklastrowanym indeksem magazynu kolumn. Ponieważ zabezpieczenia na poziomie wiersza stosują funkcję, optymalizator może zmodyfikować plan zapytania, aby nie korzystał z trybu wsadowego.

  • Widoki partycjonowane: Nie można zdefiniować predykatów bloków w widokach partycjonowanych, a widoki partycjonowane nie mogą być tworzone na podstawie tabel korzystających z predykatów blokowych. Predykaty filtrów są zgodne z widokami podzielonymi na partycje.

  • Tabele czasowe: Tabele czasowe są zgodne z zabezpieczeniami na poziomie wiersza. Jednak predykaty zabezpieczeń w bieżącej tabeli nie są automatycznie replikowane do tabeli historii. Aby zastosować zasady zabezpieczeń zarówno do bieżących, jak i tabel historii, należy osobno dodać predykat zabezpieczeń dla każdej tabeli.

Inne ograniczenia:

  • Usługi Microsoft Fabric i Azure Synapse Analytics obsługują tylko predykaty filtrów. Predykaty blokowe nie są obecnie obsługiwane w usługach Microsoft Fabric i Azure Synapse Analytics.

Examples

A. Scenariusz dla użytkowników, którzy uwierzytelniają się w bazie danych

Ten przykład tworzy trzech użytkowników oraz tworzy tabelę i wypełnia ją sześcioma wierszami. Następnie tworzy funkcję w tabeli wbudowanej i zasady zabezpieczeń dla tabeli. W tym przykładzie pokazano, jak dla różnych użytkowników są filtrowane zapytania select.

Utwórz trzy konta użytkowników, które demonstrują różne możliwości dostępu.

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

Utwórz tabelę do przechowywania danych.

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

Wypełnij tabelę sześcioma wierszami danych, pokazując trzy zamówienia dla każdego przedstawiciela sprzedaży.

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;

Udziel dostępu do odczytu w tabeli każdemu z użytkowników.

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

Utwórz nowy schemat i funkcję wbudowaną w tabelę. Funkcja zwraca 1 , gdy wiersz w SalesRep kolumnie jest taki sam jak użytkownik wykonujący zapytanie (@SalesRep = USER_NAME()) lub jeśli użytkownik wykonujący zapytanie jest użytkownikiem Menedżera (USER_NAME() = 'Manager'). Ten przykład funkcji zwracającej tabelę zdefiniowanej przez użytkownika jest przydatny, aby służyć jako filtr polityki zabezpieczeń utworzonej w następnym 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

Utwórz zasady zabezpieczeń, dodając funkcję jako predykat filtru. Element STATE musi być ustawiony na ON aby włączyć politykę.

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

Zezwalaj na SELECT uprawnienia do tvf_securitypredicate funkcji:

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

Teraz przetestuj predykat filtrowania, wybierając go z Sales.Orders tabeli jako każdy użytkownik.

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;

Menedżer powinien zobaczyć wszystkie sześć rzędów. Użytkownicy Sales1 i Sales2 powinni widzieć tylko własną sprzedaż.

Zmień zasady zabezpieczeń, aby wyłączyć zasady.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Teraz użytkownicy Sales1 i Sales2 mogą zobaczyć wszystkie sześć wierszy.

Połącz się z bazą danych SQL, aby wyczyścić zasoby z tego przykładowego ćwiczenia:

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. Scenariusze używania zabezpieczeń na poziomie wiersza w zewnętrznej tabeli usługi Azure Synapse

Ten krótki przykład tworzy trzech użytkowników i tabelę zewnętrzną z sześcioma wierszami. Następnie tworzy funkcję w tabeli wbudowanej i zasady zabezpieczeń dla tabeli zewnętrznej. W przykładzie pokazano, jak instrukcje select są filtrowane dla różnych użytkowników.

Prerequisites

  1. Musisz mieć dedykowaną pulę SQL. Zobacz Tworzenie dedykowanej puli SQL
  2. Serwer hostujące dedykowaną pulę SQL musi być zarejestrowany w usłudze Microsoft Entra ID (dawniej Azure Active Directory) i musisz mieć konto usługi Azure Storage z uprawnieniami Storage Blog Data Contributor . Postępuj zgodnie z instrukcjami, aby użyć punktów końcowych i reguł usługi sieci wirtualnej dla serwerów w usłudze Azure SQL Database.
  3. Utwórz system plików dla konta usługi Azure Storage. Użyj Eksploratora usługi Azure Storage, aby wyświetlić konto magazynowania. Kliknij prawym przyciskiem myszy kontenery i wybierz polecenie Utwórz system plików.

Po wprowadzeniu wymagań wstępnych utwórz trzy konta użytkowników, które demonstrują różne możliwości dostępu.

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

Utwórz tabelę do przechowywania danych.

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

Wypełnij tabelę sześcioma wierszami danych, pokazując trzy zamówienia dla każdego przedstawiciela sprzedaży.

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;

Utwórz tabelę zewnętrzną usługi Azure Synapse na podstawie utworzonej Sales tabeli.

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;

Przyznaj SELECT trzem użytkownikom w zewnętrznej tabeli Sales_ext, którą utworzyłeś.

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

Utwórz nowy schemat oraz wbudowaną funkcję zwracającą wartości tabelaryczne; być może ukończyłeś/aś to w przykładzie A. Funkcja zwraca 1 w przypadku, gdy wiersz w kolumnie SalesRep odpowiada użytkownikowi wykonującemu zapytanie (@SalesRep = USER_NAME()) lub gdy użytkownik wykonujący zapytanie jest użytkownikiem 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';

Utwórz zasady zabezpieczeń w tabeli zewnętrznej przy użyciu wbudowanej funkcji z wartością tabeli jako predykatu filtru. Element STATE musi być ustawiony na ON aby włączyć politykę.

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

Teraz przetestuj predykat filtrowania, wybierając z tabeli zewnętrznej Sales_ext . Zaloguj się jako każdy użytkownik, Sales1, Sales2i Manager. Uruchom następujące polecenie jako każdy użytkownik.

SELECT * FROM Sales_ext;

Manager powinien zobaczyć wszystkie sześć wierszy. Użytkownicy Sales1 i Sales2 powinni widzieć tylko swoją sprzedaż.

Zmień zasady zabezpieczeń, aby wyłączyć zasady.

ALTER SECURITY POLICY SalesFilter_ext
WITH (STATE = OFF);

Teraz użytkownicy Sales1 i Sales2 mogą zobaczyć wszystkie sześć wierszy.

Połącz się z bazą danych usługi Azure Synapse, aby wyczyścić zasoby z tego przykładowego ćwiczenia:

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;

Połącz się z bazą danych serwera master logicznego, aby wyczyścić zasoby:

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

C. Scenariusz dla użytkowników, którzy łączą się z bazą danych za pośrednictwem aplikacji warstwy środkowej

Note

W tym przykładzie funkcja predykatów blokowych nie jest obecnie obsługiwana w przypadku usług Microsoft Fabric i Azure Synapse, dlatego wstawianie wierszy dla nieprawidłowego identyfikatora użytkownika nie jest blokowane.

W tym przykładzie pokazano, jak aplikacja warstwy środkowej może implementować filtrowanie połączeń, w którym użytkownicy aplikacji (lub najemcy) współużytkują to samo konto użytkownika serwera SQL, które jest związane z aplikacją. Aplikacja ustawia bieżący identyfikator użytkownika aplikacji w SESSION_CONTEXT po nawiązaniu połączenia z bazą danych, a następnie zasady zabezpieczeń niewidocznie filtrują wiersze, które nie powinny być widoczne dla tego identyfikatora, oraz uniemożliwiają użytkownikowi wstawianie wierszy dla nieprawidłowego identyfikatora użytkownika. Nie są konieczne żadne inne zmiany aplikacji.

Utwórz tabelę do przechowywania danych.

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

Wypełnij tabelę sześcioma wierszami danych, pokazując trzy zamówienia dla każdego użytkownika aplikacji.

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

Utwórz użytkownika z niskimi uprawnieniami, którego aplikacja będzie używać do nawiązywania połączenia.

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

Utwórz nowy schemat i funkcję predykatu, które będą używać identyfikatora użytkownika aplikacji przechowywanego w SESSION_CONTEXT() do filtrowania wierszy.

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

Utwórz politykę zabezpieczeń, która dodaje tę funkcję jako predykat filtra oraz predykat blokujący w Sales. Predykat bloku wymaga tylko AFTER INSERT, ponieważ BEFORE UPDATE i BEFORE DELETE są już filtrowane, a AFTER UPDATE jest niepotrzebne, ponieważ kolumna AppUserId nie może być zaktualizowana do innych wartości ze względu na wcześniej ustalone uprawnienia.

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

Teraz możemy zasymulować filtrowanie połączeń, wybierając z Sales tabeli po ustawieniu różnych identyfikatorów użytkowników w pliku SESSION_CONTEXT(). W praktyce aplikacja jest odpowiedzialna za ustawienie bieżącego identyfikatora użytkownika w SESSION_CONTEXT() po otwarciu połączenia. Ustawienie parametru @read_only w celu 1 uniemożliwi ponownego zmiany wartości do momentu zamknięcia połączenia (zwróconego do puli połączeń).

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

Czyszczenie zasobów bazy danych.

DROP USER AppUser;

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

D. Scenariusz użycia tablicy przeglądowej dla predykatu zabezpieczeń

W tym przykładzie użyto tabeli odnośników dla połączenia między identyfikatorem użytkownika a filtrowaną wartością, a nie konieczności określania identyfikatora użytkownika w tabeli faktów. Tworzy trzech użytkowników oraz wypełnia tabelę faktów, Sample.Sales, zawierającą sześć wierszy, oraz tabelę odnośników z dwoma wierszami. Następnie tworzy wbudowaną funkcję o wartości tabeli, która łączy tabelę faktów z tabelą wyszukiwania, aby uzyskać identyfikator użytkownika, i tworzy politykę zabezpieczeń dla tabeli. W tym przykładzie pokazano, jak dla różnych użytkowników są filtrowane zapytania select.

Utwórz trzy konta użytkowników, które demonstrują różne możliwości dostępu.

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

Utwórz schemat i tabelę Sample faktów, Sample.Sales, aby przechowywać dane.

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

Wypełnij Sample.Sales ciąg sześcioma wierszami danych.

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;

Utwórz tabelę do przechowywania danych odnośników — w tym przypadku relacja między Salesrep i Product.

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

Wypełnij tabelę odnośników przykładowymi danymi, łącząc je z Product każdym przedstawicielem handlowym.

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;

Udziel dostępu do odczytu w tabeli faktów każdemu z użytkowników.

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

Utwórz nowy schemat i funkcję typu inline o wartości tabeli. Funkcja zwraca 1, gdy użytkownik wysyła zapytanie do tabeli faktów Sample.Sales, a kolumna SalesRep tabeli Lk_Salesman_Product jest taka sama, jak ta użytkownika wykonującego zapytanie (@SalesRep = USER_NAME()), przy połączeniu z tabelą faktów na kolumnie Product, lub jeśli użytkownik wykonujący zapytanie jest użytkownikiem Manager (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'
                   ) ;

Utwórz zasady zabezpieczeń, dodając funkcję jako predykat filtru. Element STATE musi być ustawiony na ON aby włączyć politykę.

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

Zezwalaj na SELECT uprawnienia do fn_securitypredicate funkcji:

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

Teraz przetestuj predykat filtrowania, wybierając go z Sample.Sales tabeli jako każdy użytkownik.

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;

Manager powinien zobaczyć wszystkie sześć wierszy. Użytkownicy Sales1 i Sales2 powinni widzieć tylko własną sprzedaż.

Zmień zasady zabezpieczeń, aby wyłączyć zasady.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Teraz użytkownicy Sales1 i Sales2 mogą zobaczyć wszystkie sześć wierszy.

Połącz się z bazą danych SQL, aby wyczyścić zasoby z tego przykładowego ćwiczenia:

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. Scenariusz zabezpieczeń na poziomie wiersza w usłudze Microsoft Fabric

Możemy zademonstrować magazyn zabezpieczeń na poziomie wiersza i punkt końcowy analizy SQL w usłudze Microsoft Fabric.

Poniższy przykład tworzy przykładowe tabele, które będą współdziałać z magazynem w usłudze Microsoft Fabric, ale w punkcie końcowym analizy SQL używają istniejących tabel. W punkcie końcowym analityki SQL nie można używać CREATE TABLE, ale można używać CREATE SCHEMA, CREATE FUNCTION i CREATE SECURITY POLICY.

W tym przykładzie najpierw utwórz schemat sales, 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');

Security Utwórz schemat, funkcję Security.tvf_securitypredicatei zasady SalesFilterzabezpieczeń .

-- 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 zastosowaniu zasad zabezpieczeń i utworzeniu funkcji użytkownicy Sales1@contoso.com i Sales2@contoso.com będą mogli zobaczyć własne dane tylko w sales.Orders tabeli, gdzie kolumna SalesRep jest równa własnej nazwie użytkownika zwróconej przez wbudowaną funkcję USER_NAME. Użytkownik Fabric manager@contoso.com może wyświetlić wszystkie dane w sales.Orders tabeli.

Treści powiązane

  • Last updated on