MySQL audit trigger

Gli accessi ad alcuni particolari oggetti della base dati sono a volte soggetti a stringenti regole che prevedono la registrazione di tutte attivita' eseguite (AUDIT) per ottemperare alle leggi ed ai regolamenti sulla gestione delle basi dati.

Con MySQL e' semplice utilizzare un trigger per effettuare il controllo di tutte le modifiche effettuate su una tabella.

Esempio

La cosa piu' facile per comprendere la tecnica utilizzata e' partire con un semplice esempio. Vogliamo controllare la tabella degli impiegati (EMP) ed in particolare le colonne dello stipendio e dei premi (SAL, COMM):

DELIMITER @@
-- emp after INSERT trigger
CREATE TRIGGER adt_emp_after_insert AFTER INSERT ON emp
 FOR EACH ROW BEGIN
  insert into emp_audit(empno, ename, sal, comm, audit_event, audit_timestamp)
   values(NEW.empno, NEW.ename, NEW.sal, NEW.comm, 'insert', CURRENT_TIMESTAMP);
 END;

-- emp after UPDATE trigger    
CREATE TRIGGER adt_emp_after_update AFTER UPDATE ON emp
 FOR EACH ROW BEGIN
  insert into emp_audit(empno, ename, sal, comm, audit_event, audit_timestamp)
   values(NEW.empno, NEW.ename, NEW.sal, NEW.comm, 'update', CURRENT_TIMESTAMP);
 END;

-- emp after DELETE trigger
CREATE TRIGGER adt_emp_after_delete AFTER DELETE ON emp
 FOR EACH ROW BEGIN
  insert into emp_audit(empno, ename, sal, comm, audit_event, audit_timestamp)
   values(OLD.empno, OLD.ename, OLD.sal, OLD.comm, 'delete', CURRENT_TIMESTAMP);
 END;
@@
DELIMITER ;

Sulla tabella EMP_AUDIT vengono registrate tutte le modifiche effettuate sulla tabella EMP. In particolare nella tabella di audit vengono registrati i valori delle colonne piu' importanti.

I valori delle colonne possono essere analizzati prima dell'esecuzione dello statement SQL (OLD) o dopo (NEW). I valori OLD sono in sola lettura mentre il trigger puo' modificare i valori NEW (eg. per inserire il risultato di calcoli).

Nei trigger MySQL e' possibile utilizzare statement complessi (basta racchiuderli con BEGIN ... END), richiamare stored routine ed impostare variabili (eg. @sum).

Varie ed eventuali

Dalla versione 5.7 e' possibile avere piu' trigger sulla stessa tabella e sullo stesso evento. In questo modo e' possibile sviluppare e gestire in modo indipendente trigger di business o di sicurezza.
Per default l'ordine di esecuzione dei trigger corrisponde a quello di creazione ma e' possibile specificarlo con le clausole FOLLOWS e PRECEDES.

I trigger possono essere definiti solo sugli statement di DML (INSERT, UPDATE, DELETE). Questa tecnica quindi non puo' essere utilizzata per effettuare l'AUDIT degli accessi con SELECT... pero' qualcosa si puo' comunque fare!

La documentazione ufficiale sui trigger si trova sul sito ufficiale MySQL.

Meno selettiva della tecnica dei trigger e' l'auditing. MySQL fornisce le API ed i plugin per la funzionalita' di auditing del database; su questo argomento specifico si puo' consultare questo documento.

Titolo: MySQL audit trigger
Livello: Esperto (4/5)
Data: 14 Febbraio 2017
Versione: 1.0.1 - 21 Giugno 2017
Autore: mail [AT] meo.bogliolo.name