Aufgabe 11 - Benutzer und Rechte

Aufgabe 11 - Berechtigungskonzepte & Least Privilege in PostgreSQL

Worum geht es?

In dieser Übung wird für einen Streamingdienstleister ein Least-Privilege-Rollenkonzept entworfen und umgesetzt (siehe Kapitel 5 - Benutzer und Rechte). Kern ist die Trennung von Ownership und Privileges. Im Expertenteil geht es um Default Privileges und Zugriff ausschließlich über eine Stored Procedure.

Was Sie dafür brauchen

• Kapitel 5 - Benutzer und Rechte.
• Ein PostgreSQL-Server und ein SQL-Editor.

Welche Kompetenzen Sie erwerben und zeigen

• Sie erklären Authentifizierung, Autorisierung und das Schichtenmodell.
• Sie entwerfen Gruppenrollen und trennen Ownership von Privilegien.
• Sie lösen das Default-Privileges-Problem für künftige Tabellen.
• Sie kapseln Schreibzugriff hinter einer Stored Procedure.

Pädagogische Einordnung

• Reproduktion: die Zugriffskonzepte erklären (Teil A).
• Reorganisation und Transfer: Rollen anlegen und ein Schema härten (Teile B und C).
• Reflexion, Problemlösung und Urteilsbildung: Default Privileges und Prozedur-Kapselung lösen und beurteilen (Teil D).

Arbeitsaufträge

Die Übung ist auf etwa zwei Stunden ausgelegt. Teil D ist der Expertenteil.

Teil A - Konzepte

1. Auf etwa einer halben Seite erläutern: Authentifizierung gegen Autorisierung; das Schichtenmodell (kann ein Benutzer trotz Tabellenrechten scheitern, wenn CONNECT oder USAGE fehlen?); der Vorteil von Gruppenrollen gegenüber direkter Rechtevergabe.

Teil B - Rollen und Owner-Analyse

1. Für drei Rollentypen je einen Steckbrief (typische SQL-Befehle DML/DDL, nötige Privilegien auf Datenbank-, Schema- und Objektebene): Migration-Role (Owner), Application-Service-Role (DML), Audit/Reporting-Role (Read-Only).
2. Die Rollen in SQL anlegen und je einen Benutzer zuweisen (migration_bot, app_user, report_bot). Zusätzliche Schemata und Tabellen anlegen (app_data, billing) und mit Testbefehlen zeigen, welche Rolle worauf zugreifen darf und welche Fehlermeldung sonst kommt.
3. Zum häufigen Fehler „Application-User als Owner” eine Analyse: mindestens ein PRO (warum Entwickler das oft so machen) und mindestens drei CON, besonders zu DROP und ALTER, die sich nicht mit REVOKE entziehen lassen.

Teil C - Hardened Schema (Billing)

1. Für den Bereich „Streaming-Abrechnung” mit sensiblen Rechnungsdaten festlegen, welche Rolle die Tabellen erstellt, und begründen, warum app_user niemals Owner sein darf.
2. Die Befehle angeben, die nötig sind, damit report_bot überhaupt die erste Zeile im Schema billing lesen kann (CONNECT, USAGE, SELECT), und ausführen.

Teil D - Expertenteil: Default Privileges und Prozedur-Kapselung

1. Default Privileges: Zeigen Sie das Problem: Nach GRANT SELECT ON ALL TABLES IN SCHEMA billing TO report_bot legt ein Admin später invoices_2026 an, und report_bot kann sie nicht lesen. Erklären Sie warum und lösen Sie es mit ALTER DEFAULT PRIVILEGES, sodass auch künftige Tabellen automatisch lesbar sind. Beweisen Sie die Lösung, indem Sie nach dem ALTER DEFAULT PRIVILEGES eine neue Tabelle anlegen und mit report_bot lesen.

   ALTER DEFAULT PRIVILEGES IN SCHEMA billing
     GRANT SELECT ON TABLES TO report_bot;

2. Zugriff nur über eine Prozedur: Definieren Sie ein Szenario, in dem ein api_user eine bestimmte Änderung ausführen darf, aber keinen direkten INSERT/UPDATE/DELETE auf der Tabelle hat. Legen Sie eine Stored Procedure (oder Function) mit SECURITY DEFINER an, geben Sie api_user nur EXECUTE darauf und entziehen Sie ihm alle direkten Schreibrechte. Beweisen Sie: der direkte UPDATE scheitert, der Aufruf der Prozedur gelingt.

3. Recherche und Reflexion: Recherchieren Sie das Risiko von WITH GRANT OPTION und SUPERUSER-Accounts (auch in Cloud-Umgebungen wie AWS RDS) sowie ein Beispiel, wie begrenzte Rechte den Schaden einer SQL-Injection minimiert haben. Fassen Sie in vier bis fünf Sätzen zusammen, wie die Trennung von Ownership und Privilegien den Schaden im Ernstfall begrenzt.

Wissenscheck

1. Worin unterscheiden sich Authentifizierung und Autorisierung?
2. Warum kann ein Benutzer trotz Tabellenrechten scheitern?
3. Warum lassen sich ALTER und DROP nicht per REVOKE entziehen?
4. Wozu dient ALTER DEFAULT PRIVILEGES?
5. Wie erlaubt man eine Änderung nur über eine Prozedur, ohne direktes Schreibrecht zu geben?

Abgabe

• Ein strukturierter Bericht mit den Zugriffskonzepten, den Rollen-Steckbriefen samt Owner-Analyse, dem gehärteten Billing-Konzept und den realen Fallbeispielen.
• Eine SQL-Datei mit allen Rollen, Grants, Default Privileges, der Prozedur und den Test-/Fehlermeldungen.

---

HTL Villach, 2025-2026,
https://www.htl-villach.at