Ermitteln von indirekten Rollenmitgliedern

Oft will man ermitteln welche Mitglieder eine bestimmte Datenbankrolle hat. Dabei ist jedoch zu beachten, dass es möglich ist, dass Datenbankrollen selbst wiederum Mitglied einer Datenbankrolle sind. Dies ist in der Regel eher ein schlechtes Security-Design und man sollte möglichst flache Hierarchien bilden. Allerdings kommt es durchaus vor, dass man an Systeme kommt, die man selbst nicht entworfen hat und nachträglich Rollenzugehörigkeiten ermitteln muss.

Im Folgenden wollen wir ermitteln welche direkten und indirekten Mitglieder die Rolle db_owner in einer vorher gewählten Datenbank hat. Das folgende Beispiel lässt sich natürlich auch für beliebige andere Rollen ausführen und erweitern.

Mitgliedschaft über CTE ermitteln

Um die Mitgliedschaft in einer Rolle zu ermitteln werden zwei Catalog Views benötigt. Einerseits sys.database_principals ,welche alle Datenbankbenutzer, sowie alle Datenbankrollen enthält und andererseits sys.database_role_members, welche jeweils enthält wer Mitglied in welcher Rolle ist.

Da wir hierfür eine rekursive Abfrage benötigen, habe ich im Folgenden eine CTE (Common Table Expression) für die Ermittlung der Mitglieder geschrieben:

Dabei beginnt die Rekursion bei der db_owner Rolle und schaut dann jeweils, wer Mitglied der db_owner Rolle ist. Danach wird rekursiv geschaut, wer Mitglied in den Rollen ist, die selbst Mitglied der db_owner Rolle waren usw.

Führen wir die Query innerhalb einer Datenbank aus, sehen wir hier z.B. den Benutzer StrangeUser der db_owner Rechte besitzt aber nur indirekt Mitglied der db_owner  Rolle ist. Direktes Mitglied ist er nur von der Rolle StrangeRole, welche dann aber wieder Mitglied der Rolle db_owner ist. Auf diese Weise können wir sehr leicht ermitteln, wer effektiv Mitglied einer Rolle ist, falls es hier eine Rollenhierarchie gibt.

Die CTE lässt sich natürlich auch in eine Table Valued Stored Function oder Stored Procedure einbauen, welcher man dann auch andere Rollennamen als db_owner per Parameter übergeben kann.

 

Kommentar hinterlassen

Deine E-Mail-Adresse wird nicht veröffentlicht.