Effektive Umsetzung von Row Level Security in Hierarchien: Eine Praxisanleitung

Wenn es um das Thema dynamische Row Level Security geht, begegne ich immer wieder einer Herausforderung: Dem Business User des Kunden die Vergabe der Berechtigungen für die Row Level Security so angenehm wie möglich zu machen. Dies wird insbesondere zur Herausforderung, wenn wir die RLS auf verschiedenen Leveln der Hierarchie vergeben möchten, ohne dass dem Business User bei der Pflege genau klar sein muss, welcher Knoten auf welchem Level vorliegt.

‍

‍

Anwendungsbeispiel: Der Manager, der für die Berechtigungen verantwortlich ist, möchte dem Test-User Zugriff auf den Geschäftsbereich mit dem EntityKey 8 gewähren. Zunächst wird an geeigneter Stelle eine einzelne Zeile in der Datenbank erstellt. Es handelt sich jedoch um einen höhergeordneten Knoten in der Hierarchie und kann daher noch nicht direkt mit unserer Faktentabelle verknüpft werden.

Um diese Anforderung, die ich bereits in mehreren Projekten umgesetzt habe, technisch abzubilden, ist eine Möglichkeit die direkte Implementierung ins Tabular Model. Wie das funktioniert, zeige ich Dir im nachfolgenden Beitrag am Beispiel eines Standard Finanzreport, der Beispieldaten aus der Contoso Datenbank enthält. Einen allgemeineren Überblick über Row Level Security und wofür diese sinnvoll ist, habe ich hier geschrieben.

Schritt 1: Extraktion der Parent-Child-Hierarchie und Bestimmung der verschiedenen Level

To-Do: Im ersten Schritt entnehmen wir die Parent-Child-Hierarchie aus der Datenbank, welche wir anschließend durch eine rekursive Common Table Expression (CTE) auflösen und bestimmen die verschiedenen Level. Wir starten dabei mit den Elementen, welche keine Parents haben, also die Oberknoten der Hierarchie sind:

‍Ergebnis: Dies ermöglicht die Erstellung einer Tabelle mit Spalten, die die verschiedenen Hierarchieebenen (Level 0, Level 1, Level 2 usw.) abbilden und im Tabular Model angezeigt wird:

‍

‍

Das ist für den Report an sich schon sehr hilfreich, aber noch haben wir nicht jede Ancestor-Descendant Beziehung abgebildet.

Schritt 2: Daten persistieren und rückwärtsgewandte Rekursion

To-Do: Die Hierarchie beziehungsweise das Query Ergebnis aus dem SELECT * FROM RCTE aus dem ersten Schritt bildet die Grundlage für Schritt 2. In Schritt 2 persistieren wir bei Bedarf die Hierarchie in die SQL-Datenbank und verarbeiten mittels einer „rückwärtsgewandten“ Rekursion die Daten wieder nach oben. Entscheidend ist dabei die StartingNode, welche über alle Schritte der Rekursion hinweg merkt mit welchem Element gestartet wurde.

Ergebnis: Als Ergebnis erhalten wir für jedes Element eine Liste sämtlicher Nachfolger (Descendant) für jeden Vorgänger (Ancestor) der Hierarchie.

‍

Schritt 3: Integration in das tabellarische Modell

To-Do: Im dritten Schritt integrieren wir die Liste aller übergeordneten Elemente (Ancestors) aus Schritt 2 als separate Tabelle in das tabellarische Modell. Anschließend verknüpfen wir diese Liste mit der Faktentabelle und der Berechtigungstabelle (User_Permissions Tabelle) der Row Level Security.

‍

‍

Ergebnis: Durch die Anwendung dieses Prozesses erreichen wir die Verwendung von Row Level Security auf verschiedenen Hierarchieebenen, ohne dass Business User im Detail wissen müssen, auf welcher Ebene ein bestimmter Knoten liegt. Falls der Anwendungsfall eintreten sollte, dass jemand doch nur auf dem Leaf Level berechtigt sein soll, ist wichtig zu beachten, dass in der EntityHierarchyFull Tabelle jeder EntityKey auch AncestorKey von sich selbst ist. Der einfachste Weg dies zu lösen ist eine SQL-View mit einem Union All bevor die Tabelle ins Tabellarische Modell importiert wird.

Fazit

Die hier vorgestellte Methodik bietet eine effiziente und benutzerfreundliche Lösung für die Herausforderung der Berechtigungsverwaltung auf verschiedenen Hierarchieebenen. Mit dieser Methode entfällt die Notwendigkeit, komplexe Berechtigungslogik im Frontend zu entwickeln. Die Implementierung erfolgt im Hintergrund des Datenmodells, was die Pflege und Wartung erheblich vereinfacht. Die Umsetzung dieser Lösung ist vergleichsweise schnell, effizient und erfordert keine hochspezialisierten Kenntnisse, sondern kann mit grundlegendem SQL und SSAS (SQL Server Analysis Services) umgesetzt werden. Das macht sie auch für Entwickler und Administratoren mit soliden Grundkenntnissen zugänglich.

Insgesamt bietet diese Methode eine elegante Lösung für die Implementierung von Row Level Security in komplexen Hierarchien, ohne dass Business User im Detail wissen müssen, auf welcher Hierarchieebene sich bestimmte Knoten befinden. Die alternative Lösung wäre es ein Webfrontend zu entwickeln, dass die Liste aller Descendants (Das Ergebnis aus Schritt 2) beim Eintragen der ursprünglichen Berechtigungen generiert und persistiert.