Server Trigger: Logon Trigger

In diesem Blog Eintrag geht es um Logon-Trigger. Analog zu den Einstell-Möglichkeiten im Microsoft Active Directory, wo man Benutzern verschiedene Zeiten voreinstellen kann, wann eine Anmeldung möglich ist, kann dies auch im SQL Server nachvollzogen werden.

Hierfür ist lediglich eine kleine Infrastruktur notwendig. Eine Verwaltungsdatenbank in der die Benutzer gespeichert werden, wie auch eine Tabelle in der die Zeiten definiert werden.

In folgendem Beispiel wird die Verwaltungsdatenbank erstellt mit einer Benutzertabelle in der die Benutzernamen gespeichert werden, eine Gruppentabelle um Benutzer zu Gruppen hinzuzufügen die gleichzeitig die Zeiten definieren.

Mit diesem Code wird die Benutzer Tabelle erzeugt. Hier werden lediglich die Benutzernamen gespeichert:
create table dbo.benutzer (
uid uniqueidentifier default newid() primary key not null,
benutzername varchar(255) not null unique);
go

Hier noch 2 Benutzer:
insert into benutzer (benutzername) values ('Herta');
insert into benutzer (benutzername) values ('Kerrin');
go

Um das Beispiel testen zu können, empfiehlt es sich auch diese Benutzer als SQL Authentifizierte Accounts auf dem Server anzulegen und der Datenbank zuzuordnen.

Nun wird die Gruppen Tabelle erstellt. Über die Felder Beginn- und Endzeit wird die Zeit definiert, wo eine Anmeldung möglich ist.
create table dbo.gruppen (
uid uniqueidentifier default newid() primary key not null,
gruppenname varchar(255) not null unique,
beginnzeit time not null,
endzeit time not null);
go

Nun werden noch 3 Gruppen angelegt:
insert into gruppen (gruppenname, beginnzeit, endzeit) values ('Vormittag', '07:00', '12:30');
insert into gruppen (gruppenname, beginnzeit, endzeit) values ('Nachmittag', '12:30', '18:30');
insert into gruppen (gruppenname, beginnzeit, endzeit) values ('Vollzeit', '07:00', '18:30');
go

Zuletzt wird noch die Zuordnungstabelle erstellt:
(Für die Übersicht habe ich hier die Referentiellen Integritäten nicht erstellt)
create table bennutzer_gruppen (
benutzer_uid uniqueidentifier not null,
gruppen_uid uniqueidentifier not null);
go

Nun werden die Benutzer noch jeweils einer Gruppe zugeordnet:
(Beim Test müssen die ID’s angepasst werden)
insert into bennutzer_gruppen values ('E4727227-7C64-46E0-8233-CE0A249AAAB5', 'D319D6EA-D280-4E65-8AC6-823100270667');
insert into bennutzer_gruppen values ('ED3EEA47-E4BA-4E82-A6E7-D5A69E991C95', 'F5D9E222-98BE-455A-B5B5-62096CA254EE');
go

Nun wird noch eine View erstellt, die die Benutzer zu den jeweligen Gruppen ausgibt:
create view benutzerInGruppen as
SELECT     benutzer.benutzername, gruppen.gruppenname, gruppen.beginnzeit, gruppen.endzeit
FROM         bennutzer_gruppen INNER JOIN
benutzer ON bennutzer_gruppen.benutzer_uid = benutzer.uid INNER JOIN
gruppen ON bennutzer_gruppen.gruppen_uid = gruppen.uid
go

Ergebnis:

Für den Trigger wird hier noch eine weitere View erstellt, die prüfen soll, ob es den Benutzer auch gibt:
create view benutzernamen as
SELECT benutzername FROM benutzer WHERE benutzername = SUSER_NAME()
go

Damit die Benutzer beim Login auch prüfen können, ob ein Login möglich ist, wird noch eine Rolle erstellt die auf die beiden Sichten berechtigt und die Benutzer werden dieser Rolle hinzugefügt:
create role benutzer;
grant select on benutzerInGruppen to benutzer
grant select on benutzernamen to benutzer
exec sp_addrolemember benutzer, herta;
exec sp_addrolemember benutzer, kerrin;
go

Ob die Datenbank Infrastruktur stimmt, kann mit folgenden Code überprüft werden:
EXECUTE AS LOGIN = 'kerrin'
SELECT * FROM benutzernamen
REVERT

Hiermit wird die Abfrage unter dem angegeben Benutzerkontext ausgeführt.

Nun wird der Servertrigger erstellt. Server Trigger befinden sich im SSMS hier:

Der Trigger soll folgende Voraussetzungen erfüllen:

  • Benutzer die nicht in der Tabelle definiert sind, sollen vom Trigger ausgenommen werden
  • Nur Benutzer die sich im Zeitfenster befinden dürfen sich anmelden

Nun der Code:
CREATE trigger benutzerAnmeldung on ALL SERVER
for LOGON
as
begin
DECLARE @result INT
IF((SELECT COUNT(*) from LogonTime.dbo.benutzernamen) > 0)
BEGIN
select @result = COUNT(*) from LogonTime.dbo.benutzerInGruppen
where benutzername = SUSER_SNAME()
AND (
beginnzeit <= CONVERT(time, GETDATE()) AND endzeit >= CONVERT(time, GETDATE()))
IF(@result = 0)
BEGIN
ROLLBACK;
END
END
END;
go

Nun kann man den Logon mit einem Benutzernamen testen. Befindet man sich in dem Zeitfenster funktioniert eine Anmeldung, ansonsten wird man durch folgende Meldung begrüßt:

TDE: Datenbank Verschlüsselung

Durch die in SQL Server 2008 eingeführte Funktion TDE (Transparent Data Encryption) werden die Datenbank Dateien physikalisch auf dem Dateisystem verschlüsselt. Hierbei handelt es sich nicht um die Verschlüsselung der Daten auf logischer Ebene, also beim Lesen aus den Tabellen o.ä., sondern die Dateien können nicht ohne eine Entschlüsselung auf einem anderen Server wiederhergestellt werden. Weiterhin können die Dateien auch nicht via einem HEX Editor o.ä. ausgelesen werden, da auch der komplette Inhalt verschlüsselt ist.

TDE kostet überings laut Microsoft keinerlei Performance. Voraussetzung hierfür ist, dass entsprechend CPU Zeit zur Verfügung steht, da beim Lesen von Seiten die CPU bei der Entschlüsselung mehr als sonst gefragt ist.

In diesem Beispiel wird beschrieben wie eine Datenbank mit TDE verschlüsselt und dann auf einem anderen Server wiederhergestellt wird:

Zuerst wird auf der Instanz ein Master Schlüssel erzeugt, mit dem dann die Zertifikate verschlüsselt werden:
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Kennwort1!'
GO

Nun wird das Zertfikat erstellt:
USE master
GO
CREATE CERTIFICATE TDE WITH SUBJECT = 'Zertifikat fuer die TDE Datenbank'
GO

Zur Vollständigkeit des Beispiels wird noch die Datenbank „TDE“ erstellt, die geschützt werden soll:
CREATE DATABASE TDE
GO
USE TDE
GO
CREATE TABLE dbo.Daten (wert varchar(100));
GO
INSERT INTO dbo.Daten VALUES ('Daten');
GO

Nun wird ein Verschlüsselungsschlüssel erzeugt, der die Datenbank später verschlüsselt.
Unterstützt werden: AES 128, AES 192, AES 256, TRIPLE DES
USE TDE
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDE
GO

Nun sollte zuerst das Zertifikat gesichert werden:
USE master
GO
BACKUP CERTIFICATE TDE TO FILE = 'c:\TDE_certificate'
WITH PRIVATE KEY ( FILE = 'c:\TDE_certificate_key' ,
ENCRYPTION BY PASSWORD = 'Kennwort1!' )
GO

Dabei werden 2 Dateien erzeugt: Die eigentliche Zertifikatsdatei und der Schlüssel in der zweiten Datei. Diese Datei wird durch ein Kennwort geschützt.

Verschlüsselung anwenden:
ALTER DATABASE TDE SET ENCRYPTION ON;
GO

Nun ist die Datenbank verschlüsselt und kann physikalisch nicht ohne Schlüssel ausgelesen werden. Die Verschlüsselung wirkt sich nicht auf die Datensicherung (Voll-, Differentiell- oder Transaktionslogsicherung) aus.

Nun wird eine Vollsicherung der Datenbank durchgeführt, die später auf einem anderen Server wiederhergestellt werden soll:
backup database TDE to disk = 'C:\temp\TDE.bak' WITH INIT

Nun kann ein neuer Master-Schlüssel auf dem anderen Server erzeugt werden. Das Kennwort muss nicht identisch mit dem Kennwort vom vorherigen Server sein.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Kennwort2!';
GO

Nun wird das Zertifikat wiederhergestellt. Hier kann ein beliebiger neuer Name ebenfalls gewählt werden:
CREATE CERTIFICATE TDE_SERVER2 FROM FILE = 'c:\TDE_certificate'
WITH PRIVATE KEY ( FILE = 'c:\TDE_certificate_key' ,
DECRYPTION BY PASSWORD = 'Kennwort1!' )
GO

Nun wo das Zertifikat wierhergestellt ist, kann auch die Datensicherung widerhergestellt werden:
RESTORE DATABASE [TDE_RESTORE] FROM DISK = N'C:\temp\TDE.bak' WITH FILE = 1, MOVE N'TDE' TO N'F:\TDE_RESTORE.mdf', MOVE N'TDE_log' TO N'F:\TDE_RESTORE_1.LDF', NOUNLOAD, STATS = 10
GO

Fazit:
TDE eignet sich hervorragend zum Schutz der Datendateien vor unbefugten Zugriff und kostet, in meinen Tests, keinerlei zusätzliche Ressourcen oder Zeit beim Zugriff auf die Datenbank.
Jedoch bleibt immer zu beachten, dass man Zugriff auf das Kennwort wie auch auf das Zertifikat benötigt wenn eine Datensicherung wiederhergestellt werden muss. Weiterhin bleibt zu beachten, dass es sich hier um ein Enterprise Feature handelt.

CDC – Zeilen Versionierung

In diesem Blog Eintrag geht es um Change Data Capture (CDC). Dieses Werkzeug im Microsoft SQL Server 2008 (verfügbar (leider) ab der Enterprise Edition) dient nicht für das Autiting von Benutzern sondern um Daten zu versionieren und so Veränderungen oder Bewegungen auswerten oder wiederherstellen zu können. Das bedeutet, jede Tabelle in der CDC aktiviert ist, wird entsprecht mit protokolliert und die Änderungen werden in einer eigenen Systemtabelle pro Objekt gespeichert. Wie man zu erst vermuten möchte, handelt es sich nicht um typische DML Trigger sondern um eine Technologie, die direkt vom Transaktionslog liest und somit sicher die Datenbewegungen registriert.

Als Fallbeispiel wird eine Buchungstabelle „überwacht“:
CREATE TABLE dbo.buchungen (
uid uniqueidentifier default newid() not null primary key,
buchungsid int not null,
buchungswert money not null,
buchungsdatum datetime not null);
GO

Über folgenden Befehl wird CDC in der Datenbank aktiviert:
EXEC sys.sp_cdc_enable_db
GO

Nun wird CDC für die Tabelle aktiviert:
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'buchungen',
@role_name     = NULL,
@supports_net_changes = 1
GO

Nun fügen wir einen Datensatz in die Buchungstabelle ein:
INSERT INTO dbo.buchungen (buchungsid, buchungswert, buchungsdatum)
VALUES (1, 200, GETDATE());
GO

Zuletzt verändern wir diesen Datensatz nochmal:
UPDATE dbo.buchungen SET buchungswert = 17300 WHERE buchungsid = 1
GO

Mit folgender Abfrage kann die Datenveränderung dargestellt werden mit den jeweiligen Statis:
SELECT
CASE __$operation
WHEN 1 THEN 'DELETED'
WHEN 2 THEN 'INSERTED'
WHEN 3 THEN 'BEFORE UPDATED'
WHEN 4 THEN 'AFTER UPDATED'
END 'Aktion', buchungsid, buchungswert, buchungsdatum FROM cdc.dbo_buchungen_CT

Ergebnis:

Weitere Informationen zu den Statis gibt es hier:
http://technet.microsoft.com/en-us/library/bb522511.aspx

Sobald CDC aktiviert wird, werden folgende Tabellen erstellt:

  • cdc.change_tables
    Enthält alle CDC überwachten Tabellen
  • cdc.captured_columns
    Enthält die Felder der CDC überwachten Tabellen
  • cdc.dbo_buchungen_CT
    Eine solche Tabelle wird für jedes CDC Objekt erstellt und speichert die Datenänderungen
  • cdc.ddl_history
    Speichert Änderungen an der CDC überwachten Tabelle
  • cdc.index_columns
    Enthält die Index Felder der CDC Tabellen
  • cdc.lsn_time_mapping
    Über diese Tabelle kann nachvollzogen werden, wann welche Änderung durchgeführt wurde. Dies kann anhand der LSN mit der Benutzertabelle verglichen werden.

Weitere Informationen zu den Tabellen gibt es hier: http://technet.microsoft.com/de-de/library/bb500353.aspx

Weiterhin erstellt CDC noch zwei SQL Agent Jobs:

  1. cdc.CDC_capture
    Dieser Job steuert CDC für die jeweiligen konfigurierten Objekte die für CDC aktiviert worden sind
  2. cdc.CDC_cleanup
    Entfernt abgelaufene, bzw. deaktivierte CDC Objekte die sonst vom Capture Job ausgeführt werden

Hinweis:
Das Wiederherstellungsmodell für CDC ist nicht relavant. CDC liest lediglich aus dem Aktiven Teil des Transaktionslog und somit wir auch das Einfache Wiederherstellungsmodell unterstützt.

Fazit:
CDC eignet sich hervorragend für Auswertungszwecke um Datenveränderungen zu speichern und darzustellen. Eine Speicherung von Benutzernamen etc. ist nicht vorgesehen und könnte max. selber durch DML Trigger hinzugeführt werden.

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:

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.