Über das Thema Berechtigungen wurde bereits mehrfach gebloggt, dennoch gibt es immer wieder neue interessante Lösungen. Neulich beim Kunden in Shanghai hatte das Thema Berechtigungen allerhöchste Priorität aufgrund der hohen Fluktuation von Mitarbeitern. Die Anforderungen waren dementsprechend hoch und vermeintlich widersprüchlich: Berechtigungen sollten möglichst flexibel je Abteilung, je Region und je Kennzahl vergeben werden können und das natürlich ohne großen Aufwand.
Ohne Aufwand bedeutet in diesem Fall, dass ein Mitarbeiter einen Antrag über ein Formular stellt, die-ser Antrag von einem Vorgesetzten geprüft wird und nach Freigabe von einem beliebigen Mitarbeiter der IT bearbeitet werden soll.
Durch den sehr volatilen Nutzerkreis kam als Lösung nur die Nutzung von Active-Directory(AD)-Gruppen in Frage. Jeder Anwender hat einen AD-User, und die IT braucht den Mitarbeiter ausschließlich bestimmten Gruppen zuordnen oder eben aus einer Zuordnung wieder herausnehmen bzw. den User deaktivieren. Selbstverständlich müssen die Berechtigungen über AD-Gruppen in OLAP und auch der SQL-Datenbank – beim DeltaMaster-SQL-Durchgriff – greifen.
Weiterhin enthält das BI-Modell mehrere hundert Kennzahlen und wird darüber hinaus nicht nur lokal, sondern auch in der Muttergesellschaft weiterentwickelt: Halbautomatisch werden neue Kennzahlen oder Kennzahlengruppen bereitgestellt. Eine Berechtigung auf einzelne Kennzahlen wäre somit sehr aufwendig und fehleranfällig, aber auch dafür haben wir eine Lösung.
AD-Gruppen
Für die Berechtigungsverwaltung werden die nachfolgenden Gruppen im Active Directory angelegt. Den Gruppen wird zusätzlich der Präfix DeltaMaster vorangestellt, damit aus den Namen bereits deutlich wird, für welchen Zweck diese Gruppen verwendet werden.
Damit ein Nutzer im DeltaMaster Zugriff auf seine Daten erhält, muss dieser in mindestens zwei AD-Gruppen eingetragen werden: Region und Msr (Measure). Über die Gruppen „Region“ erhält der User Zugriff auf eine oder mehrere Regionen und über die Gruppen „Msr“ auf bestimmte Kennzahlenbereiche.
OLAP-Berechtigungen
Regionen
In der OLAP-Datenbank wird für jede Region eine Rolle angelegt und die dazu passende AD-Gruppe zugeordnet. Der Rolle „Region North“ wird die AD-Gruppe „DeltaMaster_RegionNorth“ zugeordnet.
In den Dimensionsdaten müssen dann zwei Einstellungen vorgenommen werden:
- In der Dimension, in welcher die Regionen enthalten sind, wird die entsprechende Region an-gehakt und alle anderen Regionen werden abgehakt.
Abbildung 1: Rolle “Region North”, Attributsicherheit auf Regionen
- Auf Ebene des Würfels muss in der „Measuredimension“ sichergestellt werden, dass keine Kennzahl berechtigt ist. Dafür auf dem Reiter „Erweitert“ bei „Zulässige Elementgruppe“ eine leere Menge „{}“ angeben oder einfach alle Elemente abhaken. Die Berechtigungen für die Kennzahlen werden in den anderen Rollen vergeben.
Abbildung 2: Rolle “Region North”, Attributsicherheit auf der “Measuredimension”
Hinweis: Eigentlich sollte es egal sein, ob die Dimension über alle Würfel hinweg oder die Dimension im Würfel eingeschränkt wird. Es kann jedoch zu einem merkwürdigen Verhalten kommen, wenn in einer Rolle eine Dimension über alle Würfel hinweg Berechtigungen enthält und eine Dimension innerhalb eines Würfels. Deswegen empfehlen wir, die Dimension innerhalb des Würfels zu bearbeiten.
Kennzahlen
Für Berechtigungen auf Kennzahlen kann auf zwei Arten vorgegangen werden:
- Je AD-Gruppe eine Rolle anlegen, in der mehrere Kennzahlengruppen berechtigt werden
- Je Kennzahlengruppe eine Rolle anlegen und mehrere AD-Gruppen zuordnen
Wir haben uns für die Variante 1 entschieden, da die AD-Gruppen für die Abteilungen fest definiert sind und die Kennzahlengruppen volatil sind. Wird das Modell um eine neue Kennzahlengruppe erweitert, so muss ausschließlich diese Gruppe einer oder mehreren Rollen zugeordnet werden.
In den Dimensionsdaten müssen wieder zwei Einstellungen vorgenommen werden:
- In der Dimension, in welcher die Regionen enthalten sind, wird eine leere Menge angegeben, damit über diese Rolle keine Region berechtigt wird.
Abbildung 3: Rolle “Sales”, Attributsicherheit auf Regionen
- Auf Ebene des Würfels werden in der „Measuredimension“ die zu berechtigenden Kennzahlen-gruppen angegeben. Auf dem Reiter „Erweitert“ bei „Zulässige Elementgruppe“ werden die Kennzahlengruppen über MeasureGroupMeasures („<NameDerKennzahlengruppe>“) berechtigt. Sollen mehrere Kennzahlengruppen berechtigt werden, so sind diese über den Operator „+“ (Vereinigung) zu verbinden.
Abbildung 4: Rolle “Sales”, Attributsicherheit auf der “Measuredimension”
SQL-Berechtigungen
In der relationalen Datenbank muss ebenfalls sichergestellt werden, dass die gleichen Berechtigungen gelten. Voraussetzung ist, dass auf der SQL-Server-Instanz „Anmeldungen“ (Logins) für alle oben genannten AD-Gruppen angelegt werden und diesen „Anmeldungen“ in der DeltaMaster-Datenbank die Rolle „DeltaMaster_User“ zugeordnet wird.
Für die Berechtigungen auf Regionen und Kennzahlengruppen werden zwei einfache Tabellen mit je zwei Spalten angelegt:
- in der ersten Tabelle „T_SEC_AD_Group_Region“ werden die AD-Gruppen den Regionen zugeordnet
- in der zweiten Tabelle „T_SEC_AD_Group_Measuregroup“ die AD-Gruppen den Kennzahlengruppen
Über die bekannten V_SEC-Views, die DeltaMaster für RowLevel-Security verwendet, werden die Berechtigungen geprüft. Beispielhaft für die Berechtigung auf die Regionen sind die nachfolgendenden SQL Statements aufgeführt. Analog kann das Verfahren auch für Kennzahlen verwendet werden.
Das nachfolgende SQL-Statement listet alle Kunden auf, auf die der aktuelle Nutzer Zugriff hat. Dafür werden über die Hierarchie der Dimension alle notwendigen Tabellen der einzelnen Ebenen verbunden und schließlich die oben genannte Tabelle T_SEC_AD_Group_Region. Über den Befehl IS_MEMBER() wird das AD befragt, ob der aktuelle User (SYSTEM_USER) Mitglied der AD-Gruppe ist. Die AD-Gruppe „RegionAll“ kann entweder in der Tabelle „T_SEC_AD_Group_Region“ für jede Region angegeben werden oder alternativ über ein „Oder“ direkt im Statement abgefragt werden.
-- List all customerIDs the system_user has access to
SELECT
kunde.KundeID
FROM
T_DIM_05_04_Kunde kunde
LEFT JOIN T_DIM_05_03_PLZ plz
ON plz.plzID = kunde.plzID
LEFT JOIN T_DIM_05_02_Gebiet gebiet
ON gebiet.gebietID = plz.gebietID
LEFT JOIN T_SEC_AD_Group_Region ad
ON ad.regionID = gebiet.RegionID
WHERE
IS_MEMBER(ad.AD_Group) = 1 -- checks if User is member of specified AD Group
OR IS_MEMBER('Domain\DeltaMaster_RegionAll')Die Verlagerung dieser Abfrage in eine eigene View hat einige Vorteile gegenüber der direkten Verwendung innerhalb einer WHERE-Bedingung in den V_SEC-Views:
- Der Code braucht nur einmal geschrieben werden und kann so leicht wieder verwendet werden (Kapselung) und braucht bei Bedarf nur an einer Stelle angepasst werden
- Das AD wird bei erneuter Abfrage nicht erneut befragt, da der SQL Server die Abfrage für eine bestimmte Zeit in seinem Cache behält
In der V_SEC-View sieht die WHERE-Bedingung folgendermaßen aus:
WHERE
-- Authorisation Check
EXISTS (SELECT *
FROM V_SEC_AD_Group_Region region
WHERE region.RegionID = fact.KundeID) Weitere Blogbeiträge zum Thema Berechtigungen
https://www.bissantz.de/know-how/crew/dynamische-ermittlung-von-mitgliedern-einer-ad-gruppe-per-sql/
https://www.bissantz.de/know-how/crew/zellsicherheit-aus-der-zelle-zuruck-in-die-dimension/
https://www.bissantz.de/know-how/crew/sehen-und-gesehen-werden/
https://www.bissantz.de/know-how/crew/berechtigungen-fur-zellkommentare/
https://www.bissantz.de/know-how/crew/deltamaster-sql-durchgriff-optimieren/
https://www.bissantz.de/know-how/crew/berechtigungskonzept-mithilfe-von-linked-measuregroups/
https://www.bissantz.de/know-how/crew/schreibrechte-zeitgesteuert-vergeben/
https://www.bissantz.de/know-how/crew/dynamische-zeilenberechtigungen-in-sql/
