Spalten-Security mit SQL

Meist wird für das Setzen von Spalten-Security mit SQL auf einem MS SQL Server ein Grant-Statement verwendet wie zum Beispiel:

GRANT SELECT ON Membership(MemberID, FirstName, LastName, Phone, Email) TO TestUser;

Das führt dazu, dass einem User (in diesem Fall TestUser) nur ein Teil der in der Tabelle Membership zur Verfügung stehenden Felder tatsächlich auch angezeigt werden.

Es gibt jedoch Szenarien, wo das ungünstig ist. Ein Beispiel wäre etwa, wenn ein Tableau Dashboard (oder eine andere Front-End Applikation) auf diese Daten zugreift und abhängig vom ausführenden User die Spalten plötzlich verschwinden. Da sind die Laufzeitfehler vorprogrammiert, da das Verhalten des Back-Ends für den Front-End Entwickler fast unmöglich vorhersagbar ist.

Deshalb habe ich in einem aktuellen Projekt genau für diesen Use-Case „Tableau on Top of MS-SQL Server“ nach einem Weg gesucht, der immer das volle Spektrum an Spalten anzeigt aber Felder, zu denen ein Benutzer keinen Zugriff hat einfach leer läßt.

Dabei ist die Lösung für dieses Problem eigentlich ganz einfach. Zuerst habe zusammen mit dem für Datenschutz zuständigen Mitarbeiter Gruppen von Feldern definiert:

• Information Category 1: allgemeine Informationen
• Information Category 2: vertrauliche Informationen
• Information Category 3a: streng vertrauliche Mitarbeiter Daten
• Information Category 3b: streng vertrauliche Daten zur Mitarbeiter Entwicklung (Talent & Performance)
• Information Category 3c: streng vertrauliche Gehaltsdaten

Diese Eingruppierung ist natürlich auf die aktuelle Anwendung hin ausgelegt. Wenn Sie diesen Ansatz für sich adaptieren möchten, kommen Sie sicher zu anderen Gruppierungen. Als nächstes habe ich eine Benutzer-Tabelle definiert, die neben dem Windows-Account des Users auch festlegt, zu welchen Kategorien er/sie Zugriff erhält:

Wobei eine 1 für die jeweilige Info-Kategorie bedeutet, dass der benutzer Zugriff erhalten soll und 0, dass eine leere Spalte angezeigt werden soll.

Damit kann nun eine View definiert werden, die diese Werte nutzt um Spalten korrekt mit Inhalt oder mit NULL zu füllen – abhängig vom konfigurierten Werte für dei dazugehörige Info-Kategorie:

CREATE VIEW [HR_Cube].[vEOP_HIST_Secured]
as
	SELECT
		CASE WHEN U.InfoCat2 = 1 THEN EOP.ORIGINAL_START_DATE ELSE NULL END "ORIGINAL_START_DATE",
		CASE WHEN U.InfoCat2 = 1 THEN EOP.LAST_START_DATE ELSE NULL END "LAST_START_DATE",
		CASE WHEN U.InfoCat2 = 1 THEN EOP.LOC_GRP ELSE NULL END "LOC_GRP",
		CASE WHEN U.InfoCat2 = 1 THEN EOP.LOC_CLASS ELSE NULL END "LOC_CLASS",
		CASE WHEN U.InfoCat2 = 1 THEN EOP.LOC_YEARS ELSE NULL END "LOC_YEARS",
		CASE WHEN U.InfoCat2 = 1 THEN EOP.LOC_DAYS ELSE NULL END "LOC_DAYS",
		CASE WHEN U.InfoCat3a = 1 THEN EOP.BIRTHDATE ELSE NULL END "BIRTHDATE",
		CASE WHEN U.InfoCat2 = 1 THEN EOP.AGE_GRP ELSE NULL END "AGE_GRP",
		CASE WHEN U.InfoCat2 = 1 THEN EOP.AGE_CLASS ELSE NULL END "AGE_CLASS",
		CASE WHEN U.InfoCat3a = 1 THEN EOP.AGE_YEARS ELSE NULL END "AGE_YEARS",
		CASE WHEN U.InfoCat3a = 1 THEN EOP.AGE_DAYS ELSE NULL END "AGE_DAYS",
		CASE WHEN U.InfoCat3a = 1 THEN EOP.GENDER ELSE NULL END "GENDER"
	FROM HR_Cube.tblEOP_HIST EOP
	INNER JOIN [dbo].[tblUsers] U ON U.WindowsID = ORIGINAL_LOGIN()
GO

Der Join zwischen der Haupttabelle und der Tabelle mit den Benutzerdaten mag dabei etwas komisch aussehen, da er keine direkte Verbindung abbildet sondern theoretisch zu einem Kartesischen-Produkt führt. Da wir aber ORIGINAL_LOGIN() als Verknüpfungsparameter nutzen, liefert der Join genau die eine Zeile von tblUsers wieder, die mit dem Windows-Account des aktuellen Benutzers übereinstimmt. Der RowCount für dieses „Kartesische Produkt“ ist damit identisch mit dem der zugrundeliegenden Tabelle. 

Für jede Spalte definiert der View ein CASE Statement, das prüft, ob der Benutzer auf die Spalte zugreifen darf oder stattdessen NULL zurück gegeben werden soll. In meinem aktuellen Projekt habe ich für jede relevante Tabelle eine 1:1 View nach diesem Muster aufgesetzt, auf die die End-Benutzer zugriff erhalten – nicht jedoch auf die Tabellen selbst.

Die vollständige Implementierung der View ist tatsächlich noch etwas komplexer, da darin neben der Spalten-Security mit SQL auch noch eine Zeilen-Sicherheit mit eingebaut wurde – aber das zeige ich in einem späteren Blog-Beitrag einmal.  Ich hoffe, dass dieser kurze Abriss dem ein oder anderen als Inspiration dient. Auf jeden Fall freue ich mich jederzeit über Feedback.