SILICIO E NUVOLE: Corso di SQL: 8 – Gestione Utenti e Sicurezza

venerdì 14 novembre 2025

Corso di SQL: 8 – Gestione Utenti e Sicurezza

8 – Gestione Utenti e Sicurezza

Obiettivi

Imparare a creare utenti e ruoli sul database.
Assegnare/revocare permessi (GRANT / REVOKE).
Comprendere le transazioni (BEGIN / COMMIT / ROLLBACK) e le proprietà ACID.
Simulare un ambiente multiutente con permessi diversi e osservare il comportamento delle transazioni.

1) Creazione utenti e ruoli: concetti essenziali

Utente: account che si autentica al DBMS; ha credenziali e, opzionalmente, permessi.
Ruolo (role): gruppo logico di permessi che può essere assegnato a più utenti; facilita la gestione dei privilegi.
Principio del least privilege: assegnare soltanto i permessi strettamente necessari.

Differenze rapide tra MySQL e PostgreSQL

MySQL: CREATE USER 'alice'@'host' IDENTIFIED BY 'pwd';
PostgreSQL: CREATE USER alice WITH PASSWORD 'pwd'; (o CREATE ROLE alice LOGIN PASSWORD 'pwd';)
Postgres ha un modello di ruoli più flessibile (role = user o group a seconda dell’uso).

2) Comandi base: CREATE USER / CREATE ROLE

MySQL (esempi)

-- crea utente con password, limitando connessioni da localhost
CREATE USER 'analyst'@'localhost' IDENTIFIED BY 'S3cur3Pwd!';

-- crea un utente per lettura remota
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'AppPwd2025';

PostgreSQL (esempi)

-- crea utente (role con login)
CREATE ROLE analyst LOGIN PASSWORD 'S3cur3Pwd!';

-- crea ruolo di gruppo (senza login)
CREATE ROLE read_only;

-- assegnare permesso di login al ruolo (opzionale)
ALTER ROLE read_only WITH LOGIN;

3) Assegnazione permessi: GRANT

Permessi comuni: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, USAGE, EXECUTE, GRANT OPTION.

MySQL

-- dare SELECT su tutta la DB 'school' al read-only user
GRANT SELECT ON school.* TO 'analyst'@'localhost';

-- dare INSERT, UPDATE su una tabella specifica
GRANT INSERT, UPDATE ON school.students TO 'app_user'@'192.168.1.%';

-- dare tutti i permessi su un DB (attenzione: molto potente)
GRANT ALL PRIVILEGES ON shop.* TO 'admin'@'localhost';

-- applica le modifiche
FLUSH PRIVILEGES;

PostgreSQL

-- concedere SELECT su tutta la schema public a un ruolo
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

-- concedere privilegi specifici su una tabella
GRANT INSERT, UPDATE ON students TO app_user;

-- creare ruolo e assegnare permessi
GRANT read_only TO alice; -- alice eredita i permessi del ruolo read_only

Nota: in PostgreSQL è comune gestire permessi tramite ruoli di gruppo (CREATE ROLE rolename; GRANT rolename TO user;) così si possono cambiare permessi centralmente.

4) Revoca permessi: REVOKE

-- MySQL
REVOKE UPDATE ON school.students FROM 'app_user'@'192.168.1.%';

-- PostgreSQL
REVOKE INSERT, UPDATE ON students FROM app_user;

Dopo REVOKE verificare con query su catalogo (information_schema o pg_roles/pg_auth_members, mysql.user, mysql.db).

5) Transazioni: concetti e comandi base

Proprietà ACID

Atomicity: tutte le operazioni di una transazione sono eseguite o nessuna.
Consistency: stato valido → stato valido.
Isolation: transazioni concorrenti non interferiscono (isolation levels).
Durability: commit è permanente.

Comandi SQL (generico)

BEGIN;          -- inizio transazione
-- ... eseguire più query (SELECT/UPDATE/INSERT/DELETE)
COMMIT;         -- rendere permanenti le modifiche

-- oppure annullare tutto
ROLLBACK;       -- annulla le modifiche nella transazione

In MySQL: START TRANSACTION; / COMMIT; / ROLLBACK;
In PostgreSQL: BEGIN; / COMMIT; / ROLLBACK;

6) Isolation levels e fenomeni di concorrenza

Isolation levels standard SQL:

READ UNCOMMITTED — possibile dirty reads.
READ COMMITTED — evita dirty reads, ma non non-repeatable reads / phantom reads. (Default in PostgreSQL)
REPEATABLE READ — evita non-repeatable reads (MySQL/InnoDB default used to be REPEATABLE READ).
SERIALIZABLE — massimo isolamento, evita phantom reads (come se le transazioni fossero seriali).

Fenomeni da conoscere:

Dirty read: leggere dati non ancora committed da un’altra transazione.
Non-repeatable read: leggere stessa riga due volte e trovare valori diversi (altra transazione ha fatto commit).
Phantom read: seconda lettura trova righe aggiuntive (insert) o rimosse.

Impostare livello (MySQL esempio)

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
-- ...
COMMIT;

PostgreSQL (sessione)

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ...
COMMIT;

7) Esempi pratici di transazione (caso banco: trasferimento fondi)

Schema semplificato

CREATE TABLE accounts (
  account_id INT PRIMARY KEY,
  owner VARCHAR(50),
  balance DECIMAL(12,2)
);

Trasferimento atomico:

-- pseudocodice SQL (MySQL/Postgres identico concettualmente)
START TRANSACTION;

-- sottrarre dall'account A
UPDATE accounts SET balance = balance - 100.00 WHERE account_id = 1;

-- controllare che non sia negativo
SELECT balance FROM accounts WHERE account_id = 1;
-- se balance < 0 ROLLBACK

-- aggiungere a account B
UPDATE accounts SET balance = balance + 100.00 WHERE account_id = 2;

COMMIT;

Esempio concreto con check e rollback (Postgres style):

BEGIN;
UPDATE accounts SET balance = balance - 100.00 WHERE account_id = 1;
-- verifica
SELECT balance FROM accounts WHERE account_id = 1; -- supponiamo risultato -20
-- se saldo negativo
ROLLBACK; -- annulla tutto e segnala errore

Questo evita che il denaro “sparisca” in caso di errori a metà operazione.

8) Locking e concorrenza

SELECT ... FOR UPDATE (Postgres/MySQL InnoDB) prende lock sulle righe selezionate per evitare scritture concorrenti. Utile in transfer pattern:

START TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;
-- ora nessun altro può modificare questa riga fino a COMMIT/ROLLBACK
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

Attenzione ai possibili deadlock: due transazioni bloccano risorse in ordine opposto. Strategie di prevenzione:
- Accedere alle risorse in ordine consistente.
- Tenere transazioni brevi.
- Gestire deadlock rilevando l’errore e ritentando la transazione.

9) Audit, logging e sicurezza operativa

Abilitare audit logs per tracciare connessioni, query critiche, modifiche ai permessi.
Limitare dalla rete chi può connettersi al DB (firewall, bind-address).
Forzare connessioni TLS/SSL al DB e usare certificati.
Rotazione e protezione delle credenziali, uso di vault (HashiCorp Vault, AWS Secrets Manager).
Monitoraggio e alert su eventi anomali (troppe connessioni, query lente, tentativi falliti di login).

10) Esercizio svolto: simulazione multiutente con permessi diversi

Scenario

Hai un DB school. Vuoi creare:

admin_user — pieno controllo sul DB (crea, modifica, drop).
teacher_user — può leggere e aggiornare tabelle degli studenti (SELECT, UPDATE) ma non eliminare tabelle o cambiare permessi.
report_user — può solo leggere (SELECT) per creare report.

Diamo anche un esempio di transazione (registro voti) per teacher_user.

Passo-passo (MySQL style, sostituire sintassi per PostgreSQL dove necessario)

1) creare utenti

CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'Adm1nPwd!';
CREATE USER 'teacher_user'@'localhost' IDENTIFIED BY 'TeachPwd!';
CREATE USER 'report_user'@'localhost' IDENTIFIED BY 'RepPwd!';

2) creare DB e tabelle (effettuato dall'amministratore)

CREATE DATABASE school;
USE school;

CREATE TABLE students (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  grade VARCHAR(10)
);

CREATE TABLE grades (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT,
  subject VARCHAR(50),
  vote INT,
  recorded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (student_id) REFERENCES students(student_id)
);

3) popolare dati di esempio

INSERT INTO students (name, grade) VALUES ('Anna Rossi','A'), ('Marco Bianchi','B');
INSERT INTO grades (student_id, subject, vote) VALUES (1,'Math',8), (2,'Math',7);

4) concedere permessi (principio least privilege)

-- admin: tutti i permessi sul DB
GRANT ALL PRIVILEGES ON school.* TO 'admin_user'@'localhost';

-- teacher: SELECT e UPDATE su students e INSERT su grades
GRANT SELECT, UPDATE ON school.students TO 'teacher_user'@'localhost';
GRANT INSERT, SELECT ON school.grades TO 'teacher_user'@'localhost';

-- report: solo SELECT su tutte le tabelle
GRANT SELECT ON school.* TO 'report_user'@'localhost';

FLUSH PRIVILEGES;

5) verifiche (cosa ogni utente può fare)

report_user dovrà poter eseguire:

-- come report_user
SELECT s.name, g.subject, g.vote
FROM students s JOIN grades g ON s.student_id = g.student_id;

teacher_user può registrare una nuova votazione (transazione con locking se necessario):

-- come teacher_user (esempio di transazione)
START TRANSACTION;

-- opzionale: lock riga studente per update atomic
SELECT * FROM students WHERE student_id = 1 FOR UPDATE;

INSERT INTO grades(student_id, subject, vote) VALUES (1, 'Physics', 9);

COMMIT;

teacher_user NON può fare DROP TABLE né GRANT permessi (testare che fallisca).

6) Simulazione problema / rollback

Supponiamo che teacher_user inserisca un voto ma fallisca il controllo (es. voto non valido). Usare ROLLBACK:

START TRANSACTION;
INSERT INTO grades(student_id, subject, vote) VALUES (1, 'Chemistry', 12); -- voto invalido
-- validazione applicativa rileva che 12 > max 10
ROLLBACK; -- la riga inserita viene annullata

11) Esercizi proposti (da svolgere)

Creare ruoli: in PostgreSQL creare ruoli readers, writers, assegnare a due utenti e verificare ereditarietà delle autorizzazioni.
Simulare deadlock: due transazioni A e B provano ad aggiornare righe in ordine opposto; osservare l’errore di deadlock e riprovare.
Policy password: definire e applicare regole di complessità password per nuovi utenti (server side o tramite PAM).
Rollback condizionale: implementare in stored procedure il trasferimento fondi con controllo e rollback automatico in caso di fallimento.
Audit: abilitare log delle query lente e degli accessi falliti; eseguire analisi su tentativi di login.

12) Checklist rapida (Best practices operative)

Usa ruoli/gruppi per gestire permessi (non assegnare permessi per utente uno-a-uno quando possibile).
Applica principio del least privilege.
Forza connessioni TLS/SSL al DB.
Non mantenere account root o admin con password deboli o connessi da IP pubblici.
Mantieni transazioni brevi e usa lock espliciti solo quando necessario.
Gestisci deadlock con ritentativi e logging.
Audit e monitoraggio attivi per attività sospette.
Non memorizzare password in chiaro (per utenti applicativi usare vault).
Separare account applicazione (db user) da account amministrativi.

SILICIO E NUVOLE