Besuchen Sie uns unter: http://www.itrain.de
Signieren von Modulen
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'
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“
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
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'
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' )
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
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
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
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'
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
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'
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
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.
Dieses Dokument finden Sie online unter http://www.itrain.de/knowhow/sql/2005/tsql/encryption/codesign.asp. Vielen Dank für Ihr Interesse.