LU12c: MySQL – Rollen & Berechtigungen im Reiseblog

Sie können …

• den Unterschied zwischen Datenbankbenutzer und Anwendungsbenutzer erklären.
• mit CREATE USER, GRANT, REVOKE, CREATE ROLE in MySQL arbeiten.
• für das Beispiel travel_blog sinnvolle Rollen (Admin, Editor, Kommentarmoderation, App-User) definieren.

Im Beispielprojekt travel_blog gibt es:

Tabelle users (z. B. Blog-Autor:innen): → wandle das in eine Dokuwik-Tabelle um

1, caro, caro@wetraveltheworld.de, Caro Steig, …

2, martin, …

3, shaolin, …

Diese Benutzer gehören zur Fachanwendung (Reiseblog).

MySQL-Benutzer werden im System mysql.user gespeichert:

z. B. 'caro'@'%', 'martin'@'%', 'app_read'@'localhost'.

Die Einträge in travel_blog.users und in mysql.user sind völlig unterschiedliche Ebenen, auch wenn sie zufällig gleich heissen.

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

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

Besser:

• spezifische Admin-Benutzer (z. B. caro_admin, martin_admin) mit db_admin-Rolle erstellen.
• eigene technische Benutzer für Anwendungen (z. B. app_read, app_readwrite).

Annahme: Die DB travel_blog mit den Tabellen posts, users, categories, post_category existiert bereits. Wir ergänzen eine Tabelle comments:

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
);

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

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

• SELECT/INSERT/UPDATE/DELETE auf posts, categories

• SELECT/INSERT/UPDATE/DELETE auf comments

* SELECT auf allen benötigten Tabellen (für Reporting/Read-Only-App)

* SELECT/INSERT/UPDATE/DELETE auf Anwendungs-Tabellen (für Backend in Node.js/Express)

<WRAP center box 80% round>

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

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

Optional zusätzlich (Server-Ebene), wenn diese Rolle auch Benutzer verwalten soll:

 GRANT CREATE USER, DROP USER, GRANT OPTION ON *.* TO db_admin; 

 GRANT SELECT, INSERT, UPDATE, DELETE ON travel_blog.posts TO post_editor;
 
GRANT SELECT, INSERT, UPDATE, DELETE
ON travel_blog.categories
TO post_editor;

Hinweis: post_category wird über Foreign Keys und CASCADE indirekt mitgepflegt.

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

Willst du, dass normale Kommentierende nur eigene Kommentare einfügen können (z. B. über die Web-App), gibst du der App weniger Rechte (nur INSERT/SELECT) und regelst den Rest in der Business-Logik.

 -- nur lesen GRANT SELECT ON travel_blog.* TO app_read;
 
-- lesen & schreiben (aber kein DDL)
GRANT SELECT, INSERT, UPDATE, DELETE
ON travel_blog.*
TO app_readwrite;

Merksatz: Rechte fast immer an Rollen, nicht direkt an Benutzer vergeben.

Wir legen nun die MySQL-Benutzer an, die unseren Personen und Apps entsprechen.

 -- Admin-User CREATE USER 'caro_admin'@'%' IDENTIFIED BY 'CaroS!2025';
 
CREATE USER 'martin_admin'@'%'
IDENTIFIED BY 'MartinM!2025';
 
-- Editor
CREATE USER 'shaolin_editor'@'%'
IDENTIFIED BY 'Shaolin!2025';
 
-- Kommentar-Moderator
CREATE USER 'joachim_cm'@'%'
IDENTIFIED BY 'Joachim!2025';
 
-- App-User (Backend)
CREATE USER 'app_read'@'localhost'
IDENTIFIED BY 'AppRead!2025';
 
CREATE USER 'app_readwrite'@'localhost'
IDENTIFIED BY 'AppRw!2025';

(Passwörter im Unterricht bitte nicht so simpel wählen – hier nur Demo.)

 -- Admins GRANT db_admin TO 'caro_admin'@'%'; GRANT db_admin TO 'martin_admin'@'%';
 
-- Post-Editor (z. B. Shaolin)
GRANT post_editor TO 'shaolin_editor'@'%';
 
-- Kommentar-Moderator (z. B. Joachim Steig)
GRANT comment_moderator TO 'joachim_cm'@'%';
 
-- App-Rollen
GRANT app_read TO 'app_read'@'localhost';
GRANT app_readwrite TO 'app_readwrite'@'localhost';

Optional: Standardrollen aktivieren (MySQL 8):

 SET DEFAULT ROLE db_admin FOR 'caro_admin'@'%'; SET DEFAULT ROLE db_admin FOR 'martin_admin'@'%'; 

Um zu prüfen, ob alles korrekt gesetzt ist:

 SHOW GRANTS FOR 'caro_admin'@'%'; SHOW GRANTS FOR 'shaolin_editor'@'%'; SHOW GRANTS FOR 'joachim_cm'@'%'; 

Du kannst auch Rollen prüfen:

 SHOW GRANTS FOR db_admin; SHOW GRANTS FOR post_editor; 

Wenn sich Aufgaben ändern (z. B. jemand verlässt das Projekt):

 -- einzelne Rechte von einer Rolle entfernen REVOKE INSERT, DELETE ON travel_blog.comments FROM comment_moderator;
 
-- Rolle von einem Benutzer entziehen
REVOKE post_editor
FROM 'shaolin_editor'@'%';
 
-- alle Rechte + GRANT OPTION von Benutzer entfernen
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM 'caro_admin'@'%';

Rolle komplett löschen (inkl. Entzug bei allen Benutzern):

 DROP ROLE comment_moderator; 

DSG/DSGVO verlangen:

Schutz vor unbefugtem Zugriff,

Vertraulichkeit, Integrität, Verfügbarkeit,

„Need-to-know“-Prinzip.

Unsere Rollen in MySQL setzen genau das um:

Admins (Caro, Martin) → db_admin.

Editor (Shaolin) → post_editor (kein Zugriff auf Löhne, Userverwaltung etc.).

Kommentar-Moderator (Joachim) → comment_moderator.

Apps → app_read / app_readwrite statt root.

Brücke zur restlichen Modulreihe:

In früheren Lerneinheiten (LU08, LU09) habt ihr Daten in Beziehung gesetzt und abgefragt.

Jetzt ergänzt ihr: 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.