SQL Server – GRANT, DENY, REVOKE
Berechtigungen verstehen und richtig einsetzen

1. Einleitung – Grundlagen der Berechtigungssteuerung

Jede Datenbankanwendung benötigt ein durchdachtes Sicherheitskonzept. Die zentralen T‑SQL‑Anweisungen zur Vergabe, Verweigerung und zum Entzug von Rechten heißen GRANT, DENY und REVOKE. Sie steuern, wer welche Aktionen auf welchen Objekten ausführen darf. In diesem Artikel erkläre ich die Unterschiede, zeige praktische Beispiele und gebe Richtlinien für den Alltag.

2. GRANT – Rechte erteilen

GRANT weist einem Sicherheitskonto (Login, Benutzer oder Rolle) eine bestimmte Berechtigung zu. Die Syntax ist einheitlich: GRANT <Berechtigung> ON <Objekt> TO <Empfänger>. Fehlen weitere Einschränkungen, ist das Recht unbeschränkt gültig.

-- Beispiel: Leserechte auf eine Tabelle erteilen 
USE SalesDB; 
GRANT SELECT ON dbo.Orders TO ReportingUser; 
 
-- Mehrere Rechte auf einmal 
GRANT SELECT, INSERT, UPDATE ON dbo.Products TO AppRole; 
 
-- Berechtigung auf Schema-Ebene (alle Tabellen des Schemas) 
GRANT SELECT ON SCHEMA::Sales TO SalesReader; 
 
-- Ausführungsrecht auf gespeicherte Prozedur 
GRANT EXECUTE ON dbo.usp_GetCustomerData TO JuniorDeveloper;

3. DENY – Rechte explizit verweigern

DENY ist die schärfste Anweisung: Sie verweigert eine Berechtigung, selbst wenn der Benutzer sie über eine Rolle oder Gruppenmitgliedschaft erhalten hätte. DENY hat Vorrang vor GRANT. Verwenden Sie es sparsam – meist ist der Verzicht auf GRANT (also keine Erteilung) ausreichend.

-- Einer bestimmten Person explizit den Zugriff auf eine Tabelle verwehren 
DENY SELECT ON dbo.Salary TO JohnDoe; 
 
-- Verweigerung auf Spaltenebene (seit SQL Server 2012) 
DENY SELECT ON dbo.Employees (SSN) TO HRRole;

4. REVOKE – Berechtigungen entziehen

REVOKE entfernt eine zuvor erteilte oder verweigerte Berechtigung. Es ist der neutrale Zustand – weder erlaubt noch verboten. Nach einem REVOKE entscheiden allein die anderen Rollen- und Gruppenmitgliedschaften des Benutzers über den Zugriff.

-- Eine erteilte Berechtigung zurücknehmen 
REVOKE SELECT ON dbo.Orders FROM ReportingUser; 
 
-- Eine Verweigerung aufheben (die Berechtigung bleibt aber nicht erteilt) 
REVOKE DENY SELECT ON dbo.Salary TO JohnDoe;

5. Vorrangregeln: Wer gewinnt bei Konflikten?

Ein Benutzer kann Berechtigungen aus verschiedenen Quellen erhalten: eigene GRANT, Mitgliedschaft in einer Rolle, Mitgliedschaft in einer Windows-Gruppe. Die folgende Hierarchie entscheidet:

1. DENY auf Benutzerebene – hat immer Vorrang, egal was Rollen oder Gruppen sagen.
2. DENY auf Rollenebene – blockiert auch, wenn der Benutzer über andere Rollen Rechte hätte.
3. GRANT auf Benutzerebene – erlaubt Zugriff, sofern kein DENY vorliegt.
4. GRANT über Rollen oder Gruppen – gilt, wenn keine höhere Verweigerung existiert.
5. Keine Berechtigung (implizit) – Zugriff verweigert.

-- Beispielkonflikt 
-- User ist Mitglied der Rolle SalesReader (hat SELECT auf Orders) 
GRANT SELECT ON dbo.Orders TO SalesReader; 
-- Aber der User selbst bekommt ein DENY 
DENY SELECT ON dbo.Orders TO JohnDoe; 
-- Ergebnis: JohnDoe kann nicht auf Orders zugreifen (DENY auf Benutzerebene gewinnt).

6. Beispiele aus der Praxis – typische Szenarien

Szenario 1: Anwendungsleser

Eine App benötigt nur Leserechte auf einige Tabellen, aber keine Schreibrechte.

CREATE USER AppReader FOR LOGIN AppLogin; 
GRANT SELECT ON dbo.Products TO AppReader; 
GRANT SELECT ON dbo.Categories TO AppReader; 
-- Keine INSERT/UPDATE/DELETE Rechte → sicher

Szenario 2: Temporäre Sperrung eines Mitarbeiters

Ein Mitarbeiter wechselt die Abteilung und soll während der Übergangszeit keinen Zugriff auf die alte Tabelle haben, aber seine Rolle behalten.

DENY SELECT, INSERT, UPDATE, DELETE ON dbo.OldDepartmentData TO [DOMAIN\JohnDoe]; 
-- Nach der Übergangszeit das DENY wieder aufheben 
REVOKE SELECT, INSERT, UPDATE, DELETE ON dbo.OldDepartmentData TO [DOMAIN\JohnDoe];

Szenario 3: Nur bestimmte Spalten sichtbar machen

-- Berechtigung auf zwei Spalten einer Tabelle erteilen (SQL Server 2012+) 
GRANT SELECT ON dbo.Employees (Name, Department) TO TeamLead; 
-- Alle anderen Spalten (z. B. Gehalt) sind für TeamLead nicht sichtbar.

7. Überprüfen vorhandener Berechtigungen

Mit folgenden Abfragen behalten Sie den Überblick, wer welche Rechte hat.

-- Serverweite Berechtigungen eines Logins 
SELECT class_desc, permission_name, state_desc 
FROM sys.server_permissions 
WHERE grantee_principal_id = DATABASE_PRINCIPAL_ID('MyLogin'); 
 
-- Datenbankweite Berechtigungen eines Benutzers 
SELECT class_desc, object_name(major_id) AS ObjectName, 
permission_name, state_desc 
FROM sys.database_permissions 
WHERE grantee_principal_id = DATABASE_PRINCIPAL_ID('MyUser'); 
 
-- Mitgliedschaften in Datenbankrollen 
SELECT r.name AS RoleName 
FROM sys.database_role_members rm 
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id 
WHERE rm.member_principal_id = DATABASE_PRINCIPAL_ID('MyUser');

8. Tipps für den Alltag – häufige Fehler vermeiden

• Niemals DENY für öffentliche Rollen – DENY auf die Public-Rolle wirkt für alle Benutzer und kann unerwartete Sperren erzeugen.
• Verwenden Sie Objekteigentümerschaften – Der Eigentümer (z. B. dbo) kann immer alles, auch ohne explizites GRANT.
• Beachten Sie die Vererbung über WITH GRANT OPTION – Wer eine Berechtigung mit GRANT ... WITH GRANT OPTION erhält, darf sie an andere weitergeben. Verwenden Sie dies nur bewusst.
• Testen Sie Berechtigungen mit EXECUTE AS USER – So simulieren Sie die Rechte eines anderen Benutzers, ohne sich aus- und wieder einloggen zu müssen.

EXECUTE AS USER = 'JohnDoe'; 
SELECT * FROM dbo.Orders; -- Test mit Johns Rechten 
REVERT;

9. Zusammenfassung und Merkregeln

• GRANT = erlaubt etwas.
• DENY = verbietet etwas (stärker als alle GRANT).
• REVOKE = neutralisiert eine frühere GRANT oder DENY.
• Verwenden Sie DENY nur für Ausnahmen, nicht als Standard.
• Nutzen Sie Rollen, um Rechte zu bündeln und Benutzer zuzuordnen.
• Überprüfen Sie regelmäßig mit den Systemansichten, wer welche Rechte hat.

powered by dtcSoftware