Sicherheit auf Zeilenebene

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics SQL Analytics-Endpunkt in Microsoft Fabric Warehouse in Microsoft Fabric

Dekorative Grafik der Sicherheit auf Zeilenebene.

Mithilfe der Sicherheit auf Zeilenebene (Row-Level Security, RLS) können Sie den Zugriff auf Zeilen in einer Datenbanktabelle anhand der Gruppenmitgliedschaft oder des Ausführungskontexts steuern.

Die Sicherheit auf Zeilenebene vereinfacht das Entwerfen und Programmieren der Sicherheit in Ihrer Anwendung. Mithilfe der Sicherheit auf Zeilenebene (Row-Level Security, RLS) können Sie Einschränkungen in den Datenzeilenzugriff implementieren. Beispielsweise können Sie sicherstellen, dass Mitarbeiter nur auf Datenzeilen zugreifen können, die für ihre Abteilung relevant sind. Ein weiteres Beispiel: Sie können den Zugriff von Kunden auf Daten beschränken, die für ihr Unternehmen von Bedeutung sind.

Die Datenbeschränkungszugriffslogik befindet sich auf der Datenbankebene, statt fern der Daten auf einer anderen Anwendungsebene. Das Datenbanksystem wendet die Zugriffsbeschränkungen bei jedem Zugriffsversuch auf Daten aus einer beliebigen Ebene an. Dadurch bietet Ihr Sicherheitssystem eine geringere Angriffsfläche und ist zuverlässiger und robuster.

Implementieren Sie RLS, indem Sie die Transact-SQL-Anweisung CREATE SECURITY POLICY und Prädikate verwenden, die als Inline-Tabellenwertfunktionen erstellt werden.

Die Sicherheit auf Zeilenebene wurde erstmals in SQL Server 2016 (13.x) eingeführt.

Hinweis

Dieser Artikel konzentriert sich auf SQL Server- und Azure SQL-Plattformen. Informationen zu Microsoft Fabric finden Sie unter Sicherheit auf Zeilenebene in Microsoft Fabric.

Beschreibung

Sicherheit auf Zeilenebene (RLS) unterstützt zwei Arten von Sicherheitsprädikaten:

  • Filterprädikate filtern automatisch die Zeilen, die für Lesevorgänge zur Verfügung stehen (SELECT, UPDATE und DELETE).

  • Blockprädikate blockieren explizit Schreibvorgänge (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE), die das Prädikat verletzen.

Der Zugriff auf Daten auf Zeilenebene in einer Tabelle wird durch ein Sicherheitsprädikat beschränkt, das als Inline-Tabellenwertfunktion definiert ist. Die Funktion wird dann aufgerufen und durch eine Sicherheitsrichtlinie erzwungen. Für Filterprädikate gilt: Der Anwendung ist nicht „bewusst“, dass Zeilen aus dem Resultset herausgefiltert wurden. Wenn alle Zeilen gefiltert wurden, wird eine NULL-Menge zurückgegeben. Für BLOCK-Prädikate gilt: Alle Vorgänge, die gegen das Prädikat verstoßen, misslingen mit einem Fehler.

Filterprädikate werden beim Lesen von Daten aus der Basistabelle angewendet. Sie betreffen alle GET-Vorgänge: SELECT, DELETE und UPDATE. Benutzer können gefilterte Zeilen weder auswählen noch löschen. Der Benutzer kann gefilterte Zeilen nicht aktualisieren. Zeilen können jedoch so aktualisiert werden, dass sie im Anschluss gefiltert werden. BLOCK-Prädikate betreffen alle Schreibvorgänge.

  • Prädikate des Typs AFTER INSERT und AFTER UPDATE können Benutzer am Ändern von Zeilen in Werte hindern, die gegen das Prädikat verstoßen.

  • Prädikate des Typs BEFORE UPDATE können Benutzer am Ändern von Zeilen hindern, die derzeit gegen das Prädikat verstoßen.

  • Prädikate des Typs BEFORE DELETE können Löschvorgänge blockieren.

FILTER- und BLOCK-Prädikate und Sicherheitsrichtlinien weisen folgendes Verhalten auf:

  • Sie können eine Prädikatfunktion definieren, die mit einer anderen Tabelle verknüpft wird und/oder eine Funktion aufruft. Wenn die Sicherheitsrichtlinie mit SCHEMABINDING = ON (Standardeinstellung) erstellt wird, ist die Verknüpfung oder Funktion über die Abfrage erreichbar und funktioniert wie erwartet, ohne dass zusätzliche Berechtigungsüberprüfungen durchgeführt werden zu müssen. Wenn die Sicherheitsrichtlinie mit SCHEMABINDING = OFF erstellt wird, benötigen Benutzer SELECT-Berechtigungen für diese zusätzlichen Tabellen und Funktionen, um die Zieltabelle abfragen zu können. Wenn die Prädikatfunktion eine CLR-Skalarwertfunktion aufruft, wird zusätzlich die EXECUTE-Berechtigung benötigt.

  • Sie können eine Abfrage auf eine Tabelle anwenden, für die ein Sicherheitsprädikat zwar definiert, jedoch deaktiviert ist. Zeilen, die gefiltert oder gesperrt wurden, sind nicht betroffen.

  • Wenn dbo-Benutzer*innen, Mitglieder der db_owner-Rolle oder Tabellenbesitzer*innen eine Tabelle abfragen, für die eine Sicherheitsrichtlinie definiert und aktiviert ist, werden die Zeilen gemäß der definierten Sicherheitsrichtlinie gefiltert oder gesperrt.

  • Versuche, das Schema einer Tabelle zu ändern, die durch eine Sicherheitsrichtlinie an ein Schema gebunden ist, führen zu einem Fehler. Allerdings können vom Prädikat nicht referenzierte Spalten geändert werden.

  • Wenn einer Tabelle ein Prädikat hinzugefügt wird, in der für den angegebenen Vorgang bereits ein Prädikat definiert ist, wird ein Fehler verursacht. Dies geschieht unabhängig davon, ob das Prädikat aktiviert ist oder nicht.

  • Beim Versuch eine Funktion zu ändern, die innerhalb einer schemagebundenen Sicherheitsrichtlinie als Prädikat für eine Tabelle verwendet wird, wird ein Fehler verursacht.

  • Das Definieren mehrerer aktiver Sicherheitsrichtlinien, die nicht überlappende Prädikate enthalten, kann erfolgreich ausgeführt werden.

FILTER-Prädikate weisen folgendes Verhalten auf:

  • Definieren Sie eine Sicherheitsrichtlinie, die die Zeilen einer Tabelle filtert. Die Anwendung beachtet keine Zeilen, die nach SELECT-, UPDATE- und DELETE-Vorgängen gefiltert wurden. Dies gilt selbst dann, wenn alle Zeilen gefiltert wurden. Die Anwendung kann mit INSERT selbst dann Zeilen einfügen, wenn sie bei einem anderen Vorgang gefiltert werden.

BLOCK-Prädikate weisen folgendes Verhalten auf:

  • Blockprädikate für UPDATE werden in getrennte Vorgänge für BEFORE und AFTER unterteilt. So können Sie Benutzer beispielsweise nicht daran hindern, eine Zeile in einen Wert zu ändern, der höher als der aktuelle ist. Wenn diese Art von Logik erforderlich ist, müssen Sie Trigger mit den Zwischentabellen des Typs DELETED und INSERTED verwenden, um gemeinsam auf die alten und neuen Werte zu verweisen.

  • Der Optimierer überprüft kein Blockprädikat des Typs AFTER UPDATE, wenn keine der von der Prädikatfunktion verwendeten Spalten geändert wurde. Beispiel: Alice darf kein Gehalt in einen Wert über 100.000 ändern. Alice kann die Adresse eines Mitarbeiters ändern, dessen Gehalt bereits über 100.000 liegt, solange die Spalten, auf die im Prädikat verwiesen wird, nicht geändert wurden.

  • An den APIs für Massenvorgänge, einschließlich BULK INSERT, sind keine Änderungen erfolgt. Dies bedeutet, dass Blockprädikate des Typs AFTER INSERT für Masseneinfügevorgänge genauso wie für herkömmliche Einfügevorgänge gelten.

Anwendungsfälle

Hier sind Entwurfsbeispiele dazu, wie die Sicherheit auf Zeilenebene (RLS) verwendet werden kann:

  • Für ein Krankenhaus kann eine Sicherheitsrichtlinie definiert werden, die dem Pflegepersonal ausschließlich die Anzeige von Datenzeilen ermöglicht, die sich auf ihre Patienten beziehen.

  • Für eine Bank kann eine Richtlinie definiert werden, die den Zugriff auf Finanzdatenzeilen basierend auf dem Geschäftsbereich oder der Rolle eines Mitarbeiters innerhalb des Unternehmens beschränkt.

  • Eine Anwendung mit mehreren Mandanten kann eine Richtlinie zum Erzwingen einer logischen Trennung der einzelnen Datenzeilen der Mandanten aus jeder anderen Mandanten-Zeile erstellen. Effizienzen werden durch den Datenspeicher für viele Mandanten in einer einzelnen Tabelle erreicht. Jeder Mandant kann nur die eigenen Datenzeilen anzeigen.

RLS-Filterprädikate sind funktional äquivalent zum Anhängen einer WHERE-Klausel. Bei dem Prädikat kann es sich um komplexe Geschäftsabläufe handeln oder die Klausel kann so einfach sein wie das WHERE TenantId = 42.

Formaler ausgedrückt führt RLS eine prädikatbasierte Zugriffssteuerung ein. Sie ermöglicht eine flexible, zentrale und prädikatbasierte Auswertung. Das Prädikat kann auf Metadaten oder beliebigen anderen Kriterien basieren, die der Administrator für geeignet hält. Das Prädikat wird als Kriterium verwendet, um zu bestimmen, ob der Benutzer über die entsprechenden Zugriffsberechtigungen für die Daten basierend auf den Benutzerattributen verfügt. Mithilfe der prädikatbasierten Zugriffssteuerung kann eine bezeichnerbasierte Zugriffssteuerung implementiert werden.

Berechtigungen

Das Erstellen, Ändern oder Löschen von Sicherheitsrichtlinien erfordert die ALTER ANY SECURITY POLICY-Berechtigung. Das Erstellen oder Löschen einer Sicherheitsrichtlinie erfordert die ALTER-Berechtigung für das Schema.

Darüber hinaus sind die folgenden Berechtigungen für jedes hinzugefügte Prädikat erforderlich:

  • SELECT- und REFERENCES-Berechtigungen für die als Prädikat verwendete Funktion.

  • REFERENCES-Berechtigung für die Zieltabelle, die an die Richtlinie gebunden wird.

  • REFERENCES-Berechtigung für jede Spalte in der Zieltabelle, die als Argument verwendet wird.

Sicherheitsrichtlinien gelten für alle Benutzer, einschließlich der Dbo-Benutzer in der Datenbank. Dbo-Benutzer können Sicherheitsrichtlinien ändern oder löschen, ihre Änderungen an Sicherheitsrichtlinien können jedoch überwacht werden. Wenn Benutzer mit ausgedehnten Berechtigungen (z. B. „sysadmin“ oder „db_owner“) alle Zeilen sehen müssen, um Datenprobleme zu beheben oder Daten zu überprüfen, muss die Sicherheitsrichtlinie entsprechend definiert werden.

Wenn eine Sicherheitsrichtlinie mit SCHEMABINDING = OFF erstellt wird, benötigen die Benutzer*innen zum Abfragen der Zieltabelle die SELECT- oder EXECUTE-Berechtigung für die Prädikatfunktion und alle weiteren Tabellen, Sichten oder Funktionen, die innerhalb der Prädikatfunktion verwendet werden. Wenn eine Sicherheitsrichtlinie mit SCHEMABINDING = ON erstellt wird (Standard), werden diese Berechtigungsprüfungen umgangen, wenn Benutzer die Zieltabelle abfragen.

Bewährte Methoden

  • Es wird dringend empfohlen, ein separates Schema für die RLS-Objekte zu erstellen: Prädikatfunktionen und Sicherheitsrichtlinien. So lassen sich die Berechtigungen, die für diese speziellen Objekte erforderlich sind, von den Zieltabellen trennen. Eine weitere Trennung für verschiedene Richtlinien und Prädikatfunktionen ist möglicherweise in mehrinstanzenfähigen Datenbanken erforderlich, aber nicht als Standardeinstellung für jeden Fall.

  • Die ALTER ANY SECURITY POLICY-Berechtigung sollte nur für Benutzer mit erhöhten Berechtigungen (z. B. einen Sicherheitsrichtlinienmanager) verwendet werden. Der Sicherheitsrichtlinienmanager benötigt keine SELECT-Berechtigung für die geschützten Tabellen.

  • Vermeiden Sie Konvertierungen in Prädikatfunktionen, um potenzielle Laufzeitfehler zu vermeiden.

  • Vermeiden Sie nach Möglichkeit Rekursionen in Prädikatfunktionen, um Leistungseinbußen zu vermeiden. Der Abfrageoptimierer versucht, direkte Rekursionen zu erkennen. Indirekte Rekursionen werden jedoch nicht zuverlässig gefunden. Eine indirekte Rekursion liegt vor, wenn eine zweite Funktion die Prädikatfunktion aufruft.

  • Vermeiden Sie übermäßige Tabellenverknüpfungen Prädikatfunktionen, um die Leistung zu maximieren.

Vermeiden Sie Prädikatlogik, die von sitzungsspezifischen SET-Optionen abhängt: Wenngleich ihre Verwendung in der Praxis eher unwahrscheinlich ist, können Prädikatfunktionen, deren Logik von bestimmten sitzungsspezifischen SET-Optionen abhängt, Informationen preisgeben, wenn Benutzer in der Lage sind, beliebige Abfragen auszuführen. Beispiel: Eine Prädikatfunktion, die eine Zeichenfolge implizit in datetime konvertiert, kann unterschiedliche Zeilen basierend auf der Option SET DATEFORMAT für die aktuelle Sitzung filtern. Im Allgemeinen sollten Prädikatfunktionen die folgenden Regeln einhalten:

Sicherheitshinweis: Seitenkanalangriffe

Schädliche Sicherheitsrichtlinien-Manager

Es ist wichtig zu beachten, dass ein schädlicher Sicherheitsrichtlinien-Manager mit ausreichenden Berechtigungen zum Erstellen einer Sicherheitsrichtlinie auf eine vertrauliche Spalte und der Berechtigung zum Erstellen oder Ändern von Inline-Tabellenwertfunktionen mit einem anderen Benutzer zusammenwirken kann, der über ausgewählte Berechtigungen für eine Tabelle verfügt, um eine Datenexfiltration durchzuführen, indem schädliche Inline-Tabellenwertfunktionen erstellt werden, die dazu dienen sollen, Seitenkanalangriffe zu verwenden, um Daten abzuleiten. Solche Angriffe sind möglich, wenn sich Benutzer abgesprochen haben, oder wenn einem böswilligen Benutzer zu hohe Berechtigungen erteilt wurden. Zudem sind vermutlich mehrere Iterationen zum Anpassen der Richtlinie vonnöten. Dazu sind Berechtigungen zum Entfernen das Prädikats erforderlich, um die Schemabindung aufheben zu können. Gleichzeitig müssen die Inline-Tabellenwertfunktionen angepasst und SELECT-Anweisungen wiederholt für die Zieltabelle ausgeführt werden. Es wird empfohlen, Berechtigungen nach Bedarf zu beschränken und das System auf verdächtige Aktivitäten zu überwachen. Aktivitäten wie das ständige Ändern von Richtlinien und Inline-Tabellenwertfunktionen im Zusammenhang mit der Sicherheit auf Zeilenebene sollten überwacht werden.

Sorgfältig erstellte Abfragen

Durch sorgfältig erstellte Abfragen, die Fehler für die Datenexfiltration verwenden, können u. U. Informationsverluste verursacht werden. Beispiel: SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe'; würde einen schädlichen Benutzer oder eine schädliche Benutzerin wissen lassen, dass das Gehalt von John Doe genau 100.000 USD beträgt. Auch wenn ein Sicherheitsprädikat eingerichtet ist, um zu verhindern, dass ein schädlicher Benutzer die Gehälter anderer Personen direkt abfragen kann, kann der Benutzer bestimmen, wann die Abfrage eine Division-durch-Null-Ausnahme zurückgibt.

Feature-übergreifende Kompatibilität

Im Allgemeinen funktioniert Sicherheit auf Zeilenebene featureübergreifend wie erwartet. Es gibt jedoch einige Ausnahmen. In diesem Abschnitt finden Sie verschiedene Hinweise und Vorsichtsmaßnahmen bei Verwenden von Sicherheit auf Zeilenebene mit bestimmten anderen Features von SQL Server.

  • DBCC SHOW_STATISTICS meldet Statistiken zu ungefilterten Daten, was zur Offenlegung von Informationen führen kann, die ansonsten durch eine Sicherheitsrichtlinie geschützt sind. Aus diesem Grund ist die Anzeige eines Statistikobjekts für eine Tabelle mit einer Richtlinie für Sicherheit auf Zeilenebene beschränkt. Der Benutzer muss die Tabelle besitzen oder Mitglied der festen Serverrollen sysadmin, der festen Datenbankrolle db_owner oder der festen Datenbankrolle db_ddladmin sein.

  • Filestream: RLS ist nicht kompatibel mit Filestream.

  • PolyBase: RLS wird mit externen Tabellen in Azure Synapse und SQL Server 2019 CU7 oder höheren Versionen unterstützt.

  • Speicheroptimierte Tabellen: Die Inline-Tabellenwertfunktion, die für ein Sicherheitsprädikat für eine speicheroptimierte Tabelle verwendet wird, muss mit der Option WITH NATIVE_COMPILATION definiert werden. Bei dieser Option werden von speicheroptimierten Tabellen nicht unterstützte Sprachfeatures gesperrt, und zur Erstellungszeit wird der entsprechende Fehler ausgelöst. Weitere Informationen finden Sie unter Sicherheit auf Zeilenebene in speicheroptimierten Tabellen.

  • Indizierte Sichten: Im allgemeinen können Sicherheitsrichtlinien basierend auf Sichten erstellt werden. Sichten können basierend auf Tabellen erstellt werden, die durch Sicherheitsrichtlinien gebunden sind. Allerdings können indizierte Sichten nicht basierend auf Tabellen erstellt werden, für die eine Sicherheitsrichtlinie gilt, da die Richtlinie bei Zeilensuchvorgängen über den Index umgangen würde.

  • Change Data Capture: Change Data Capture (CDC) kann ganze Zeilen preisgeben, die für Mitglieder von db_owner oder Benutzer gefiltert werden sollen, die Mitglieder der „Gating-Rolle“ sind, die angegeben wird, wenn CDC für eine Tabelle aktiviert ist. Sie können diese Funktion explizit auf NULL festlegen, damit alle Benutzer auf die Änderungsdaten zugreifen können. Im Endeffekt können db_owner und Mitglieder dieser Gating-Rolle alle Datenänderungen für eine Tabelle anzeigen, auch wenn für die Tabelle eine Sicherheitsrichtlinie gilt.

  • Änderungen: Die Änderungsnachverfolgung kann zur Offenlegung des Primärschlüssels von Zeilen führen, die für Benutzer mit den Berechtigungen SELECT und VIEW CHANGE TRACKING gefiltert werden sollen. Tatsächliche Datenwerte werden nicht preisgegeben, sondern nur dass Spalte A für die Spalte mit einem bestimmten Primärschlüssel aktualisiert/eingefügt/gelöscht wurde. Dies ist problematisch, wenn der primäre Schlüssel ein vertrauliches Element enthält, z. B. eine US-Sozialversicherungsnummer. In der Praxis ist CHANGETABLE jedoch fast immer mit der ursprünglichen Tabelle verknüpft, um die neuesten Daten abzurufen.

  • Volltextsuche: Eine Leistungsverschlechterung wird für Abfragen erwartet, die die folgenden Volltextsuch- und semantischen Suchfunktionen verwenden. Der Grund ist ein zusätzlicher Join-Vorgang, der für das Aktivieren der Sicherheit auf Zeilenebene und Vermeiden der Preisgabe der Primärschlüssel von Zeilen erfolgt, die gefiltert werden sollen: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.

  • Columnstore-Indizes: RLS ist sowohl mit gruppierten als auch nicht gruppierten Columnstore-Indizes kompatibel. Da für die Sicherheit auf Zeilenebene jedoch eine Funktion angewendet wird, ist es möglich, dass der Optimierer den Abfrageplan so ändert, dass nicht der Batchmodus verwendet wird.

  • Partitionierte Sichten: BLOCK-Prädikate können nicht für partitionierte Sichten definiert werden, und partitionierte Sichten können nicht basierend auf Tabellen erstellt werden, die BLOCK-Prädikate verwenden. FILTER-Prädikate sind kompatibel mit partitionierten Sichten.

  • Temporale Tabellen: Temporale Tabellen sind mit RLS kompatibel. Sicherheitsprädikate für die aktuelle Tabelle werden jedoch nicht automatisch in die Verlaufstabelle repliziert. Um eine Sicherheitsrichtlinie auf die aktuellen und Verlaufstabellen anzuwenden, müssen Sie für jede Tabelle ein Sicherheitsprädikat einzeln hinzufügen.

Weitere Einschränkungen:

  • Microsoft Fabric und Azure Synapse Analytics unterstützen nur Filterprädikate. Blockprädikate werden derzeit in Microsoft Fabric und Azure Synapse Analytics nicht unterstützt.

Beispiele

A. Szenario für Benutzer, die sich bei der Datenbank authentifizieren

In diesem Beispiel werden drei Benutzer und eine Tabelle erstellt, die sechs Zeilen enthält. Anschließend werden eine Inline-Tabellenwertfunktion und eine Sicherheitsrichtlinie für die Tabelle erstellt. Im Beispiel wird gezeigt, wie ausgewählte Anweisungen für verschiedene Benutzer gefiltert werden.

Erstellen Sie drei Benutzerkonten, anhand derer unterschiedliche Zugriffsmöglichkeiten vorgeführt werden.

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

Erstellen Sie eine Tabelle zum Speichern von Daten.

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

Füllen Sie die Tabelle mit sechs Datenzeilen auf, sodass drei Bestellungen pro Vertriebsmitarbeiter enthalten sind.

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;

Gewähren Sie den Benutzern Lesezugriff auf die Tabelle.

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

Erstellen Sie ein neues Schema und eine Inline-Tabellenwertfunktion. Die Funktion gibt 1 zurück, wenn eine Zeile in der Spalte SalesRep dem Benutzer entspricht, der die Abfrage ausführt (@SalesRep = USER_NAME()) oder wenn der Benutzer, der die Abfrage ausführt, der Manager-Benutzer ist (USER_NAME() = 'Manager'). Dieses Beispiel für eine benutzerdefinierte Tabellenwertfunktion ist nützlich als Filter für die im nächsten Schritt erstellte Sicherheitsrichtlinie.

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

Erstellen Sie eine Sicherheitsrichtlinie, die die Funktion als Filterprädikat hinzufügt. Der STATE muss auf ON festgelegt werden, um die Richtlinie zu aktivieren.

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

Lassen Sie SELECT-Berechtigungen für die Funktion tvf_securitypredicate zu:

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

Testen Sie jetzt das Filterungsprädikat, indem Sie sie als jeweiliger Benutzer aus der Sales.Orders-Tabelle auswählen.

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;

Dem Manager sollten alle sechs Zeilen angezeigt werden. Den Benutzern Sales1 und Sales2 sollten nur ihre eigenen Verkäufe angezeigt werden.

Ändern Sie die Sicherheitsrichtlinie, um die Richtlinie zu deaktivieren.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Jetzt können die Benutzer Sales1 und Sales2 alle sechs Zeilen sehen.

Stellen Sie anhand dieses Beispiels eine Verbindung mit der SQL-Datenbank her, um Ressourcen zu bereinigen:

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. Szenarios für die Verwendung von Sicherheit auf Zeilenebene in einer externen Azure Synapse-Tabelle

In diesem kurzen Beispiel werden drei Benutzer und eine externe Tabelle mit sechs Zeilen erstellt. Anschließend werden eine Inline-Tabellenwertfunktion und eine Sicherheitsrichtlinie für die externe Tabelle erstellt. Im Beispiel wird gezeigt, wie ausgewählte Anweisungen für verschiedene Benutzer gefiltert werden.

Voraussetzungen

  1. Sie besitzen einen dedizierten SQL-Pool. Siehe Erstellen eines dedizierten SQL-Pools.
  2. Der Server, auf dem Ihr dedizierter SQL-Pool gehostet wird, muss mit Microsoft Entra ID (vormals Azure Active Directory) registriert werden, und Sie müssen über ein Azure-Speicherkonto mit Storage Blog Data Contributor-Berechtigungen verfügen. Befolgen Sie die Schritte zum Verwenden von Virtual Network-Dienstendpunkten und -Regeln für Server in Azure SQL-Datenbank.
  3. Erstellen Sie ein Dateisystem fürIhr Azure Storage-Konto. Zeigen Sie mit Azure Storage Explorer Ihr Speicherkonto an. Klicken Sie mit der rechten Maustaste auf Container und wählen Sie Dateisystem erstellen aus.

Sobald Sie die Voraussetzungen eingerichtet haben, erstellen Sie drei Benutzerkonten, anhand derer unterschiedliche Zugriffsmöglichkeiten gezeigt werden.

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

Erstellen Sie eine Tabelle zum Speichern von Daten.

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

Füllen Sie die Tabelle mit sechs Datenzeilen auf, sodass drei Bestellungen pro Vertriebsmitarbeiter enthalten sind.

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;

Erstellen Sie aus der Sales-Tabelle, die Sie gerade erstellt haben, eine externe Azure Synapse-Tabelle.

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;

Erteilen Sie den drei Benutzern SELECT-Berechtigungen für die externe Tabelle Sales_ext, die Sie erstellt haben.

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

Erstellen Sie ein neues Schema und eine Inline-Tabellenwertfunktion. In Beispiel A haben Sie diesen Vorgang möglicherweise bereits abgeschlossen. Die Funktion gibt 1 zurück, wenn eine Zeile in der Spalte SalesRep dem Benutzer entspricht, der die Abfrage ausführt (@SalesRep = USER_NAME()), oder wenn der Benutzer, der die Abfrage ausführt, der Manager-Benutzer ist (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';

Erstellen Sie eine Sicherheitsrichtlinie für die externe Tabelle, wobei Sie die Inline-Tabellenwertfunktion als Filterprädikat verwenden. Der STATE muss auf ON festgelegt werden, um die Richtlinie zu aktivieren.

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

Testen Sie jetzt das Filterprädikat, indem Sie in der externen Sales_ext-Tabelle eine Auswahl treffen. Melden Sie sich als jeweiliger Benutzer, Sales1, Sales2 und Manager an. Führen Sie den folgenden Befehl als jeweiliger Benutzer aus.

SELECT * FROM Sales_ext;

Dem Manager sollten alle sechs Zeilen angezeigt werden. Die Benutzer Sales1 und Sales2 sollten nur ihre eigenen Verkaufsdaten sehen.

Ändern Sie die Sicherheitsrichtlinie, um die Richtlinie zu deaktivieren.

ALTER SECURITY POLICY SalesFilter_ext
WITH (STATE = OFF);

Jetzt werden den Benutzern Sales1 und Sales2 alle sechs Zeilen angezeigt.

Stellen Sie anhand dieses Beispiels eine Verbindung mit der Azure Synapse-Datenbank her, um Ressourcen zu bereinigen:

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;

Stellen Sie eine Verbindung mit der master-Datenbank des logischen Servers her, um Ressourcen zu bereinigen:

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

C. Szenario für Benutzer, die sich über eine Middle-Tier Application mit der Datenbank verbinden

Hinweis

In diesem Beispiel wird die Funktionalität zum Blockieren von Prädikaten für Microsoft Fabric und Azure Synapse momentan nicht unterstützt, sodass das Einfügen von Zeilen für die falsche Benutzer-ID nicht blockiert wird.

Dieses Beispiel zeigt, wie eine Anwendung der mittleren Schicht eine Verbindungsfilterung implementieren kann, bei der Anwendungsbenutzer (oder Mandanten) den gleichen SQL Server-Benutzer (die Anwendung) gemeinsam verwenden. Die Anwendung legt nach dem Verbinden mit der Datenbank die aktuelle Anwendungsbenutzer-ID in SESSION_CONTEXT fest. Dann sorgen Sicherheitsrichtlinien dafür, dass Zeilen transparent herausgefiltert werden, die für diese ID nicht sichtbar sein sollen. Außerdem wird der Benutzer am Einfügen von Zeilen für die falsche Benutzer-ID gehindert. Es sind keine weiteren App-Änderungen erforderlich.

Erstellen Sie eine Tabelle zum Speichern von Daten.

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

Füllen Sie die Tabelle mit sechs Datenzeilen auf, sodass drei Bestellungen pro Anwendungsbenutzer enthalten sind.

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

Erstellen Sie ein Benutzerkonto mit geringen Berechtigungen, das die Anwendung zum Herstellen der Verbindung verwendet.

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

Erstellen Sie ein neues Schema und eine Prädikatfunktion, die die Anwendungsbenutzer-ID verwendet, die in SESSION_CONTEXT() zum Filtern von Zeilen gespeichert ist.

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

Erstellen Sie eine Sicherheitsrichtlinie, die diese Funktion als FILTER-Prädikat und BLOCK-Prädikat für Saleshinzufügt. Das Blockprädikat benötigt nur AFTER INSERT, da BEFORE UPDATE und BEFORE DELETE bereits gefiltert sind und AFTER UPDATE unnötig ist, da die Spalte AppUserId aufgrund von zuvor festgelegten Spaltenberechtigungen nicht in andere Werte geändert werden kann.

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

Nun können wir die Verbindungsfilterung durch Auswahl der Tabelle Sales simulieren, nachdem in SESSION_CONTEXT() andere Benutzer-IDs festgelegt wurden. In der Praxis ist die Anwendung nach Öffnen einer Verbindung zuständig für das Festlegen der aktuellen Benutzer-ID in SESSION_CONTEXT(). Durch Festlegen des @read_only-Parameters auf 1 wird verhindert, dass der Wert wieder geändert wird, bis die Verbindung geschlossen wird (an den Verbindungspool zurückgegeben).

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

Bereinigen Sie Datenbankressourcen.

DROP USER AppUser;

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

D: Szenario zur Verwendung einer Nachschlagetabelle für das Sicherheitsprädikat

In diesem Beispiel wird eine Nachschlagetabelle für den Link zwischen der Benutzer-ID und dem zu filternden Wert verwendet, statt die Benutzer-ID in der Faktentabelle angeben zu müssen. Es werden erstellt: drei Benutzer, eine Faktentabelle, Sample.Sales, mit sechs Zeilen und eine Nachschlagetabelle mit zwei Zeilen. Anschließend wird eine Inline-Tabellenwertfunktion erstellt, die die Faktentabelle mit der Suche verknüpft, um die Benutzer-ID und eine Sicherheitsrichtlinie für die Tabelle abzurufen. Im Beispiel wird gezeigt, wie ausgewählte Anweisungen für verschiedene Benutzer gefiltert werden.

Erstellen Sie drei Benutzerkonten, anhand derer unterschiedliche Zugriffsmöglichkeiten vorgeführt werden.

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

Erstellen Sie ein Sample-Schema und eine Faktentabelle, Sample.Sales, zur Aufnahme von Daten.

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

Füllen Sie Sample.Sales mit sechs Datenzeilen auf.

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;

Erstellen Sie eine Tabelle zur Aufnahme der Suchdaten – in diesem Fall eine Beziehung zwischen Salesrep und Product.

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

Füllen Sie die Nachschlagetabelle mit Beispieldaten auf, wobei Sie ein einziges Product mit jedem Vertriebsmitarbeiter verknüpfen.

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;

Gewähren Sie jedem der Benutzer Lesezugriff auf die Faktentabelle.

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

Erstellen Sie ein neues Schema und eine Inline-Tabellenwertfunktion. Die Funktion gibt 1 zurück, wenn ein Benutzer die Faktentabelle Sample.Sales abfragt und die Spalte SalesRep der Tabelle Lk_Salesman_Product dem Benutzer entspricht, der die Abfrage ausführt (@SalesRep = USER_NAME()), wenn er mit der Faktentabelle in der Spalte Product verknüpft ist oder wenn der Benutzer, der die Abfrage ausführt, der Manager-Benutzer ist (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'
                   ) ;

Erstellen Sie eine Sicherheitsrichtlinie, die die Funktion als Filterprädikat hinzufügt. Der STATE muss auf ON festgelegt werden, um die Richtlinie zu aktivieren.

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

Lassen Sie SELECT-Berechtigungen für die Funktion fn_securitypredicate zu:

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

Testen Sie jetzt das Filterungsprädikat, indem Sie sie als jeweiliger Benutzer aus der Sample.Sales-Tabelle auswählen.

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;

Dem Manager sollten alle sechs Zeilen angezeigt werden. Den Benutzern Sales1 und Sales2 sollten nur ihre eigenen Verkäufe angezeigt werden.

Ändern Sie die Sicherheitsrichtlinie, um die Richtlinie zu deaktivieren.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Jetzt können die Benutzer Sales1 und Sales2 alle sechs Zeilen sehen.

Stellen Sie anhand dieses Beispiels eine Verbindung mit der SQL-Datenbank her, um Ressourcen zu bereinigen:

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. Szenario der Sicherheit auf Zeilenebene in Microsoft Fabric

Wir können die Sicherheit auf Zeilenebene für Warehouse und SQL Analytics-Endpunkte in Microsoft Fabric veranschaulichen.

Im folgenden Beispiel werden Beispieltabellen erstellt, die mit Warehouse in Microsoft Fabric funktionieren, aber in SQL Analytics-Endpunkten vorhandene Tabellen verwenden. Im SQL Analytics-Endpunkt können Sie nicht CREATE TABLE, aber CREATE SCHEMA, CREATE FUNCTION und CREATE SECURITY POLICY verwenden.

Erstellen Sie in diesem Beispiel zunächst das Schema sales und die Tabelle 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');

Erstellen Sie das Schema Security, die Funktion Security.tvf_securitypredicate und die Sicherheitsrichtlinie SalesFilter.

-- Creating schema for Security
CREATE SCHEMA Security;
GO

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

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

Nachdem Sie die Sicherheitsrichtlinie angewendet und die Funktion erstellt haben, können die Benutzer Sales1@contoso.com und Sales2@contoso.com nur ihre eigenen Daten in der sales.Orders-Tabelle sehen, wobei die Spalte SalesRep ihrem eigenen Benutzernamen entspricht, der von der integrierten Funktion USER_NAME zurückgegeben wird. Der Fabric-Benutzer manager@contoso.com kann alle Daten in der sales.Orders-Tabelle sehen.

Zugehöriger Inhalt