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:

Schreibe einen Kommentar