Datenbank Trigger: Loggen von Datenbank Änderungen

In diesem Blog Eintrag geht es wie im vorherigen Eintrag „Loggen von Berechtigungen“ um das Protokollieren von Änderungen. Hier werden dafür aber jegliche Struktur Änderungen an der Datenbank protokolliert. So kann ein Datenbank Administrator prüfen, welche Änderungen an der Datenbank durchgeführt wurden.
In diesem Beispiel soll zusätzlich eine Prüfung implementiert werden, die ermöglicht eine Checksumme zu erstellen die bspw. Software Herstellern erlaubt zu prüfen, ob jegliche Anpassungen in einem Änderungsscript erfolgreich durchgeführt wurden.

In der folgenden Tabelle wird dann jegliches XML erfasst, was der DDL Trigger erfassen soll. Zusätzlich wird hier auch die Checksumme gespeichert:
CREATE TABLE dbo.logDbTracking (
uid uniqueidentifier default newid() not null primary key,
xmltext XML NULL,
checksumme INT NULL,
datum DATETIME DEFAULT GETDATE() NOT NULL,
);

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

  • DDL_TABLE_EVENTS
    CREATE, ALTER, DROP TABLE
  • DDL_VIEW_EVENTS
    CREATE, ALTER, DROP VIEW
  • DDL_INDEX_EVENTS
    CREATE, ALTER, DROP INDEX, beinhaltet auch Volltext Indizes
  • DDL_FUNCTION_EVENTS
    CREATE, ALTER, DROP FUNCTION
  • DDL_PROCEDURE_EVENTS
    CREATE, ALTER, DROP PROCEDURE
  • DDL_TRIGGER_EVENTS
    CREATE, ALTER, DROP TRIGGER

Der Trigger schreibt in die Tabelle fü alle erfassten Events das XML wie auch aus dem XML eine erstelle Checksumme.
CREATE TRIGGER TR_DBChangeTracking
ON DATABASE
FOR
DDL_TABLE_EVENTS, DDL_VIEW_EVENTS,
DDL_INDEX_EVENTS, DDL_FUNCTION_EVENTS,
DDL_PROCEDURE_EVENTS, DDL_TRIGGER_EVENTS
AS
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON

DECLARE @xmlData XML
SELECT @xmlData = EVENTDATA()
DECLARE @cmdText NVARCHAR(MAX)
SET @cmdText = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(MAX)')

INSERT INTO dbo.logDbTracking
(xmltext, checksumme)
VALUES (
@xmlData,
CHECKSUM(@cmdText)
);
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT OFF

Nun werden jegliche strukturellen Änderungen an der Datenbank entsprechend protokolliert. Aus jedem protokollierten TSQL Statement wird in der Tabelle eine Checksumme gebildet. So erhält man die Möglichkeit einen Abgleich über die Vollständigkeit durchzuführen.

Hinweis: Oben stehendes Script funktioniert sowohl auf einem Microsoft SQL Server 2005 wie auch unter 2008 (R2).

Download:

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:

Berechnete Spalten

In diesem kleinen Blog-Eintrag geht es um berechnete Spalten um einen Teil der Business Logik innerhalb des Tabellen-Designs zu verlagern. Typischer Fall ist die Berechnung von kumulierten Netto und Brutto Werten innerhalb eines Bestellwesens.

Ausgangslage ist eine Bestelltabelle in der bestellte Artikel mit einer Menge und einem zugehörigen Preis gespeichert werden. Um entsprechend den Gesamtpreis zu ermitteln, benötigt man i.d.R. eine Funktion, Abfrage oder Prozedur. Ziel ist hier das Ergebnis direkt innerhalb der Tabelle zu speichern. Zusätzlich soll auch gleich die Mehrwertsteuer und der Brutto Wert abgespeichert werden.

Zuerst, um nicht bei einer Mehrwertsteuer Änderung das Tabellen Design anzupassen, benötigen wir eine Funktion die uns die Mehrwertsteuer zurückgibt:
CREATE FUNCTION dbo.HoleMehrwertsteuer ()
RETURNS float
BEGIN
RETURN 19.0
END
GO
Diese Funktion liefert die Mehrwertssteuer zurück.

Nun wird die Tabelle erstellt mit den Berechneten Spalten:
CREATE TABLE dbo.Bestellungen (
uid uniqueidentifier default newid() not null primary key,
artikelname varchar(255) not null,
artikelpreis money not null,
artikelmenge float not null,
nettopreis AS (artikelpreis * artikelmenge),
bruttopreis AS ((artikelpreis * artikelmenge) +
((artikelpreis * artikelmenge) *  (dbo.HoleMehrwertsteuer() / 100 )))
,
mehrwertsteuer AS (artikelpreis * artikelmenge * (dbo.HoleMehrwertsteuer() / 100 ))
);
GO
Erläuterung:

  • nettopreis: Berechnet den Preis anhand der Menge
  • bruttopreis: Berechnet den Preis anhand der Menge und addiert die MwSt hinzu
  • mehrwertsteuer: Gibt die enthaltene Mehrwertsteuer aus

Ein paar Daten:
INSERT INTO dbo.Bestellungen (artikelname, artikelpreis, artikelmenge)
VALUES
('Hemd', 30.00, 1),
('Jacke', 100.00, 1),
('Hose', 70.00, 2);
GO

Ergebnis:

In wie weit sich das Beispiel in der Praxis nutzen lässt, muss man für sich selber entscheiden. Grundsätzlich sollten Berechnete Spalten nicht ausßer Acht gelassen werden, denn dadurch kann man sich doch eine Menge an kleinen „lästigen“ Funktionen und Abfragen sparen kann, insbesondere wenn diese sehr Häufig wiederverwendet werden müssen.

Download:

Partitionierung von SQL Server Tabellen

In diesem Blog Eintrag wird die Partitionierung von Tabellen mit dem Microsoft SQL Server 2008R2 beschrieben.
Diese Funktion wurde mit der Version 2005 eingeführt und ermöglicht die Verteilung von Daten einer Tabelle in verschiedene Dateigruppen um diese bspw. Archivieren zu können indem die Daten anhand eines Merkmals in verschiedene Dateigruppen unterteilt werden.

Folgendes Beispiel beschreibt die einer Buchungstabelle in der die Datensätze anhand des Buchungsdatums in einzelne Dateigruppen unterteilt werden können. So kann bei anwachsen der Datenbank eine einzelne Datei, wo die Datensätze nicht mehr benötigt werden, gesichert und geleert werden. Dadurch wird die Datenbank wieder kleiner und erhöht gleichzeitig die Performance der Datenbank.

Vorab möchte ich noch darauf hinweisen, dass die Partitionierungs-Funktion nur in der Enterprise/Datacenter Edition verügbar ist.

Ausgangssituation für folgendes Beispiel:

  • Eine Buchungstabelle soll erstellt werden in der Daten aus einer anderen Datenbank übernommen werden
  • Es werden Daten aus den Kalenderjahren 2010, 2009 und früher übernommen.
  • Die Daten die früher als 2009 eingegeben wurden sind nur wenige
  • Die Partitionierung soll erweitert werden können um zukünftige Geschäftsjahre aufnehmen zu können

Lösungsmöglichkeit: Zuerst werden 4 Dateigruppen in die Datenbank eingefügt für die Geschäftsjahre 2011, 2010, 2009 und eine Datei für alle vorherigen Jahre:
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2009]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2010]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2011]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv]
Nun wird zu den 4 Dateigruppen jeweils eine Datendatei hinzugefügt:
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2009',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VERTEILER\MSSQL\DATA\2009.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2009]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2010',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VERTEILER\MSSQL\DATA\2010.ndf' ,SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2010]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2011',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VERTEILER\MSSQL\DATA\Aktuell.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2011]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'Archiv',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VERTEILER\MSSQL\DATA\Archiv.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv]
GO

Nun wird eine Partitionierungs-Funktion erstellt, die auf ein DATETIME Feld prüft und die Daten entsprechend verteilt:
CREATE PARTITION FUNCTION ArchivierungsFunktion (DATETIME) AS RANGE RIGHT
FOR VALUES (
'20090101' -- Kleiner als 01.01.2009
, '20100101' -- Kleiner als 01.01.2010, größer als 01.01.2009
, '20110101' -- Kleiner als 01.01.2011, größer als 01.01.2010 geht
);

Nun wird das Schema erstellt was die Funktion als Basis für die Datenverteilung nutzt und diese dann in die Dateigruppen aufteilt:
CREATE PARTITION SCHEME ArchivierungsSchema AS PARTITION ArchivierungsFunktion
TO ('Archiv', 'Archiv2009', 'Archiv2010', 'Archiv2011');
Als nächstes wird die Buchungstabelle erstellt die dem Schema zugeordnet wird. Das Datumsfeld, welches die Daten an die Funktion zur Verteilung weitergibt, muss mit in den Primärschlüssel aufgenommen werden:
CREATE TABLE dbo.buchungen (
[uid] uniqueidentifier default NEWID() NOT NULL,
[name] VARCHAR(50),
[datum] DATETIME DEFAULT GETDATE() NOT NULL,
[wert] MONEY) ON ArchivierungsSchema(datum);
GO
ALTER TABLE dbo.buchungen ADD CONSTRAINT
PK_buchungen PRIMARY KEY CLUSTERED
([uid], [datum]);

INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('Januar 2008', '20080308', '100');
GO
-- Insert in Archiv2009
INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('März 2009', '20090308', '100');
GO
-- Insert in Archiv2010
INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('Februar 2010', '20100209', '100');
GO
-- Insert in Archiv2011
INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('Januar 2011', '20110120', '100');
Um zu prüfen ob die eingetragenen Datensätze sich in den richtigen Partionen befinden, kann folgendes Statement verwendet werden:
SELECT
sys.filegroups.name 'Partitionsname' , sys.partitions.rows 'Zeilenanzahl'
FROM
sys.partitions INNER JOIN sys.allocation_units
ON sys.allocation_units.container_id = sys.partitions.hobt_id
INNER JOIN sys.filegroups ON sys.filegroups.data_space_id = sys.allocation_units.data_space_id
WHERE sys.partitions.object_id = OBJECT_ID('dbo.buchungen')

Hier sehen wir dann die Datenverteilung wie die Datensätze sich durch die Funktion in die richtigen Dateien verteilt haben.

 

Problematisch wird es dann wenn ein neues Geschäftsjahr anbricht und Datensätze für 2012 eingetragen werden. Diese Datensätze befinden sich dann in der Partition für 2011: INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('Januar 2012', '20120115', '300'); Dieses Problem kann dann durch Erstellen einer neuen Dateigruppe und dem Anpassen der Funktion gelöst werden:
-- Neue Dateigruppe für 2012
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2012]
GO

-- Neue Datei für die Dateigruppe 2012
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2012', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VERTEILER\MSSQL\DATA\2012.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2012]

Nun muss das Schema angepasst werden um die neue Dateigruppen zu verwenden, diese wird dann als Standard hinten dran gestellt.
Weiterhin wird durch die SPLIT() Funktion eine neue Begrenzung für 2012 eingefügt. Dadurch werden dann die bestehenden Daten geprüft und neu verteilt:
ALTER PARTITION SCHEME ArchivierungsSchema
NEXT USED [Archiv2012]
GO
ALTER PARTITION FUNCTION ArchivierungsFunktion ()
SPLIT RANGE ('20120101');

Hier ist dann zu sehen das die neue Dateigruppe 2012 verwendet wird, und der Datensatz aus der Partition 2011 in die Partition 2012 verschoben worden ist.

 

Download:

Datenverschlüsselung mit symetrischen Schlüsseln

In den unten aufgeführten Dokumenten wird die Verschlüsselung von Daten mit dem Microsoft SQL Server 2008R2 beschrieben.
Die Verschlüsselung eignet sich insbesondere zum Schutz von Datensätzen, bzw. einzelnen Feldern vor unbefugten Zugriff über das SMSS oder Access (oder anderen Möglichkeiten wo die Anwendung die die Daten schützt umgangen werden). Beide Beispiele befassen sich mit dem Schutz von Personaldaten auf Tabellenebene von den Gehältern und Kreditkartennummern.
Die Scripte können so wie sie sind über das SMSS ausgeführt werden und erzeugen eine Datenbank, eine Tabelle innerhalb von einem Schema, und greifen dann auf die Daten zu indem diese entschlüsselt werden.