0Pricing
SQL Academy · Lektion

Rollen und Berechtigungen

Zugriff mit GRANT und REVOKE gewähren und entziehen

Rollen und Berechtigungen ist eine kostenlose SQL Academy-Lektion auf CoddyKit. Dies ist Lektion 1 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des SQL Academy-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der SQL Academy-Kurs umfasst insgesamt 4 Lektionen.

Was sind Rollen und Berechtigungen?

In SQL sind Rollen benannte Gruppen von Berechtigungen, die Benutzern zugewiesen werden können. Berechtigungen legen fest, welche Aktionen ein Benutzer oder eine Rolle an Datenbankobjekten wie Tabellen, Sichten und Funktionen ausführen darf.

Statt jedem Benutzer einzeln Berechtigungen zu erteilen, erstellen Sie eine Rolle mit den erforderlichen Berechtigungen und weisen diese Rolle anschließend mehreren Benutzern gleichzeitig zu. Dadurch lässt sich die Zugriffskontrolle in großem Maßstab wesentlich einfacher verwalten.

Eine Rolle erstellen

Verwenden Sie CREATE ROLE, um eine neue Rolle in PostgreSQL zu definieren. Eine Rolle kann je nach Konfiguration einen einzelnen Benutzer oder eine Benutzergruppe darstellen.

Rollen werden standardmäßig ohne Berechtigungen erstellt — Sie müssen ihnen den Zugriff auf Objekte ausdrücklich gewähren.

CREATE ROLE readonly_user;
CREATE ROLE app_writer;
CREATE ROLE db_admin;

Tabellenberechtigungen erteilen

Die Anweisung GRANT erteilt einer Rolle oder einem Benutzer die Berechtigung, bestimmte Vorgänge an einem Datenbankobjekt auszuführen. Zu den gängigen Berechtigungen auf Tabellenebene gehören SELECT, INSERT, UPDATE und DELETE.

Sie können eine einzelne oder mehrere Berechtigungen gleichzeitig erteilen, indem Sie sie durch Kommas trennen.

-- Grant SELECT only (read-only role)
GRANT SELECT ON employees TO readonly_user;

-- Grant multiple privileges
GRANT SELECT, INSERT, UPDATE ON orders TO app_writer;

Alle Berechtigungen erteilen

Wenn eine Rolle vollständigen Zugriff auf eine Tabelle benötigt, können Sie GRANT ALL PRIVILEGES verwenden, statt jede Berechtigung einzeln aufzulisten. Dadurch werden alle auf das angegebene Objekt anwendbaren Berechtigungen erteilt.

Seien Sie bei ALL PRIVILEGES vorsichtig — erteilen Sie diese Berechtigung nur Rollen, die tatsächlich die vollständige Kontrolle über das Objekt benötigen.

-- Grant full access to db_admin on a table
GRANT ALL PRIVILEGES ON employees TO db_admin;

-- Or the shorthand form
GRANT ALL ON orders TO db_admin;

Berechtigungen entziehen

Die Anweisung REVOKE entzieht einer Rolle oder einem Benutzer zuvor erteilte Berechtigungen. So schränken Sie den Zugriff ein, wenn sich Anforderungen ändern oder eine Rolle bestimmte Berechtigungen nicht mehr benötigt.

Nach einem REVOKE verliert die betroffene Rolle die angegebene Berechtigung für das benannte Objekt sofort.

-- Remove UPDATE access from app_writer
REVOKE UPDATE ON orders FROM app_writer;

-- Remove all privileges from a role
REVOKE ALL PRIVILEGES ON employees FROM readonly_user;

Eine Rolle einem Benutzer zuweisen

In PostgreSQL sind Benutzer ebenfalls Rollen — eine Rolle mit dem Attribut LOGIN kann eine Verbindung zur Datenbank herstellen. Sie weisen eine Gruppenrolle einer Anmeldungsrolle mit GRANT role TO user zu.

Nach der Zuweisung erbt der Benutzer alle Berechtigungen dieser Rolle, wodurch sich Berechtigungen für viele Benutzer gleichzeitig unkompliziert verwalten lassen.

-- Create a login user
CREATE ROLE alice WITH LOGIN PASSWORD 'secret123';

-- Assign the readonly role to alice
GRANT readonly_user TO alice;

-- Alice can now SELECT on tables granted to readonly_user

Eine Rolle von einem Benutzer entziehen

Um einem Benutzer eine Rollenzuweisung zu entziehen, verwenden Sie REVOKE role FROM user. Danach erbt der Benutzer die mit dieser Rolle verbundenen Berechtigungen nicht mehr.

Das ist hilfreich, wenn ein Mitarbeiter seine Zuständigkeiten wechselt oder die Organisation verlässt — Sie können die Rolle entziehen, ohne deren Berechtigungsdefinitionen ändern zu müssen.

-- Remove the readonly role from alice
REVOKE readonly_user FROM alice;

-- Alice no longer has SELECT on tables via that role

Berechtigungen auf Schemaebene

Bevor eine Rolle auf Objekte innerhalb eines Schemas zugreifen kann, benötigt sie die Berechtigung USAGE für dieses Schema. Ohne diese Berechtigung kann selbst eine Rolle mit SELECT-Berechtigung für eine bestimmte Tabelle keine Daten lesen, da sie den Schemapfad nicht auflösen kann.

Erteilen Sie immer USAGE für das Schema zusätzlich zu den Berechtigungen auf Objektebene.

-- Allow readonly_user to see inside the public schema
GRANT USAGE ON SCHEMA public TO readonly_user;

-- Then grant table-level privilege
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

Standardberechtigungen für zukünftige Objekte

Wenn neue Tabellen in einem Schema erstellt werden, erhalten vorhandene Rollen nicht automatisch Zugriff darauf. Verwenden Sie ALTER DEFAULT PRIVILEGES, um sicherzustellen, dass zukünftige Objekte, die von einer bestimmten Rolle erstellt werden, automatisch für eine andere Rolle zugänglich sind.

So vermeiden Sie ein häufiges Problem: Eine neu erstellte Tabelle ist für Anwendungsrollen nicht sichtbar, bis jemand daran denkt, manuell GRANT auszuführen.

-- Future tables created by the current user will be SELECTable by readonly_user
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly_user;

-- Future tables will allow INSERT/UPDATE/DELETE for app_writer
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer;

Die WITH GRANT OPTION

Standardmäßig kann eine Rolle, die eine Berechtigung erhält, diese Berechtigung nicht an andere Rollen weitergeben. Durch das Hinzufügen von WITH GRANT OPTION kann der Empfänger diese Berechtigung auch anderen erteilen.

Verwenden Sie diese Option mit Bedacht — die Rolle wird dadurch zu einer vertrauenswürdigen Stelle für die Weitergabe von Zugriffen, was bei übermäßiger Verwendung die Prüfung erschweren kann.

-- app_writer can now grant SELECT on orders to other roles
GRANT SELECT ON orders TO app_writer WITH GRANT OPTION;

-- app_writer can then do:
-- GRANT SELECT ON orders TO reporting_role;

Erteilte Berechtigungen anzeigen

PostgreSQL speichert Informationen zu Berechtigungen in Ansichten des Systemkatalogs. Sie können information_schema.role_table_grants abfragen, um zu sehen, welchen Rollen welche Berechtigungen für welche Tabellen erteilt wurden.

Der Backslash-Befehl \dp tablename in psql zeigt die Zugriffsliste für eine Tabelle ebenfalls in kompakter Form an.

-- List all table-level grants in the current database
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
ORDER BY grantee, table_name;

Kurzer Wissenscheck

Testen Sie Ihr Verständnis von Rollen und Berechtigungen in SQL.

Zusammenfassung: Rollen und Berechtigungen

In dieser Lektion haben Sie gelernt, wie SQL die Zugriffskontrolle mithilfe von Rollen und Berechtigungen verwaltet:

  • CREATE ROLE definiert eine neue Rolle oder ein neues Benutzerkonto
  • GRANT weist einer Rolle Berechtigungen (SELECT, INSERT, UPDATE, DELETE, ALL) für Objekte zu
  • REVOKE entzieht diese Berechtigungen
  • GRANT role TO user weist einem Anmeldungsbenutzer eine Gruppenrolle zu, sodass dieser deren Berechtigungen erbt
  • Rollen benötigen USAGE für das Schema, bevor sie auf darin enthaltene Objekte zugreifen können
  • ALTER DEFAULT PRIVILEGES stellt sicher, dass zukünftige Objekte automatisch zugänglich sind
  • WITH GRANT OPTION ermöglicht es dem Empfänger, die Berechtigung weiterzugeben

Minimale, rollenbasierte Berechtigungen sind ein grundlegendes Prinzip sicheren Datenbankdesigns — erteilen Sie nur, was benötigt wird, und entziehen Sie Berechtigungen umgehend, sobald sie nicht mehr erforderlich sind.

Häufig gestellte Fragen

Ist die Lektion „Rollen und Berechtigungen“ kostenlos?

Ja — der vollständige Text von „Rollen und Berechtigungen“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des SQL Academy-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der SQL Academy-Kurs umfasst insgesamt 4 Lektionen.

Was lerne ich in „Rollen und Berechtigungen“?

Zugriff mit GRANT und REVOKE gewähren und entziehen Du übst SQL Academy mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.

Brauche ich Erfahrung, um SQL Academy zu starten?

Keine Vorkenntnisse erforderlich. SQL Academy auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 1 von 4.

Wie lange dauert die Lektion „Rollen und Berechtigungen“?

Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.

Kann ich in dieser SQL Academy-Lektion Code schreiben und ausführen?

Ja. Jede SQL Academy-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.

Alle Lektionen in diesem Kurs

  1. Rollen und Berechtigungen
  2. Richtlinien für Sicherheit auf Zeilenebene
  3. Berechtigungen auf Spaltenebene
  4. Zugriffe prüfen
← Zurück zu SQL Academy