LU12A: Data Security & User Management (DCL) – Rollen & Berechtigungen

Ebenen: Global → Schema → Tabelle → Spalte → Routine (PROCEDURE/FUNCTION). Rollen (MySQL 8): Mehrere Privilegien bündeln, einmal verwalten, mehrfach zuweisen. Benutzer: user'@'host (Host einschränken: localhost vs. % für extern).

2.1 Demo-Schema & Tabelle

CREATE DATABASE IF NOT EXISTS app_demo;
USE app_demo;
 
CREATE TABLE IF NOT EXISTS notes(
  id INT PRIMARY KEY AUTO_INCREMENT,
  owner VARCHAR(64) NOT NULL,
  body  TEXT
);

2.2 Rollen definieren

CREATE ROLE IF NOT EXISTS 'app_read', 'app_write', 'app_admin';
 
GRANT SELECT                                ON app_demo.* TO 'app_read';
GRANT SELECT, INSERT, UPDATE, DELETE        ON app_demo.* TO 'app_write';
GRANT ALL PRIVILEGES                        ON app_demo.* TO 'app_admin';

2.3 Benutzer anlegen (lokal)

CREATE USER IF NOT EXISTS 'app_ro'@'localhost'    IDENTIFIED BY 'Str0ng!Ro';
CREATE USER IF NOT EXISTS 'app_rw'@'localhost'    IDENTIFIED BY 'Str0ng!Rw';
CREATE USER IF NOT EXISTS 'app_admin'@'localhost' IDENTIFIED BY 'Str0ng!Admin';

2.4 Rollen zuweisen & Standardrolle setzen

GRANT 'app_read'  TO 'app_ro'@'localhost';
GRANT 'app_write' TO 'app_rw'@'localhost';
GRANT 'app_admin' TO 'app_admin'@'localhost';
 
SET DEFAULT ROLE 'app_read'  TO 'app_ro'@'localhost';
SET DEFAULT ROLE 'app_write' TO 'app_rw'@'localhost';
SET DEFAULT ROLE 'app_admin' TO 'app_admin'@'localhost';

2.5 Kontrolle

SHOW GRANTS FOR 'app_rw'@'localhost';

3.1 Read-Only testen

-- als app_ro
USE app_demo;
SELECT * FROM notes;         -- sollte funktionieren
INSERT INTO notes(owner, body) VALUES ('lisa','hello');  -- sollte fehlschlagen (permission)

3.2 Rechte ändern (root)

-- als root
REVOKE 'app_write' FROM 'app_rw'@'localhost';
GRANT  'app_read'  TO   'app_rw'@'localhost';
SHOW GRANTS FOR 'app_rw'@'localhost';

Spaltenrechte

GRANT SELECT(id, owner) ON app_demo.notes TO 'app_ro'@'localhost';

Passwort-Policy & Ablauf

ALTER USER 'app_rw'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'app_rw'@'localhost' ACCOUNT LOCK;   -- bei Bedarf
-- ALTER USER 'app_rw'@'localhost' ACCOUNT UNLOCK;

Benutzer & Hosts auflisten

SELECT USER, host FROM mysql.user ORDER BY USER, host;