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:

USE ExampleDB;
GO

WITH SecMember 
(parent_principal_id, member_principal_id)
AS 
(
	SELECT D.principal_id, M.member_principal_id 
		FROM sys.database_principals D JOIN sys.database_role_members M 
			ON D.principal_id=M.role_principal_id 
		WHERE D.name = 'db_owner'
	UNION ALL
	SELECT M.role_principal_id, M.member_principal_id 
		FROM SecMember S JOIN sys.database_role_members M 
		ON S.member_principal_id=M.role_principal_id		
)
SELECT DM.principal_id AS 'Principal ID',
	   DM.name AS 'Member Name', 
	   DM.type_desc AS 'Member Type', 
	   DP.principal_id AS 'Parent Principal ID', 
	   DP.name AS 'Parent Name', 
	   DP.type_desc AS 'Parent Type' 
    FROM SecMember S JOIN sys.database_principals DM 
	   ON S.member_principal_id=DM.principal_id 
	   JOIN sys.database_principals DP 
	   ON S.parent_principal_id=DP.principal_id 
	OPTION (MAXRECURSION 10000);

USE ExampleDB;

WITH SecMember

(parent_principal_id, member_principal_id)

(

SELECT D.principal_id, M.member_principal_id

FROM sys.database_principals D JOIN sys.database_role_members M

ON D.principal_id=M.role_principal_id

WHERE D.name = 'db_owner'

UNION ALL

SELECT M.role_principal_id, M.member_principal_id

FROM SecMember S JOIN sys.database_role_members M

ON S.member_principal_id=M.role_principal_id

)

SELECT DM.principal_id AS 'Principal ID',

DM.name AS 'Member Name',

DM.type_desc AS 'Member Type',

DP.principal_id AS 'Parent Principal ID',

DP.name AS 'Parent Name',

DP.type_desc AS 'Parent Type'

FROM SecMember S JOIN sys.database_principals DM

ON S.member_principal_id=DM.principal_id

JOIN sys.database_principals DP

ON S.parent_principal_id=DP.principal_id

OPTION (MAXRECURSION 10000);

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.