LU12c: MySQL – Rollen & Berechtigungen im Reiseblog

Sie können …

• den Unterschied zwischen Anwendungsbenutzer und Datenbankbenutzer erklären.
• mit den Befehlen CREATE USER, GRANT, REVOKE, CREATE ROLE in MySQL arbeiten.

Im Beispielprojekt travel_blog gibt es eine Tabelle users für die Benutzer der WordPress-Applikation Reiseblog. Diese Einträge gehören zur Webanwendung, nicht zur MySQL-Benutzerverwaltung.

MySQL-Benutzerkonten werden dagegen im System mysql.user gespeichert. Das heisst, diese User haben Zugriff auf (Teile) der Datenbank in MySQL:

• 'caro'@'%'
• 'martin'@'%'
• 'travel_app'@'localhost'

Wichtig: Die Einträge in travel_blog.users und in mysql.user sind zwei verschiedene Ebenen, auch wenn sie zufällig gleich heissen:

• travel_blog.users → Wer darf sich im Reiseblog einloggen? (Anwendung)
• mysql.user → Wer darf direkt in MySQL Änderungen machen? (Datenbank)

Der Benutzer root wird bei der MySQL-Installation automatisch erstellt.

• root hat volle Kontrolle über den Server (alle Datenbanken, alle Tabellen, alle Benutzer).
• Anwendungen sollten niemals mit root arbeiten.

Besser ist:

• spezifische Admin-Benutzer (z. B. caro, martin) mit einer Admin-Rolle (db_admin),
• eine eigene technische Benutzerkennung für die Anwendung (z. B. travel_app), die nur die benötigten Rechte erhält.

So setzen Sie das Prinzip der minimalen Privilegien auch auf Datenbankebene um.

Annahme: Die Datenbank travel_blog mit den Tabellen posts, users, categories, post_category existiert bereits.

Wir ergänzen eine Tabelle comments für Blog-Kommentare:

USE travel_blog;
 
CREATE TABLE comments (
id INT AUTO_INCREMENT PRIMARY KEY,
post_id INT NOT NULL,
user_id INT NOT NULL,
comment_text TEXT NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_comments_post
FOREIGN KEY (post_id) REFERENCES posts(id)
ON DELETE CASCADE,
CONSTRAINT fk_comments_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
); 

Damit ist technisch klar, wo Kommentare gespeichert werden und wie sie mit Posts und Benutzer*innen verknüpft sind.

Wir bilden die im Text genannten Personen und Aufgaben im MySQL-Rollenkonzept ab.

* db_admin

• volle Rechte auf travel_blog.*
• darf Rechte weitergeben (GRANT OPTION)
• gedacht für: Caro Steig, Martin Merten

* post_editor

• SELECT/INSERT/UPDATE/DELETE auf posts und categories
• z. B. für: Shaolin Tran (Blog-Redaktion)

* comment_moderator

• SELECT/INSERT/UPDATE/DELETE auf comments
• z. B. für: Joachim Steig (Moderation der Kommentare)

* app_readwrite

• SELECT/INSERT/UPDATE/DELETE auf den Anwendungstabellen
• wird von der späteren Backend-Anwendung (Node.js / Express) verwendet

CREATE ROLE db_admin;
CREATE ROLE post_editor;
CREATE ROLE comment_moderator;
CREATE ROLE app_readwrite; 

Merksatz: Vergeben Sie Berechtigungen wenn möglich an Rollen, nicht direkt an einzelne Benutzer. Benutzer bekommen danach nur noch Rollen zugewiesen – das ist übersichtlicher und skalierbarer.

Jetzt hinterlegen Sie bei jeder Rolle, was sie darf.

GRANT ALL PRIVILEGES
ON travel_blog.*
TO db_admin
WITH GRANT OPTION; 

Diese Rolle darf alles in der Datenbank travel_blog und darf diese Rechte auch an andere weitergeben.

GRANT SELECT, INSERT, UPDATE, DELETE
ON travel_blog.comments
TO comment_moderator; 

Wenn Sie möchten, dass normale Blog-Besucher*innen nur eigene Kommentare anlegen können, bekommt der Webserver später weniger Rechte (z. B. nur INSERT und SELECT) und die Businesslogik prüft, wer was ändern darf.

GRANT SELECT, INSERT, UPDATE, DELETE
ON travel_blog.*
TO app_readwrite; 

Diese Rolle wird später vom Backend (Node.js / Express) verwendet, um über eine REST-API CRUD-Operationen auszuführen – aber nur auf der Datenbank travel_blog, nicht auf dem ganzen MySQL-Server.

Nun legen Sie wenige, klar definierte MySQL-Benutzer an und geben ihnen die passenden Rollen.

Beispielhafte Benutzer:

• caro – Admin im Reiseblog-Projekt
• martin – Admin im Reiseblog-Projekt
• shaolin – Redaktorin für Blogbeiträge
• joachim – Moderator für Kommentare
• travel_app – technischer Benutzer für die Webanwendung

CREATE USER 'caro'@'%'
IDENTIFIED BY 'CaroS!2025';
 
CREATE USER 'martin'@'%'
IDENTIFIED BY 'MartinM!2025';
 
CREATE USER 'shaolin'@'%'
IDENTIFIED BY 'Shaolin!2025';
 
CREATE USER 'joachim'@'%'
IDENTIFIED BY 'Joachim!2025';
 
CREATE USER 'travel_app'@'localhost'
IDENTIFIED BY 'TravelApp!2025'; 

*(Hinweis: Verwenden Sie in der Praxis stärkere und individuelle Passwörter – diese dienen nur als Beispiel im Unterricht.)*

-- Admin-Rolle für Caro und Martin
GRANT db_admin
TO 'caro'@'%', 'martin'@'%';
 
-- Editor-Rolle für Shaolin
GRANT post_editor
TO 'shaolin'@'%';
 
-- Kommentar-Moderation für Joachim
GRANT comment_moderator
TO 'joachim'@'%';
 
-- Rolle für die Anwendung
GRANT app_readwrite
TO 'travel_app'@'localhost'; 

Damit ist die Verbindung klar:

* Caro und Martin: Admins mit voller Verantwortung für die Datenbank. * Shaolin: Darf Inhalte (Posts, Kategorien) bearbeiten, aber keine Struktur ändern. * Joachim: Darf Kommentare moderieren. * travel_app: Wird vom Backend genutzt – kein Login durch reale Personen.

Um zu prüfen, welche Rechte ein Benutzer effektiv hat, verwenden Sie:

SHOW GRANTS FOR 'caro'@'%';
SHOW GRANTS FOR 'shaolin'@'%';
SHOW GRANTS FOR 'travel_app'@'localhost'; 

MySQL zeigt dabei an, welche Rollen und Privilegien dem jeweiligen Account zugeordnet sind.

Wenn sich Aufgaben ändern (z. B. verlässt jemand das Projekt), können Sie Rollen oder Benutzer mit REVOKE und DROP ROLE bzw. DROP USER wieder sauber entfernen.

Die Rollen und Berechtigungen im Reiseblog-Beispiel setzen zentrale Datenschutzprinzipien technisch um:

* Schutz vor unbefugtem Zugriff

• Nur definierte Rollen (z. B. db_admin, post_editor) dürfen auf bestimmte Tabellen zugreifen.

* Vertraulichkeit, Integrität, Verfügbarkeit

• Admins dürfen mehr, Redaktor*innen nur so viel wie nötig, die App nur, was für ihren Zweck erforderlich ist.

* Need-to-know / Prinzip der minimalen Privilegien

• Keine Nutzung von root durch Anwendungen.
• travel_app hat nur Rechte in travel_blog, nicht auf dem gesamten Server.

Die Logik ist die gleiche wie in den Schul-Beispielen (WebUntis, Moodle, Absenzen-Tool, Notenbuch, Lohnliste):

* Schulleitung und Verwaltung haben mehr Rechte als Lehrpersonen. * Lehrpersonen haben mehr Rechte als Lernende. * Lernende haben oft nur Leserechte oder dürfen nur eigene Daten bearbeiten. * Lohn- und Personaldaten sind besonders geschützt und nur für sehr wenige Rollen sichtbar.

In früheren Lerneinheiten (LU08, LU09) haben Sie Daten in Beziehung gesetzt und abgefragt. Jetzt erweitern Sie diese Sicht um die Frage:

* Wer darf was mit diesen Daten tun?

Das ist der Kern von Handlungsziel 6:

*Rollen und Berechtigungen vergeben, um Datensicherheit und Datenschutz zu gewährleisten.*