Signieren von Prozeduren

Die Möglichkeit Module digital zu signieren erweitert die Möglichkeiten der Berechtigungsvergabe im SQL Server 2005. Durch das Signieren von Code kann außerdem sichergestellt werden, dass keine Änderungen am Modul selbst vorgenommen werden können.

Funktionsweise

Beim Signieren von Modulen wird mit Hilfe einer Hash-Funktion ein Hash-Wert aus dem Inhalt des Moduls erzeugt. Dieser Hash-Wert wird anschließend mit dem privaten Schlüssel eines Zertifikats verschlüsselt. Dieser so verschlüsselte Wert – die Signatur – kann anschließend dem Modul zugeordnet werden. Wird ein so signiertes Modul später aufgerufen, so kann mit dem öffentlichen Schlüssel des Zertifikats geprüft werden, ob das Modul mit dem ursprünglich signierten Modul übereinstimmt. Ist diese Prüfung erfolgreich, werden im SQL Server die Zertifikatsberechtigungen – zusätzlich zu den Berechtigungen des Benutzers – ebenfalls erteilt.

Da bei diesem Vorgehen der private Schlüssel des Zertifikats nur zum Verschlüsseln verwendet wird, kann der private Schlüssel nach dem Signieren der Module aus der Datenbank entfernt werden. Dadurch ist sichergestellt, dass die Module zwar weiterhin ausgeführt werden können (der für das Entschlüsseln der Signatur notwendige öffentliche Schlüssel ist ja noch vorhanden), es ist jedoch nicht mehr möglich das Modul zu ändern und anschließend erneut zu signieren.

Signieren von Modulen – Schritt für Schritt

Die folgenden zwei Beispiele geben einen Überblick über die Verwendungsmöglichkeiten von signiertem Code. In beiden Beispielen werden gespeicherte Prozeduren mit Hilfe eines Zertifikats signiert.

Im ersten Beispiel soll eine Prozedur erstellt werden, mit deren Hilfe Sicherungen des Transaktionsprotokolls der Datenbank durchgeführt werden können. Benutzer in der Datenbank sollen nur die Möglichkeit haben über diese Prozedur Sicherungen erstellen zu können, d.h. sie sollen nicht Mitglieder der Datenbankrolle „db_backupoperator“ sein.

Um dieses Szenario umzusetzen werden die folgenden Schritte durchgeführt:

Erstellen eines neuen Zertifikats „BackupZertifikat“
Erstellen eines Zertifikatbenutzers „BackupZertifikatBenutzer“
Zuordnen des Zertifikatbenutzers zur Rolle „db_backupoperator“
Erstellen einer gespeicherten Prozedur zum Sichern des Transaktionsprotokolls in eine Datei.
Signieren der Prozedur mit dem zuvor erstellten Zertifikat
Sichern des privaten Schlüssels des Zertifikats
Entfernen des privaten Schlüssels aus der Datenbank

Erstellen eines neuen Zertifikats

Im ersten Schritt wird ein neues Zertifikat in der Datenbank erstellt. Der private Schlüssel des Zertifikats wird durch ein Kennwort gesichert:

CREATE CERTIFICATE BackupZertifikat
ENCRYPTION BY PASSWORD = '#sdW2A@erQp0as'
WITH SUBJECT = 'Datenbanksicherung',
START_DATE = '20060101'

Listing: Erstellen eines neuen Zertifikats für das Signieren von Modulen

Erstellen eines Zertifikatsbenutzers

Anschließend wird ein “Zertifikatsbenutzer” erstellt. Dieser Schritt ist notwendig, da einem Zertifikat selbst keine Berechtigungen erteilt werden können (Zertifikate sind keine Prinzipale). Der neu erstellte Zertifikatsbenutzer wird der Rolle „db_backupoperator“ zugeordnet:

CREATE USER BackupZertifikatBenutzer
FROM CERTIFICATE BackupZertifikat
EXEC sp_addrolemember 'db_backupoperator', 'BackupZertifikatBenutzer'
Listing: Erstellen eines Zertifikatbenutzers mit Zugehörigkeit zur festen Datenbankrolle „db_backupoperator“

Anschließend wird die gespeicherte Prozedur “BackupLogAdventureWorks” erstellt. Diese Prozedur sichert das Transaktionsprotokoll der Datenbank „AdventureWorks“ in eine Datei.

CREATE PROCedure dbo.BackupLogAdventureWorks
AS
SET NOCOUNT ON
BACKUP LOG AdventureWorks
TO DISK = 'E:\SQLBackup\AWLOG.BAK' WITH NOINIT
GO

Listing: Erstellen einer gespeicherten Prozedur zum Sichern des Transaktionsprotokolls in eine Datei

Die gespeicherte Prozedur kann nun mit Hilfe des Zertifikats signiert werden.

ADD SIGNATURE
TO dbo.BackupLogAdventureWorks
BY CERTIFICATE BackupZertifikat
WITH PASSWORD = '#sdW2A@erQp0as'

Listing: Signieren der Prozedur „BackupLogAdventureWorks“ mit dem Zertifikat „BackupZertifikat“

Nachdem die Prozedur signiert wurde, wird der private Schlüssel nicht mehr benötigt, da für das Entschlüsseln der Signatur ja der öffentliche Schlüssel des Zertifikats verwendet wird. Zur Sicherheit wird das Zertifikat vor dem Entfernen des privaten Schlüssel gesichert:

BACKUP CERTIFICATE BackupZertifikat
TO FILE = 'C:\SAFE\BackupZertifikat.CER'
WITH PRIVATE KEY ( FILE = 'C:\SAFE\BackupZertifikat.PVK',
ENCRYPTION BY PASSWORD = '#tAiD7D33§#aQd',
DECRYPTION BY PASSWORD = '#sdW2A@erQp0as' )

ALTER CERTIFICATE BackupZertifikat
REMOVE PRIVATE KEY

Listing: Sichern des Zertifikats in eine Datei und Entfernen des privaten Schlüssels aus der Datenbank

Durch das Hinzufügen der Signatur zur Prozedur werden nun beim Ausführen der Prozedur zusätzlich die Berechtigungen des Zertifikatsbenutzers aktiv. Um die Wirksamkeit zu demonstrieren wird ein „einfacher“ Datenbankbenutzer mit dem Namen „demoUser“ angelegt. Dieser Benutzer erhalt die Ausführungsberechtigung (EXECUTE) für die gespeicherte Prozedur.

CREATE USER demoUser WITHOUT LOGIN
GRANT EXEC ON dbo.BackupLogAdventureWorks
TO demoUser

Listing: Erstellen eines Datenbankbenutzers und Erteilen der Execute-Berchtigung für die Prozedur „BackupLogAdventureWorks“

Wird die BACKUP-Anweisung vom Benutzer “demoUser” direkt ausgeführt, so wird die Ausführung verweigert. Wird hingegen die gespeicherte Prozedur verwendet, so kann das Transaktionsprotokoll gesichert werden.

EXECUTE AS USER = 'demoUser'
SELECT USER_NAME()
BEGIN TRY
BACKUP LOG AdventureWorks TO DISK = 'E:\SQLBackup\AWLOG.BAK' WITH NOINIT
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
EXEC dbo.BackupLogAdventureWorks
REVERT

Listing: Testen der signierten Prozedur: Das direkte Ausführen von BACKUP LOG ist nicht möglich; das Sichern des Protokolls durch die Prozedur ist jedoch zulässig

demoUser(1 Zeile(n) betroffen)

BACKUP LOG wird fehlerbedingt beendet.

2 Seiten wurden für die 'AdventureWorks'-Datenbank, Datei 'AdventureWorks_Log' für Datei 8, verarbeitet.BACKUP LOG hat erfolgreich 2 Seiten in 0.167 Sekunden verarbeitet (0.073 MB/s).

Listing: Ausgabe nach Ausführen des Tests aus Listing

Sollen mit Hilfe eines Zertifikats Berechtigungen auf Serverebene erteilt werden, so muss für das Zertifikat auch eine „Zertifikatsanmeldung“ erstellt werden. Das folgende Beispiel zeigt, wie man in diesem Fall vorgehen kann. Über eine Prozedur soll einem Benutzer in der AdventureWorks-Datenbank die Möglichkeit gegeben werden einen Datenbank-Snapshot zu erstellen. Dazu ist die Berechtigung „CREATE ANY DATABASE“ auf Serverebene erforderlich.

Im Vergleich zum vorangegangenen Beispiel müssen zusätzlich die folgenden Schritte ausgeführt werden:

Der öffentliche Schlüssel des verwendeten Zertifikats muss in die master-Datenbank geladen werden
Eine neue Anmeldung muss für dieses Zertifikat erstellt werden
Der neuen „Zertifikatsanmeldung“ wird die Serverrolle „dbcreator“ zugewiesen

Das folgende Beispielskript erledigt diese drei zusätzlichen Schritte. Dabei wird der öffentliche Schlüssel des Zertifikats aus einer Datei geladen:

USE master
GO
CREATE CERTIFICATE SnapshotAdmZertifikat
FROM FILE = 'C:\SAFE\SnapshotAdmZertifikat.CER'

CREATE LOGIN SnapshotAdmZertifikatAnmeldung
FROM CERTIFICATE SnapshotAdmZertifikat
GO
-- Für die Zertifikatsanmeldung wird keine CONNECT-Berechtigung
-- benötigt
REVOKE CONNECT SQL
FROM SnapshotAdmZertifikatAnmeldung
GO
-- Die Zertifikatsanmeldung wird der Serverrolle “dbcreator”-- zugeordnet:
EXEC sp_addsrvrolemember 'SnapshotAdmZertifikatAnmeldung', 'dbcreator'
GO

Listing: Laden des öffentlichen Schlüssels des Zertifikats in die master-Datenbank und Anlegen einer „Zertifikatsanmeldung“

Nachdem die Zertifikatsanmeldung erstellt ist, kann in der Datenbank AdventureWorks eine gespeicherte Prozedur zum Erstellen des Datenbank-Snapshots erstellt und signiert werden. Für das Signieren wird das Zertifikat – in diesem Fall inclusive des privaten Schlüssels – auch in die Adventureworks-Datenbank geladen. Anschließend wird die Prozedur signiert. Auch in diesem Beispiel könnte der private Schlüssel des Zertifikats – wie im vorangegangenen Beispiel – nach dem Signieren wieder aus der Datenbank gelöscht werden.

USE AdventureWorks
GO
-- Laden des Zertifikats incl. privatem Schlüssel
CREATE CERTIFICATE SnapshotAdmZertifikat
FROM FILE = 'C:\SAFE\SnapshotAdmZertifikat.CER'
WITH PRIVATE KEY ( FILE = 'C:\SAFE\SnapshotAdmZertifikat.PVK',
DECRYPTION BY PASSWORD = 'q@21alödssd89jkwesdiudds',
ENCRYPTION BY PASSWORD = '@alkjiooxcA00a$2' );
GO
-- Erstellen einer Prozedur zum Erstellen eines Datenbanksnapshots
CREATE PROC dbo.createSnapshot
AS
SET NOCOUNT ON
DECLARE @Anweisung nvarchar(MAX)
DECLARE @snapshotname sysname
SET @snapshotname = 'AdventureWorks_Snap_' + CONVERT(nvarchar(10), GetDate(), 112)
IF EXISTS (SELECT * FROM sys.databases WHERE name = @snapshotname)
PRINT 'Heute wurde bereits ein Snapshot erstellt.'
ELSE
BEGIN
SET @Anweisung = 'CREATE DATABASE ' + QUOTENAME(@snapshotname) + ' ON ( NAME = AdventureWorks_Data, FILENAME = ''E:\SQLDATEN\' + @snapshotname + '.ss '') AS SNAPSHOT OF [AdventureWorks]'
EXEC ( @Anweisung )
SELECT * FROM sys.databases WHERE name = @snapshotname
END
GO
-- Die Prozedur wird mit dem Zertifikat signiert:
ADD SIGNATURE TO dbo.createSnapshot
BY CERTIFICATE SnapshotAdmZertifikat
WITH PASSWORD = '@alkjiooxcA00a$2'

Zum Testen der Prozedur werden eine SQL Server-Anmeldung und ein zugehöriger Datenbankbenutzer angelegt. Anschließend kann die Prozedur im Kontext des neu angelegten Benutzers ausgeführt werden:

CREATE Login DEMOLOGIN WITH PASSWORD = 'testaasdfasdf33!'
CREATE USER DEMOLOGIN
GRANT EXEC ON dbo.createSnapshot
TO demologin
EXECUTE AS LOGIN='demologin'
EXEC dbo.createSnapshot
REVERT

Listing: Testen der signierten Prozedur „createSnapshot“

Wichtig:

Die Signatur wird grundsätzlich automatisch vom Modul entfernt, sobald die ALTER PROCEDURE Anweisung aufgerufen wird; dabei ist es unerheblich, ob der Inhalt der Prozedur selbst tatsächlich geändert wurde.

Link-Tipp zum Thema "Microsoft SQL Server": www.sqldts.com/

Sehr schöne Site zum Thema "Data Transformation Services"