Datenbank Trigger: Loggen von Berechtigungen

Datenbank Trigger eignen sich z.B. zum Mitschneiden von Berechtigungen die innerhalb einer Datenbank vergeben werden. So kann bspw. der Server- oder Datenbankadministrator prüfen und sicherstellen das jegliche Datenbank Berechtigungen die vergeben werden, von einem Datenbank Trigger erfasst und protokolliert werden.
In diesem Beispiel sollen jegliche Änderungen am Berechtigungskonzept der Datenbank in einer Protokollierungstabelle gespeichert werden.

Zuerst wird eine Tabelle benötigt, in die der Datenbank Trigger schreiben kann.
Gespeichert wird hier innerhalb eines XML Feldes die spätere Ausgabe, der Hostname vom Client der die Änderung veranlasst und das Programm mit der die Änderung durchgeführt wurde:

CREATE TABLE dbo.logObjektBerechtigungen (
uid uniqueidentifier default newid() not null primary key,
xmltext XML NULL,
hostname VARCHAR(MAX) NULL,
programmname VARCHAR(MAX) NULL,
datum DATETIME DEFAULT GETDATE() NOT NULL,
);

Nun wird der Trigger erstellt. Dieser Trigger erfasst mehrere DDL Ereignisgruppen:

  • DDL_GDR_DATABASE_EVENTS
    GRANT, DENY, REVOKE
  • DDL_ROLE_EVENTS
    CRATE ROLE, ALTER ROLE, DROP ROLE
  • DDL_USER_EVENTS
    CREATE USER, ALTER USER, DROP USER

Für jede DDL Abfrage wird der Trigger ausgeführt und schreibt das XML mit dem Host- und Programmnamen in die Protokollierungstabelle:
ALTER TRIGGER TR_ObjektBerechtigungen
ON DATABASE
FOR DDL_GDR_DATABASE_EVENTS, DDL_ROLE_EVENTS, DDL_USER_EVENTS
AS
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
INSERT INTO dbo.logObjektBerechtigungen
(xmltext, hostname, programmname)
VALUES ((SELECT EVENTDATA()), HOST_NAME(), PROGRAM_NAME());
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT OFF

Da der Trigger mehrere Ereignisgruppen protokolliert, bietet es sich an, für die verschiedenen Gruppen eine Sicht zu erstellen. Hier wird eine Sicht erstellt, die das „eingefangene“ XML entsprechend nach dem Ereignis „Role“ (enhält die Aktionen auf eine Datenbankrolle) filtert und arbeitet dann das XML entsprechend auf:
CREATE VIEW dbo.RollenBerechtigungen
AS
SELECT
hostname, programmname
,xmltext.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(MAX)') 'Art'
,xmltext.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(MAX)') 'Server Name'
,xmltext.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(MAX)') 'Login Name'
,xmltext.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(MAX)') 'Datenbank'
,xmltext.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(MAX)') 'Object'
,xmltext.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(MAX)') 'TSQL Befehl'
,xmltext
,datum
FROM logObjektBerechtigungen
WHERE (xmltext.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(MAX)') = 'ROLE')
Weitere Sichten können dann relativ simpel erstellt werden indem das XML in der Spalte „xmltext“ entsprechend ausgewertet wird.

Download:

Ein Gedanke zu „Datenbank Trigger: Loggen von Berechtigungen

  1. Pingback: Beliebteste Suchbegriffe » Datenbank Trigger: Loggen von Berechtigungen | flip-it.de …

Schreibe einen Kommentar