LU12d - Rollen: Rechte einfach verwalten

1. DB & Tabelle

CREATE DATABASE IF NOT EXISTS crm;
USE crm;
CREATE TABLE IF NOT EXISTS customers(
  id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(255) NOT NULL,
  last_name  VARCHAR(255) NOT NULL,
  phone VARCHAR(32) NOT NULL,
  email VARCHAR(255)
);

2. Rollen anlegen

CREATE ROLE IF NOT EXISTS crm_dev, crm_read, crm_write;

3. Rechte an Rollen vergeben

GRANT ALL            ON crm.* TO crm_dev;       -- Entwickler: alles
GRANT SELECT         ON crm.* TO crm_read;      -- nur lesen
GRANT INSERT, UPDATE, DELETE ON crm.* TO crm_write; -- lesen+schreiben

4. Benutzer anlegen & Rollen zuweisen

CREATE USER IF NOT EXISTS 'crm_dev1'@'localhost'   IDENTIFIED BY 'Secure$1782';
CREATE USER IF NOT EXISTS 'crm_read1'@'localhost'  IDENTIFIED BY 'Secure$5432';
CREATE USER IF NOT EXISTS 'crm_write1'@'localhost' IDENTIFIED BY 'Secure$9075';
CREATE USER IF NOT EXISTS 'crm_write2'@'localhost' IDENTIFIED BY 'Secure$3452';
 
GRANT crm_dev   TO 'crm_dev1'@'localhost';
GRANT crm_read  TO 'crm_read1'@'localhost';
GRANT crm_read, crm_write TO 'crm_write1'@'localhost', 'crm_write2'@'localhost';

5. Standardrollen setzen (auto-aktiv bei Login)

SET DEFAULT ROLE ALL TO 'crm_read1'@'localhost', 'crm_write1'@'localhost', 'crm_write2'@'localhost', 'crm_dev1'@'localhost';

Aktive Rollen sehen

SELECT CURRENT_ROLE();

Rollen umschalten (Session)

SET ROLE NONE;     -- keine Rolle aktiv
SET ROLE ALL;      -- alle zugewiesenen aktiv
SET ROLE DEFAULT;  -- Standardrollen aktiv
-- oder gezielt:
SET ROLE crm_read;