{"id":99,"date":"2011-04-17T18:21:44","date_gmt":"2011-04-17T18:21:44","guid":{"rendered":"http:\/\/www.flip-design.de\/?p=99"},"modified":"2011-04-17T18:21:44","modified_gmt":"2011-04-17T18:21:44","slug":"server-trigger-logon-trigger","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=99","title":{"rendered":"Server Trigger: Logon Trigger"},"content":{"rendered":"

\"\"<\/a>In diesem Blog Eintrag geht es um Logon-Trigger. Analog zu den Einstell-M\u00f6glichkeiten im Microsoft Active Directory, wo man Benutzern verschiedene Zeiten voreinstellen kann, wann eine Anmeldung m\u00f6glich ist, kann dies auch im SQL Server nachvollzogen werden.<\/p>\n

Hierf\u00fcr ist lediglich eine kleine Infrastruktur notwendig. Eine Verwaltungsdatenbank in der die Benutzer gespeichert werden, wie auch eine Tabelle in der die Zeiten definiert werden.<\/p>\n

In folgendem Beispiel wird die Verwaltungsdatenbank erstellt mit einer Benutzertabelle in der die Benutzernamen gespeichert werden, eine Gruppentabelle um Benutzer zu Gruppen hinzuzuf\u00fcgen die gleichzeitig die Zeiten definieren.<\/p>\n

Mit diesem Code wird die Benutzer Tabelle erzeugt. Hier werden lediglich die Benutzernamen gespeichert:
\ncreate table dbo.benutzer (
\nuid uniqueidentifier default newid() primary key not null,
\nbenutzername varchar(255) not null unique);
\ngo<\/code><\/p>\n

Hier noch 2 Benutzer:
\ninsert into benutzer (benutzername) values ('Herta');
\ninsert into benutzer (benutzername) values ('Kerrin');
\ngo<\/code><\/p>\n

Um das Beispiel testen zu k\u00f6nnen, empfiehlt es sich auch diese Benutzer als SQL Authentifizierte Accounts auf dem Server anzulegen und der Datenbank zuzuordnen.<\/p>\n

Nun wird die Gruppen Tabelle erstellt. \u00dcber die Felder Beginn- und Endzeit wird die Zeit definiert, wo eine Anmeldung m\u00f6glich ist.
\ncreate table dbo.gruppen (
\nuid uniqueidentifier default newid() primary key not null,
\ngruppenname varchar(255) not null unique,
\nbeginnzeit time not null,
\nendzeit time not null);
\ngo<\/code><\/p>\n

Nun werden noch 3 Gruppen angelegt:
\ninsert into gruppen (gruppenname, beginnzeit, endzeit) values ('Vormittag', '07:00', '12:30');
\ninsert into gruppen (gruppenname, beginnzeit, endzeit) values ('Nachmittag', '12:30', '18:30');
\ninsert into gruppen (gruppenname, beginnzeit, endzeit) values ('Vollzeit', '07:00', '18:30');
\ngo<\/code><\/p>\n

Zuletzt wird noch die Zuordnungstabelle erstellt:
\n(F\u00fcr die \u00dcbersicht habe ich hier die Referentiellen Integrit\u00e4ten nicht erstellt)
\ncreate table bennutzer_gruppen (
\nbenutzer_uid uniqueidentifier not null,
\ngruppen_uid uniqueidentifier not null);
\ngo<\/code><\/p>\n

Nun werden die Benutzer noch jeweils einer Gruppe zugeordnet:
\n(Beim Test m\u00fcssen die ID’s angepasst werden)
\ninsert into bennutzer_gruppen values ('E4727227-7C64-46E0-8233-CE0A249AAAB5', 'D319D6EA-D280-4E65-8AC6-823100270667');
\ninsert into bennutzer_gruppen values ('ED3EEA47-E4BA-4E82-A6E7-D5A69E991C95', 'F5D9E222-98BE-455A-B5B5-62096CA254EE');
\ngo<\/code><\/p>\n

Nun wird noch eine View erstellt, die die Benutzer zu den jeweligen Gruppen ausgibt:
\ncreate view benutzerInGruppen as
\nSELECT\u00a0\u00a0\u00a0\u00a0 benutzer.benutzername, gruppen.gruppenname, gruppen.beginnzeit, gruppen.endzeit
\nFROM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 bennutzer_gruppen INNER JOIN
\nbenutzer ON bennutzer_gruppen.benutzer_uid = benutzer.uid INNER JOIN
\ngruppen ON bennutzer_gruppen.gruppen_uid = gruppen.uid
\ngo<\/code><\/p>\n

Ergebnis:
\n\"\"<\/a><\/p>\n

F\u00fcr den Trigger wird hier noch eine weitere View erstellt, die pr\u00fcfen soll, ob es den Benutzer auch gibt:
\ncreate view benutzernamen as
\nSELECT benutzername FROM benutzer WHERE benutzername = SUSER_NAME()
\ngo<\/code><\/p>\n

Damit die Benutzer beim Login auch pr\u00fcfen k\u00f6nnen, ob ein Login m\u00f6glich ist, wird noch eine Rolle erstellt die auf die beiden Sichten berechtigt und die Benutzer werden dieser Rolle hinzugef\u00fcgt:
\ncreate role benutzer;
\ngrant select on benutzerInGruppen to benutzer
\ngrant select on benutzernamen to benutzer
\nexec sp_addrolemember benutzer, herta;
\nexec sp_addrolemember benutzer, kerrin;
\ngo<\/code><\/p>\n

Ob die Datenbank Infrastruktur stimmt, kann mit folgenden Code \u00fcberpr\u00fcft werden:
\nEXECUTE AS LOGIN = 'kerrin'
\nSELECT * FROM benutzernamen
\nREVERT<\/code>
\nHiermit wird die Abfrage unter dem angegeben Benutzerkontext ausgef\u00fchrt.<\/p>\n

Nun wird der Servertrigger erstellt. Server Trigger befinden sich im SSMS hier:
\n\"\"<\/a><\/p>\n

Der Trigger soll folgende Voraussetzungen erf\u00fcllen:<\/p>\n