{"id":60,"date":"2011-03-24T16:54:18","date_gmt":"2011-03-24T16:54:18","guid":{"rendered":"http:\/\/www.flip-design.de\/?p=60"},"modified":"2011-04-04T18:21:30","modified_gmt":"2011-04-04T18:21:30","slug":"datenbank-trigger-loggen-von-berechtigungen","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=60","title":{"rendered":"Datenbank Trigger: Loggen von Berechtigungen"},"content":{"rendered":"<p><a href=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2011\/04\/dbtrigger_perm.png\"><img decoding=\"async\" loading=\"lazy\" class=\"alignleft size-full wp-image-61\" title=\"dbtrigger_perm\" src=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2011\/04\/dbtrigger_perm.png\" alt=\"\" width=\"131\" height=\"157\" \/><\/a>Datenbank Trigger eignen sich z.B. zum Mitschneiden von Berechtigungen die  innerhalb einer Datenbank vergeben werden. So kann bspw. der Server- oder  Datenbankadministrator pr\u00fcfen und sicherstellen das jegliche Datenbank  Berechtigungen die vergeben werden, von einem Datenbank Trigger erfasst und  protokolliert werden.<br \/>\nIn diesem Beispiel sollen jegliche \u00c4nderungen am  Berechtigungskonzept der Datenbank in einer Protokollierungstabelle gespeichert  werden.<\/p>\n<p>Zuerst wird eine Tabelle ben\u00f6tigt, in die der Datenbank Trigger  schreiben kann.<br \/>\nGespeichert wird hier innerhalb eines XML Feldes die sp\u00e4tere  Ausgabe, der Hostname vom Client der die \u00c4nderung veranlasst und das Programm  mit der die \u00c4nderung durchgef\u00fchrt wurde:<br \/>\n<code><br \/>\nCREATE TABLE dbo.logObjektBerechtigungen  (<br \/>\nuid uniqueidentifier default newid() not null primary key,<br \/>\nxmltext  XML NULL,<br \/>\nhostname VARCHAR(MAX) NULL,<br \/>\nprogrammname VARCHAR(MAX)  NULL,<br \/>\ndatum DATETIME DEFAULT GETDATE() NOT NULL,<br \/>\n);<\/code><\/p>\n<p>Nun wird der  Trigger erstellt. Dieser Trigger erfasst mehrere <a href=\"http:\/\/msdn.microsoft.com\/de-de\/library\/bb510452.aspx\" target=\"_blank\">DDL  Ereignisgruppen<\/a>:<\/p>\n<ul>\n<li>DDL_GDR_DATABASE_EVENTS<br \/>\nGRANT, DENY, REVOKE<\/li>\n<li>DDL_ROLE_EVENTS<br \/>\nCRATE ROLE, ALTER ROLE, DROP ROLE<\/li>\n<li>DDL_USER_EVENTS<br \/>\nCREATE USER, ALTER USER, DROP USER<\/li>\n<\/ul>\n<p>F\u00fcr jede DDL  Abfrage wird der Trigger ausgef\u00fchrt und schreibt das XML mit dem Host- und  Programmnamen in die Protokollierungstabelle:<br \/>\n<code>ALTER TRIGGER  TR_ObjektBerechtigungen<br \/>\nON DATABASE<br \/>\nFOR DDL_GDR_DATABASE_EVENTS,  DDL_ROLE_EVENTS, DDL_USER_EVENTS<br \/>\nAS<br \/>\nSET NOCOUNT ON<br \/>\nSET  QUOTED_IDENTIFIER ON<br \/>\nINSERT INTO dbo.logObjektBerechtigungen<br \/>\n(xmltext, hostname, programmname)<br \/>\nVALUES ((SELECT  EVENTDATA()), HOST_NAME(), PROGRAM_NAME());<br \/>\nSET  QUOTED_IDENTIFIER OFF<br \/>\nSET NOCOUNT OFF <\/code><\/p>\n<p>Da der Trigger mehrere  Ereignisgruppen protokolliert, bietet es sich an, f\u00fcr die verschiedenen Gruppen  eine Sicht zu erstellen. Hier wird eine Sicht erstellt, die das &#8222;eingefangene&#8220;  XML entsprechend nach dem Ereignis &#8222;Role&#8220; (enh\u00e4lt die Aktionen auf eine  Datenbankrolle) filtert und arbeitet dann das XML entsprechend auf:<br \/>\n<code>CREATE  VIEW dbo.RollenBerechtigungen<br \/>\nAS<br \/>\nSELECT<br \/>\nhostname, programmname<br \/>\n,xmltext.value('(\/EVENT_INSTANCE\/EventType)[1]',  'nvarchar(MAX)') 'Art'<br \/>\n,xmltext.value('(\/EVENT_INSTANCE\/ServerName)[1]',  'nvarchar(MAX)') 'Server  Name'<br \/>\n,xmltext.value('(\/EVENT_INSTANCE\/LoginName)[1]', 'nvarchar(MAX)')  'Login Name'<br \/>\n,xmltext.value('(\/EVENT_INSTANCE\/DatabaseName)[1]',  'nvarchar(MAX)')  'Datenbank'<br \/>\n,xmltext.value('(\/EVENT_INSTANCE\/ObjectName)[1]',  'nvarchar(MAX)')  'Object'<br \/>\n,xmltext.value('(\/EVENT_INSTANCE\/TSQLCommand\/CommandText)[1]',  'nvarchar(MAX)') 'TSQL Befehl'<br \/>\n,xmltext<br \/>\n,datum<br \/>\nFROM  logObjektBerechtigungen<br \/>\nWHERE  (xmltext.value('(\/EVENT_INSTANCE\/ObjectType)[1]', 'nvarchar(MAX)') =  'ROLE')<\/code> Weitere Sichten k\u00f6nnen dann relativ simpel erstellt werden indem  das XML in der Spalte &#8222;xmltext&#8220; entsprechend ausgewertet wird.<\/p>\n<p><strong>Download:<\/strong><\/p>\n<ul>\n<li><a href=\"download\/SQLServer_DDL_Berechtigungen.pdf\" target=\"_blank\">Download  Script<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Datenbank Trigger eignen sich z.B. zum Mitschneiden von Berechtigungen die innerhalb einer Datenbank vergeben werden. So kann bspw. der Server- oder Datenbankadministrator pr\u00fcfen und sicherstellen das jegliche Datenbank Berechtigungen die vergeben werden, von einem Datenbank Trigger erfasst und protokolliert werden. &hellip; <a href=\"https:\/\/www.flip-design.de\/?p=60\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[10,7,3],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/60"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=60"}],"version-history":[{"count":4,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/60\/revisions"}],"predecessor-version":[{"id":64,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/60\/revisions\/64"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=60"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=60"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=60"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}