IT Forum Nachlese und Erweiterung des Verschlüsselungs-Beispiels

  • Article

Vorige Woche war ich auf dem Microsoft IT Forum in Barcelona (endlich ein wenig Wärme!) und habe 2 Sessions gehalten: Meine SQL Server BI Demo und einen Chalk Talk über Verschlüsselung in SQL Server 2005 mit Rafal Lukawiecki und Maciej Pilecki. Das war eine gute Kombination - der Krypto-Experte Rafal und wir als Datenbanker. Maciej Pilecki hat mir auch eine neue Erkenntnis gebracht, wie sich ein "Chef"-Szenario mit Verschlüsselung einrichten läßt:

Voraussetzung: 2 Personen haben je einen symmetrischen Schlüssel und ein Zertifikat, das mit dem Database Master Key verschlüsselt ist (Rafal würde jetzt sagen: Der private Schlüssel im Schlüsselpaar des Zertifikats ist mit dem Database Master Key verschlüsselt). Mit diesem Schlüssel und Zertifikat verschlüsseln sie Daten in der Datenbank. Problem jetzt: Ein Chef soll Zugriff auf die verschlüsselten Daten beider Personen (aber nicht von anderen!) bekommen. Will man das mit den vorhandenen Zertifikaten machen, so muss der dritte zwar nur REFERENCES-Rechte auf die Schlüssel bekommen (eigentlich reicht sogar VIEW DEFINITION), aber er braucht CONTROL, also Eigentümer-gleiche Rechte auf die Zertifikate! Das ist nicht gut.

Die Lösung besteht darin, der dritten Person ein eigenes Zertifikat zu erzeugen und die vorhandenen symmetrischen Schlüssel zusätzlich mit diesem Zertifikat (ja, Rafal, mit dem privaten Schlüssel aus dem Schlüsselpaar des Zertifikats) zu verschlüsseln. Das geht so:

-- Neuer benutzer Chef
-- Soll Daten beider Doktoren lesen können

Create

login Chef with password = 'Passwort-kommt-hier'
go

use

ClinicDB

create

user Chef
go

-- Zertifikat für Chef

create certificate ChefCert
authorization Chef with subject = 'ChefCert'
go

--Schlüssel öffnen zum Ändern (als Admin!)

open symmetric key Doc1Key decryption by certificate Doc1Cert
open symmetric key Doc2Key decryption by certificate Doc2Cert

-- Verschlüsselung zu vorhandenen Doc-Keys hinzufügen

alter symmetric key Doc1Key
add encryption by certificate ChefCert

alter

symmetric key Doc2Key
add encryption by certificate ChefCert

close

all symmetric keys

-- ansehen, jeder Key hat 2 Encryptions!
select * from sys.key_encryptions

-- Rechte an den Schlüsseln

GRANT REFERENCES ON SYMMETRIC KEY::Doc1Key TO Chef
GRANT REFERENCES ON SYMMETRIC KEY::Doc2Key TO Chef
GRANT SELECT ON PatientTable TO Chef

-- Test

execute as login = 'Chef'

--Öffne den Key

open symmetric key Doc1Key decryption by certificate ChefCert
open symmetric key Doc2Key decryption by certificate ChefCert

--Entschlüssseln der Daten

select Id, name, Docname, 
convert(varchar,decryptbykey(SSN)) as SSN,
convert (varchar,decryptbykey(Problem)) as Ailment
from PatientTable

--Log out Chef

close all symmetric keys
revert

Hier noch 2 Bilder von der Session:

Gruß,
Steffen