Sicherheit auf Zeilenebene automatisch konfigurieren

Berechtigungen auf Datensatzebene sind für Business Intelligence und Data Warehousing so notwendig wie heikel und müssen penibel gehandhabt werden. Microsoft SQL Server regelt Zugriffsbeschränkungen mit integrierten Berechtigungsrichtlinien (Row-level Security, RLS. Unser Bissantz Application Designer konfiguriert den Zugriffsschutz automatisch – denn Automation vermeidet manuelle Fehler und geht schneller.

Schon in einem der ersten Blogbeiträge der Bissantz-Crew-Reihe aus dem Jahr 2009 wurde beschrieben, wie mit SQL-Bordmitteln das Lesen von bestimmten Inhalten in einer Tabelle verhindert werden kann. Aufbauend auf dieser Darstellung wird in diesem Artikel beschrieben, wie mit den integrierten Berechtigungsrichtlinien des Microsoft SQL-Servers (ab Version 2016) eine Zugriffsbeschränkung konfiguriert werden kann.

In der Praxis kommt es häufig vor, dass man nur Daten aus einer Tabelle lesen darf, die für einen selbst bestimmt sind; Daten aus derselben Tabelle mit Inhalten für andere Personen sollen jedoch nicht eingesehen, geändert oder gelöscht werden. Ein Kostenstellenverantwortlicher darf z. B. die Finanzdaten eines Unternehmens nur für seine Kostenstelle(n) einsehen, hingegen nicht andere, wie z. B. die Lohn- und Gehaltskostenstelle.

Ein anderes Beispiel könnte eine Datenbankanwendung sein, die für mehrere Mandanten konzipiert ist. Ein Mandant soll immer nur seine Stamm- und Bewegungsdaten sehen dürfen, es sollen aber nicht für jeden Mandaten extra Datenbanken/Tabellen angelegt werden. Wir benötigten für dieses einfache Beispiel drei Tabellen:

1. Eine Mandatentabelle, in der die Mandanten aufgeführt werden,
2. eine Benutzertabelle, in der die Anwender je Mandant angelegt werden können und
3. eine (einfache) Eingabetabelle zum Testen der Sicherheitseinstellung (hier T_S_Language).

Das Anlegen der Tabelle (und später auch der Berechtigungsobjekte) überlassen wir dem Bissantz Application Designer. Mit diesem mächtigen Werkzeug können sehr schnell komplexe Datenbankanwendungen erstellt und verwaltet werden.

Wir definieren also 3 Tabellen (vergl. Abb. 1) mit den dazugehörigen Spalten (Abb. 2).

Abb. 1: Definition von 3 Beispieltabellen im Bissantz Application Designer

Nachdem der Application Designer die drei Tabellen erstellt hat, sind sie im SQL-Management-Studio sichtbar und können mit Beispieldaten befüllt werden.
Für dieses kleine Beispiel legen wir in der Tabelle T_S_Client zwei Mandanten und in der Tabelle T_S_User für jeden Mandanten einen Benutzer an (vgl. Abb. 3).

Abb. 2: Spaltendefinition der drei Beispieltabellen im Bissantz Application Designer

Abb. 3: Inhalt der Tabellen T_S_Client (2 Mandaten) und T_S_User (2 Benutzer)

Die zwei Benutzer müssen auch physisch auf der Datenbank angelegt werden. Dies geht mit folgenden SQL-Befehlen:

CREATE LOGIN UserClient1 WITH PASSWORD = 'hier kommt das PWD rein'
CREATE USER UserClient1 FOR LOGIN UserClient1 WITH DEFAULT_SCHEMA=[dbo]

CREATE LOGIN UserClient2 WITH PASSWORD = 'hier kommt das PWD rein'
CREATE USER UserClient2 FOR LOGIN UserClient2 WITH DEFAULT_SCHEMA=[dbo]

Hinweis: Das Anlegen eines “Login”, bevor der Benutzer angelegt wird, ist erforderlich, damit in der später notwendigen Abfrage des Benutzernamens über „SELECT SYSTEM_USER“ der Namen zurückgegeben wird und nicht eine zufällige SID.

Es kann auch mit der integrierten Windows-Authentifizierung gearbeitet werden. Dies wird aber in diesem Beispiel nicht beschrieben.

Als nächstes benötigen wir eine Inline-Tabellenwertfunktion, die 1 zurückgibt, wenn der Benutzer den Mandanten sehen darf. Den aktuellen Benutzer kann man, wie im Hinweis schon erwähnt, über „SYSTEM_USER“ abfragen. Die Funktion wird in einem separaten Schema „[RowLevelSecurity]“ angelegt:

Create FUNCTION [RowLevelSecurity].[F_Filter_dbo_T_S_Language_by_dbo_T_S_User](@ClientID INT)
RETURNS TABLE  
WITH SCHEMABINDING  
AS  
RETURN
(
    SELECT 1 AS RowGranted
    FROM [dbo].[T_S_User]
    WHERE ([ClientID] = @ClientID AND [Username] = SYSTEM_USER)
); 

Die entscheidende Komponente für die Berechtigungssteuerung ist eine Sicherheitsrichtline, die die Funktion als Filterprädikat enthält.

CREATE SECURITY POLICY [RowLevelSecurity].[SECPOL_dbo_T_S_Status_Language_by_dbo_T_S_User] 
ADD FILTER PREDICATE [RowLevelSecurity].[F_Filter_dbo_T_S_Status_Language_by_dbo_T_S_User]([ClientID]) ON [dbo].[T_S_Status_Language]
WITH (STATE = ON, SCHEMABINDING = ON)
NOT FOR REPLICATION

Das Filterprädikat schützt die Tabelle bei Lesevorgängen, weil automatisch nur die zugewiesenen Zeilen angezeigt werden. Um die Tabelle auch vor unerlaubten Schreibvorgängen zu schützen (z. B. Update auf eine Zeile mit einem fremden Mandanten), müssen diese explizit auch verboten werden. Daher wird die obige Sicherheitsrichtlinie um Blockprädikate ergänzt:

ALTER SECURITY POLICY [RowLevelSecurity].[SECPOL_dbo_T_S_Status_Language_by_dbo_T_S_User] 

ALTER BLOCK PREDICATE [RowLevelSecurity].[F_Filter_dbo_T_S_Status_Language_by_dbo_T_S_User]([ClientID]) ON [dbo].[T_S_Status_Language] AFTER INSERT,
ALTER BLOCK PREDICATE [RowLevelSecurity].[F_Filter_dbo_T_S_Status_Language_by_dbo_T_S_User]([ClientID]) ON [dbo].[T_S_Status_Language] AFTER UPDATE,
ALTER BLOCK PREDICATE [RowLevelSecurity].[F_Filter_dbo_T_S_Status_Language_by_dbo_T_S_User]([ClientID]) ON [dbo].[T_S_Status_Language] BEFORE UPDATE,
ALTER BLOCK PREDICATE [RowLevelSecurity].[F_Filter_dbo_T_S_Status_Language_by_dbo_T_S_User]([ClientID]) ON [dbo].[T_S_Status_Language] BEFORE

Wenn die Funktion und die Sicherheitsrichtlinie angelegt sind, kann das Konzept getestet werden. Beim Testen nutzen wir den SQL-Befehl „EXECUTE AS USER = 'Username'“, um den Benutzer zu wechseln. Mit dem „REVERT“-Befehl gelangt man zum ursprünglichen Benutzer mit Administrationsrechten zurück.

In unserem Beispiel hat die Tabelle T_S_Language drei Einträge, zwei für Mandant 1 und einen Eintrag für Mandant 2 (vgl. Abb. 4).

Abb. 4: Inhalt der Tabellen T_S_Language

Fragen wir den Inhalt der Tabelle mit einem „SELECT *“ ab, erhalten wir als Admin-Benutzer jedoch kein Ergebnis zurück. Woher kommt das? Die Sicherheitsrichtlinien gelten sofort, wenn sie angelegt werden und in der Tabelle T_S_User wurden bisher nur die zwei Testuser angelegt und nicht der aktuelle Admin-Benutzer. Die Berechtigungssteuerung funktioniert also schon!

Es gibt drei Möglichkeiten, dem Admin-Benutzer den Inhalt der Tabelle anzeigen zu lassen:

a)  Der Benutzer könnte ebenfalls in die T_S_User-Tabelle eingetragen werden.
b)  Die Inline-Tabellenwertfunktion wird um den Benutzer erweitert.¹
c)   Zum Testen kann die Sicherheitsrichtlinie ausgeschalten werden. Das geht über ein SQL_Statement² oder einfacher über das Kontextmenü der Sicherheitsrichtline im SQL-Managementstudio (vgl. Abb. 5).

Abb. 5: Inhalt der Tabellen T_S_Language, Sicherheitsrichtlinie ausgeschalten.

Nun sollen die neu angelegten Benutzer getestet werden. Der erste Test (vgl. Abb. 6) scheitert aber schon, weil die neuen Benutzer noch gar keine Berechtigung auf die Tabelle haben. Die Benutzer benötigen noch Zugriffsrechte auf die Tabelle (oder gleich auf das ganze dbo-Schema, siehe Abb. 7; der SQL-Befehl funktioniert natürlich nur, nachdem mit „REVERT“ auf den ursprünglichen Benutzer zurückgewechselt wurde).

Abb. 6: Abfrage scheitert an fehlenden Berechtigungen

Abb. 7: Die neuen Benutzer bekommen grundlegende Lese- und Schreibberechtigungen auf das dbo-Schema.

Der erneute Test mit den zwei Benutzern liefert nun das gewünschte Ergebnis: Benutzer 1 sieht nur Einträge des Mandanten 1, Benutzer 2 sieht nur Einträge des Mandanten 2 (vgl. Abb. 8).

Abb. 8: Dieselbe Abfrage mit den unterschiedlichen Benutzern ergibt das gewünschte Ergebnis.

Das kleine Beispiel zeigt, wie schnell eine Tabelle und alle davon abhängigen Objekte (Sichten, Prozeduren etc.) in wenigen Schritten vor falschen Zugriffen geschützt werden kann. Noch rascher geht das, wenn die Datenbank und die Tabellen mit dem Bissantz Application Designer generiert werden. Hierbei wird die benötigte Funktion und die benötigte Sicherheitsrichtline automatisch angelegt, wenn folgende Einstellungen gemacht werden.

Schritt 1: In der Zuordnungstabelle Benutzername zu Sicherungsobjekt (im obigen Beispiel die Tabelle „T_S_User“ mit dem Mandant („ClientID“) als Sicherungsobjekt) wird die Spalte mit dem Usernamen mit dem Spaltentyp „User Name (Row-Level Security)“ gekennzeichnet (s. Abb. 9).

Abb. 9: Markierung der „User Name“-Spalte im DeltaMaster Application Designer.

Schritt 2: Nun ist es möglich, in allen zu schützenden Tabellen per Drop-Down-Box in der Spalte „Row-Level-Security Object“ die Tabelle T_S_User auszuwählen und die Zuordnungsspalte (hier „ClientID“) als „Row-Level Security Column“ zu bestimmen (vgl. Abb. 10).

Abb. 10: Auswahl des „Row-Level Security Object” und der “Row-Level Security Column” im Application Designer.

Hinweis 1: Die Tabelle T_S_Client lässt sich so auch vor Zugriff schützen. Damit kann kein Benutzer einen neuen Mandanten anlegen (und nur seinen eigenen sehen).

Hinweis 2: Die Tabelle T_S_User ist mit dieser Vorgehensweise über den Application Designer jedoch nicht automatisch geschützt. Diese muss, wie oben beschrieben, mit einer eigenen Sicherheitsrichtlinie manuell im „DDL Object“-Teil des Application Designer (zu finden im Ordner „Programmability“) geschützt werden.

Hinweis 3: Eine weitere Möglichkeit im Application Designer, zeilenbasierte Berechtigungen einzurichten, ist der Weg über eine selbst erstellte Filterfunktion. Diese legt man als DDL-Object an (siehe Abb. 10) und weist sie dann der Spalte (oder den Spalten) in der „Column“-Tabelle zu (siehe Abb. 11). Die Sicherheitsrichtlinie dazu wird dann automatisch erstellt.

Abb. 11: Eine selbst erstellte Filterfunktion im DDL-Object-Teil…

Abb. 12: …kann auch als „Row-Level Security Object” verwendet werden. Die RLS-Column-Spalte bleibt frei.

¹ SQL-Statment um „WHERE [ClientID] = @ClientID AND ([Username] = SYSTEM_USER OR [Username] = 'ADMIN-Username')

² SQL-Statement: ALTER SECURITY POLICY [RowLevelSecurity].[SECPOL_dbo_T_S_Language_by_dbo_T_S_User] WITH (STATE = ON)