Il miglior design per una tabella di database di changelog / auditing?

Ho bisogno di creare una tabella di database per memorizzare diversi log / auditing (quando qualcosa è stato aggiunto, cancellato, modificato, ecc.). Non ho bisogno di memorizzare informazioni particolarmente dettagliate, quindi stavo pensando qualcosa sulla falsariga di:

  • id (per evento)
  • utente che l’ha triggersto
  • nome dell’evento
  • descrizione dell’evento
  • timestamp dell’evento

Mi sto perdendo qualcosa qui? Ovviamente posso continuare a migliorare il design, anche se non ho intenzione di renderlo complicato (creare altre tabelle per tipi di eventi o cose del genere è fuori discussione dal momento che è una complicazione per il mio bisogno).

Nel progetto a cui sto lavorando, anche il log di controllo è partito dal design molto minimalista, come quello che hai descritto:

event ID event date/time event type user ID description 

L’idea era la stessa: mantenere le cose semplici.

Tuttavia, divenne subito ovvio che questo design minimalista non era sufficiente. L’audit tipico si stava riducendo a domande come questa:

 Who the heck created/updated/deleted a record with ID=X in the table Foo and when? 

Quindi, per essere in grado di rispondere rapidamente a tali domande (usando SQL), abbiamo finito con l’avere due colonne aggiuntive nella tabella di controllo

 object type (or table name) object ID 

Questo è quando la progettazione del nostro registro di controllo è davvero stabilizzata (da alcuni anni a questa parte).

Ovviamente, l’ultimo “miglioramento” funzionerebbe solo per le tabelle che avevano chiavi sostitutive. Ma indovina un po? Tutte le nostre tabelle che valgono la pena di auditing hanno una tale chiave!

Ci sono molte altre cose che potresti voler controllare come nomi di tabelle / colonne, computer / applicazione da cui è stato effettuato l’aggiornamento e altro.

Ora, questo dipende da quanto auditing dettagliato hai davvero bisogno ea che livello.

Abbiamo iniziato a creare la nostra soluzione di auditing basata su trigger e volevamo controllare tutto e anche avere un’opzione di recupero a portata di mano. Ciò si è rivelato troppo complesso e abbiamo finito con ApexSQL Audit, uno strumento di terze parti basato su trigger reverse engineering, per creare la nostra soluzione personalizzata.

Suggerimenti:

-Include valori prima / dopo

-Include 3,4 colonne per la memorizzazione della chiave primaria (nel caso si tratti di una chiave composita)

-Store dati al di fuori del database principale come già suggerito da Robert

-Spegnisci una quantità decente di tempo nella preparazione dei rapporti, in particolare quelli che potrebbero essere necessari per il recupero

-Piano per la memorizzazione di nome host / applicazione – questo potrebbe essere molto utile per tracciare attività sospette

Registriamo anche i vecchi e nuovi valori e la colonna da cui provengono, nonché la chiave primaria della tabella sottoposta a controllo in una tabella dei dettagli di controllo. Pensa a cosa ti serve la tabella di controllo? Non solo vuoi sapere chi ha apportato una modifica e quando, ma quando accade un brutto cambiamento, vuoi un modo veloce per ripristinare i dati.

Mentre stai progettando, dovresti scrivere il codice per recuperare i dati. Quando hai bisogno di recuperare, di solito è di fretta, meglio essere già preparati.

Ci sono molte risposte interessanti qui e in domande simili. L’unica cosa che posso aggiungere dall’esperienza personale è …

  1. Metti la tua tabella di controllo in un altro database. Idealmente vuoi la separazione dai dati originali. Se è necessario ripristinare il database, non si desidera realmente ripristinare la traccia di controllo.

  2. Denormalizzare il più ragionevolmente ansible. Si desidera che la tabella abbia il minor numero ansible di dipendenze dai dati originali. La tabella di controllo dovrebbe essere semplice e veloce per recuperare i dati da. Nessuna unione o ricerca di fantasia su altre tabelle per ottenere i dati.

Ci sono molti modi per farlo. Il mio modo preferito è:

0 – Aggiungi un campo mod_user alla tua tabella di origine (quella che vuoi registrare)

1 – Creare una tabella di registro che contenga i campi che si desidera registrare, oltre a un campo log_datetime e seq_num. seq_num è la chiave primaria.

2 – Costruire un trigger sulla tabella di origine che controlla la modifica di qualsiasi campo monitorato e inserisce il record corrente nella tabella dei log in qualsiasi modifica

Ora hai un record di ogni cambiamento e chi l’ha fatto.

Cosa abbiamo nella nostra tabella: –

 Primary Key Event type (eg "UPDATED", "APPROVED") Description ("Frisbar was added to blong") User Id User Id of second authoriser Amount Date/time Generic Id Table Name 

L’ID generico punta su una riga nella tabella che è stata aggiornata e il nome della tabella è il nome di quella tabella come una stringa. Non un buon progetto di DB, ma molto utilizzabile. Tutte le nostre tabelle hanno una singola colonna chiave surrogata, quindi funziona bene.

In generale, l’audit personalizzato (creazione di varie tabelle) è una ctriggers opzione. I trigger di database / tabella possono essere disabilitati per saltare alcune attività di registro. Le tabelle di controllo personalizzate possono essere manomesse. Possono verificarsi eccezioni che faranno cadere l’applicazione. Non menzionare difficoltà nella progettazione di una soluzione robusta. Finora vedo casi molto semplici in questa discussione. È necessaria una separazione completa dal database corrente e da qualsiasi utente privilegiato (DBA, sviluppatori). Ogni RDBMS mainstream fornisce servizi di controllo che persino DBA non è in grado di disabilitare, manomettere la segretezza. Pertanto, la capacità di verifica fornita dal fornitore RDBMS deve essere la prima opzione. Un’altra opzione potrebbe essere il lettore di registri delle transazioni di terze parti o un lettore di registri personalizzato che trasferisce le informazioni scomposte in un sistema di messaggistica che finisce in alcune forms di Audit Data Warehouse o gestore di eventi in tempo reale. In sintesi: Solution Architect / “Hands on Data Architect” deve coinvolgere nel destinare un sistema basato sui requisiti. Di solito è roba troppo seria solo per passare a una soluzione per sviluppatori.

Secondo il principio di separazione: –

1) Le tabelle dei dati di controllo devono essere separate dal database principale. Poiché la base di dati di revisione può avere molti dati storici.

quindi ha senso anche dall’utilizzo della memoria per tenerlo separato.

2) Non utilizzare i trigger per controllare il database dei fori. Perché finirai con un pasticcio di supporto per database diversi. Devi scrivere anche uno per DB2, SQLServer e Mysql ecc.

In ritardo per la festa, ma consiglio vivamente il progetto AutoAudit .
È al 100% gratuito e open source. È stato creato da MVP di SQL Paul Nielsen e John Sigouin. È molto stabile ed è attualmente in versione 3.30.

Semplice da installare. Basta eseguire l’SP che forniscono. Creerà uno schema di audit, alcuni SP di manutenzione e i trigger necessari per l’auditing. Da lì, scegli solo quali tabelle desideri controllare e con quali dettagli.