Tabellen vor versehentlichem Löschen schützen

Ich kann mich an einige Fälle erinnern, als Produktionsdatenbanken aus einem Versehen oder fehlender Übersicht der Infrastruktur verschrottet wurden. Es fängt mit dem Löschen einer Tabelle an und endet mit der Bereitstellung einer nicht getesteten Version der DB auf dem Produktionssystem. Dazu gibt es noch Benutzertabellen in der SQL Server Systemdatenbanken, die eigentlich nicht dahin gehören.

Könnte man die Fälle vermeiden oder das Risiko zumindest reduzieren?

Als erste Maßnahme soll man die Sicherheit der SQL Server Umgebung konzipieren und den Kreis der Mitarbeiter reduzieren, die zu der Serverrolle sysadmin und der Datenbankrolle dbo (databaseowner) zugeordnet sind.

Leider lässt es sich nicht in allen Projekten und nicht bei allen Kunden vermeiden, dass die sa- oder dbo-Berechtigungen für viele zugänglich sind. Außerdem können die Fehler auch einem erfahrenen Entwickler oder Administrator passieren.

Ich habe in einem Buch den Vorschlag gelesen, View‘s mit der Option SCHEMABINDING zu erstellen um das Löschen der zugrunde liegenden Tabellen zu unterbinden. Die aus einer View referenzierten Tabellen können nur nach einem Löschen der View gelöscht werden. Das Beispiel 1 zeigt, wie es umgesetzt werden könnte.

Beispiel 1: Schutz einer Tabelle vor versehentlichem Löschen mit einer schemagebundenen View

use [Insidesql.org]
go
create table dbo.MyTable
(
    MyTableID int identity,
    Col1 nvarchar(55)
)
go
create view dbo.vwMyTable
    with schemabinding
as
select
    MyTableID,
    Col1
from
    dbo.MyTable
go
-- Den folgenden Versuch, die Tabelle dbo.MyTable zu löschen,
-- beenden mit der Fehlermeldung:
-- Msg 3729, Level 16, State 1, Line 1
-- Cannot DROP TABLE 'dbo.MyTable' because it is being referenced by object 'vwMyTable'.
drop table dbo.MyTable
go
-- Um die Tabelle löschen zu können, muss zuerst die View dbo.vwMyTable gelöscht werden.
drop view dbo.vwMyTable
drop table dbo.MyTable
go

Tatsächlich kann man View's mit SCHEMABINDING als Schutz vor einem Löschen der Tabellen nutzen, aber ich persönlich bevorzuge, einen DDL-Trigger einzusetzen. Beispiel 2 zeigt, wie man mit einem DDL-Trigger das Erstellen, Ändern und Löschen aller Tabellen der Datenbank unterbinden kann. Es gibt sogar weitere Möglichkeiten mit einem DDL-Trigger Functions, Stored Procedures und andere Datenbankobjekte vor einem Löschen zu schützen. Ein Überblick über die DDL-Ereignisgruppen, die einen DDL-Trigger ausführen, bekommt man in dem MSDN Artikel DDL-Ereignisgruppen.

Beispiel 2: DDL-Trigger als Überwachung der Datenbankänderungen

use [Insidesql.org]
go
create table dbo.MyTable
(
    MyTableID int identity,
    Col1 nvarchar(55)
)
go
if exists
(
    select *
    from sys.triggers
    where name = N'TR_TABLE_DENY_CAD'
        and parent_class_desc = N'DATABASE'
)
drop trigger TR_TABLE_DENY_CAD on database
go

create trigger TR_TABLE_DENY_CAD on database 
    for CREATE_TABLE, ALTER_TABLE, DROP_TABLE 
as 
begin
    print 'You must ask your DBA to create, alter or drop tables!' 
    rollback transaction
end
go

-- Den folgenden Versuch, die Tabelle dbo.MyTable zu löschen
-- beenden mit der Fehlermeldung:
-- You must ask your DBA to create, alter or drop tables!
-- Msg 3609, Level 16, State 2, Line 1
-- The transaction ended in the trigger. The batch has been aborted.
drop table dbo.MyTable
go

Vorteile der DDL-Trigger-Lösung

• In einem DDL-Trigger können mehrere Typen von DDL-Ereignissen unterbunden werden
• Es können mehrere DDL-Trigger für unterschiedliche DDL-Ereignisse erstellt werden
• Ein DDL-Trigger überwacht alle Objekte der DDL-Ereignisgruppe, damit ist keine Anpassung des Triggers notwendig, wen z.B. eine Tabelle umbenannt oder gelöscht wird
• Es ist kein Löschen des DDL-Triggers notwendig, wenn die DDL-Ereignisse zugelassen werden müssen, für die der Trigger erstellt ist. Es reicht den DDL-Trigger zu deaktivieren
• DDL-Trigger's sind von anderen Datenbankobjekten getrennt gespeichert und damit leicht zugänglich

Aktivieren und Deaktivieren vom DDL-Trigger
Wird ein DDL-Trigger zum Schützen von Datenbankobjekten vor versehentlichem Löschen eingesetzt, sollte man den Trigger vor der Bereitstellung der Datenbank per Hand über das SSMS (siehe Abbildung 1) in der Zieldatenbank deaktivieren. Um den Trigger nach der Bereitstellung automatisch zu aktivieren, kann man am Ende des Bereitstellungsskripts den folgenden Befehl einbauen:

enable trigger TR_TABLE_DENY_CAD on database
go

Abbildung 1: Deaktivieren von DDL-Trigger

Zusammenfassung
Man kann das Risiko einer versehentlichen Änderung einer Datenbank reduzieren, indem:

• die Datenbankberechtigungen sorgfältig verteilt werden
• Standarddatenbank von SQL Server-Anmeldungen gepflegt wird
• use-Anweisung in Bereitstellungsskripten benutzt wird
• schemagebundene View’s oder DDL-Trigger’s implementiert werden.