Code & Queries

Um Spalten in SQL Server zu verschlüsseln und den Zugriff basierend auf Benutzerrollen zu steuern, können Sie eine Kombination aus Verschlüsselungstechniken und Berechtigungssteuerung verwenden. Hier ist eine Schritt-für-Schritt-Anleitung:

---

1. Spaltenverschlüsselung

SQL Server bietet verschiedene Möglichkeiten zur Verschlüsselung von Spalten:

a) Always Encrypted

• Always Encrypted ist eine Funktion, die es ermöglicht, Spalten so zu verschlüsseln, dass die Daten nur auf der Clientseite entschlüsselt werden können.
• Die Daten bleiben in der Datenbank verschlüsselt, und der SQL Server hat keinen Zugriff auf die Entschlüsselungsschlüssel.
• Schritte:
  1. Aktivieren Sie Always Encrypted für die gewünschte Spalte.
  2. Konfigurieren Sie die Schlüsselverwaltung (z. B. in Azure Key Vault oder einem lokalen Zertifikatspeicher).
  3. Stellen Sie sicher, dass nur autorisierte Anwendungen oder Benutzer Zugriff auf die Entschlüsselungsschlüssel haben.

b) Transparent Data Encryption (TDE)

• TDE verschlüsselt die gesamte Datenbank auf Dateiebene, nicht einzelne Spalten.
• Dies ist weniger granular, aber einfacher zu implementieren.

c) Cell-Level Encryption

• Verwenden Sie integrierte Funktionen wie ENCRYPTBYPASSPHRASE und DECRYPTBYPASSPHRASE, um einzelne Spalten zu verschlüsseln.

• Beispiel:

  -- Verschlüsseln
  UPDATE Tabelle
  SET GeheimeSpalte = ENCRYPTBYPASSPHRASE('MeinGeheimnis', KlartextDaten);
  
  -- Entschlüsseln
  SELECT DECRYPTBYPASSPHRASE('MeinGeheimnis', GeheimeSpalte) AS KlartextDaten
  FROM Tabelle;
  

---

2. Berechtigungen basierend auf Rollen steuern

Um den Zugriff auf die verschlüsselten Spalten zu steuern, können Sie SQL Server-Berechtigungen verwenden:

a) Rollen erstellen

Erstellen Sie Rollen und weisen Sie Benutzern diese Rollen zu:

CREATE ROLE RolleMitZugriff;
CREATE ROLE RolleOhneZugriff;

b) Berechtigungen vergeben

Gewähren oder verweigern Sie den Zugriff auf die Spalten basierend auf den Rollen:

-- Zugriff gewähren
GRANT SELECT ON Tabelle(GeheimeSpalte) TO RolleMitZugriff;

-- Zugriff verweigern
DENY SELECT ON Tabelle(GeheimeSpalte) TO RolleOhneZugriff;

c) Benutzer zu Rollen hinzufügen

Fügen Sie Benutzer den entsprechenden Rollen hinzu:

ALTER ROLE RolleMitZugriff ADD MEMBER Benutzer1;
ALTER ROLE RolleOhneZugriff ADD MEMBER Benutzer2;

---

3. Zugriff über Sichten steuern

Eine weitere Möglichkeit besteht darin, eine Sicht zu erstellen, die die verschlüsselten Daten nur für bestimmte Rollen anzeigt:

a) Sicht erstellen

Erstellen Sie eine Sicht, die die entschlüsselten Daten anzeigt:

CREATE VIEW SichtMitZugriff AS
SELECT ID, DECRYPTBYPASSPHRASE('MeinGeheimnis', GeheimeSpalte) AS KlartextDaten
FROM Tabelle;

b) Berechtigungen für die Sicht vergeben

Gewähren Sie Zugriff auf die Sicht nur für die Rolle mit Zugriff:

GRANT SELECT ON SichtMitZugriff TO RolleMitZugriff;
DENY SELECT ON SichtMitZugriff TO RolleOhneZugriff;

---

4. Testen und Überprüfen

• Testen Sie den Zugriff mit verschiedenen Benutzern, um sicherzustellen, dass die Berechtigungen korrekt funktionieren.
• Überprüfen Sie, ob die Daten für Benutzer ohne die entsprechende Rolle nicht sichtbar oder entschlüsselbar sind.

---

Zusammenfassung

• Verwenden Sie Always Encrypted oder Cell-Level Encryption, um Spalten zu verschlüsseln.
• Steuern Sie den Zugriff über Rollen und Berechtigungen.
• Optional können Sie Sichten verwenden, um den Zugriff weiter zu granularisieren.

Durch diese Kombination können Sie sicherstellen, dass nur autorisierte Benutzer Zugriff auf die verschlüsselten Spalten haben.