Traccia di controllo nel DBMS

introduzione

Un audit trail, noto anche come registro delle transazioni, è una registrazione di tutte le modifiche apportate a un database in un DBMS (sistema di gestione del database). Viene utilizzato per tracciare e monitorare l'attività del database, identificare e risolvere i problemi e garantire l'integrità e la sicurezza dei dati. In questo articolo esploreremo lo scopo e i vantaggi degli audit trail nei DBMS, come funzionano e forniremo esempi di vita reale e di codice SQL della loro implementazione e utilizzo.

Che cos'è una traccia di controllo nel DBMS?

Un audit trail è una registrazione cronologica di tutte le transazioni del database, inclusi inserimenti, aggiornamenti ed eliminazioni. Cattura sia i valori vecchi che quelli nuovi dei dati modificati, nonché metadati come l'utente o l'applicazione responsabile della modifica, la data e l'ora della modifica e il tipo di modifica (ad esempio, inserimento, aggiornamento, eliminazione).

È possibile utilizzare un audit trail per tenere traccia e monitorare l'attività del database, identificare e risolvere i problemi e garantire l'integrità e la sicurezza dei dati. Ad esempio, se un utente elimina accidentalmente dati importanti dal database, l'audit trail può essere utilizzato per identificare l'utente responsabile e ripristinare i dati eliminati. Allo stesso modo, se i dati vengono danneggiati o modificati in modo non autorizzato, l'audit trail può aiutare a identificare la causa e intraprendere azioni correttive.

Tipi di audit trail

In un sistema di gestione di database (DBMS), un audit trail è una registrazione delle modifiche apportate al database. Esistono diversi tipi di audit trail che possono essere utilizzati per tenere traccia delle modifiche in un DBMS. I tre tipi principali di audit trail sono audit trail interni, esterni e IRS (Internal Revenue Service).

Percorsi di controllo interni: questi percorsi di controllo vengono utilizzati dalle organizzazioni per tenere traccia delle modifiche apportate ai propri database. Vengono generalmente utilizzati per garantire l'integrità dei dati, rilevare e correggere errori e soddisfare i requisiti normativi.

Esempio - Una società potrebbe utilizzare una traccia di controllo interna per tenere traccia delle modifiche apportate ai propri registri finanziari o al database dei clienti.

Percorsi di controllo esterni - Questi percorsi di controllo vengono utilizzati da organizzazioni o revisori esterni per rivedere i dati in un database. Vengono spesso utilizzati per verificare l'accuratezza e l'affidabilità dei dati per scopi normativi o di conformità.

Esempio - Un revisore esterno potrebbe utilizzare una pista di controllo esterna per esaminare i registri finanziari di una società per verificarne la conformità agli standard contabili.

Percorsi di controllo IRS - Questi percorsi di controllo vengono utilizzati dall'Internal Revenue Service (IRS) per tenere traccia delle modifiche apportate ai documenti fiscali. Vengono utilizzati per garantire l'accuratezza e l'integrità delle informazioni fiscali e per individuare e prevenire le frodi fiscali.

Esempio - L'IRS potrebbe utilizzare una traccia di controllo IRS per tenere traccia delle modifiche apportate ai documenti fiscali di un individuo, come modifiche al reddito o alle detrazioni.

Di seguito sono menzionati alcuni altri tipi importanti di audit trail e i relativi esempi.

Tracce di controllo basate su registro: queste tracce di controllo utilizzano un file di registro per registrare le modifiche apportate al database. Il file di registro contiene informazioni su ciascuna modifica, come l'ora in cui è stata apportata la modifica, l'utente che ha apportato la modifica e il tipo di modifica (ad esempio, inserimento, aggiornamento, eliminazione).

Esempio - In un database finanziario, una traccia di controllo basata su log potrebbe essere utilizzata per tenere traccia delle modifiche ai saldi o alle transazioni dei conti.

Audit trail basati su trigger - Questi audit trail utilizzano trigger, che sono tipi speciali di oggetti di database che vengono attivati quando si verifica un evento specifico (ad esempio, una riga viene inserita o aggiornata). I trigger possono essere utilizzati per registrare le modifiche apportate al database in una tabella di controllo.

Esempio - In un database sanitario, un audit trail basato su trigger potrebbe essere utilizzato per tenere traccia delle modifiche alle cartelle cliniche dei pazienti, come le modifiche agli elenchi dei farmaci o ai segni vitali.

Tracce di controllo basate sulla versione: queste tracce di controllo utilizzano il controllo delle versioni per tenere traccia delle modifiche apportate al database. Ogni volta che viene apportata una modifica a una riga nel database, viene creata una nuova versione della riga con i dati aggiornati. Viene mantenuta la vecchia versione della riga, consentendoti di visualizzare la cronologia delle modifiche apportate alla riga.

Esempio - In un database di gestione del progetto, un audit trail basato sulla versione potrebbe essere utilizzato per tenere traccia delle modifiche alle attività del progetto, come le modifiche alle date di scadenza o allo stato di completamento.

Tabelle shadow - Queste sono tabelle utilizzate per archiviare copie di righe man mano che vengono aggiornate nella tabella principale. La tabella shadow contiene sia la vecchia che la nuova versione della riga, consentendoti di vedere la cronologia delle modifiche apportate alla riga.

Esempio - In un database CRM (Customer Relationship Management), una tabella ombra potrebbe essere utilizzata per tenere traccia delle modifiche ai profili dei clienti, come le modifiche alle informazioni di contatto o alla cronologia degli acquisti.

Esempio SQL

Ecco un esempio di traccia di controllo che utilizza SQL che tiene traccia delle modifiche apportate a una tabella denominata "dipendenti" -

CREATE TABLE employees_audit (
   employee_id INTEGER,
   action VARCHAR(255),
   change_time TIMESTAMP,
   old_data JSON,
   new_data JSON
);

CREATE TRIGGER audit_employee_changes
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
   IF (TG_OP = 'DELETE') THEN
      INSERT INTO employees_audit (employee_id, action, change_time, old_data)
      VALUES (OLD.id, 'DELETE', NOW(), OLD.*);
   ELSEIF (TG_OP = 'UPDATE') THEN
      INSERT INTO employees_audit (employee_id, action, change_time, old_data, new_data)
      VALUES (OLD.id, 'UPDATE', NOW(), OLD.*, NEW.*);
   ELSE
      INSERT INTO employees_audit (employee_id, action, change_time, new_data)
      VALUES (NEW.id, 'INSERT', NOW(), NEW.*);
   END IF;
END;

Questo codice SQL crea una tabella di controllo chiamata "employees_audit" e un trigger chiamato "audit_employee_changes". Il trigger viene attivato ogni volta che una riga viene inserita, aggiornata o eliminata nella tabella "dipendenti".

Quando il trigger viene attivato, inserisce una nuova riga nella tabella "employees_audit" con le informazioni sulla modifica apportata. La colonna "action" specifica il tipo di modifica (INSERT, UPDATE o DELETE), la colonna "change_time" registra l'ora in cui è stata apportata la modifica e le colonne "old_data" e "new_data" contengono i dati prima e dopo la modifica , rispettivamente.

Ad esempio, se viene aggiornata una riga nella tabella "employees", il trigger inserirà una nuova riga nella tabella "employees_audit" con l'azione "UPDATE", l'ora corrente, i vecchi dati della riga prima dell'aggiornamento e i nuovi dati dalla riga dopo l'aggiornamento. Ciò consente di tenere traccia delle modifiche apportate alla tabella "dipendenti" nel tempo.

Vantaggi degli audit trail nei DBMS

Ci sono diversi vantaggi nell'implementare una traccia di controllo in un DBMS −

Integrità e sicurezza dei dati - Gli audit trail aiutano a garantire l'integrità e la sicurezza dei dati monitorando e monitorando tutta l'attività del database. Ciò può aiutare a prevenire l'accesso non autorizzato, la modifica o la cancellazione dei dati, nonché a rilevare e correggere errori o danneggiamenti.
Conformità: molti settori e organizzazioni prevedono normative e requisiti di conformità rigorosi per la gestione dei dati, come il GDPR per i dati personali nell'UE o l'HIPAA per i dati sanitari negli Stati Uniti. Gli audit trail possono aiutare le organizzazioni a soddisfare questi requisiti fornendo una registrazione di tutte le attività del database e garantendo che i dati vengano gestiti in modo sicuro e conforme.
Risoluzione dei problemi e risoluzione dei problemi: è possibile utilizzare gli audit trail per identificare e risolvere i problemi relativi al database, come errori, corruzione o accesso non autorizzato. Possono anche aiutare a risolvere i problemi fornendo una registrazione delle modifiche che hanno portato al problema, consentendo l'adozione di azioni correttive.
Analisi di controllo e forense: gli audit trail possono essere utilizzati per analisi di controllo e forensi per indagare su potenziali violazioni della sicurezza o attività fraudolente. Forniscono una registrazione dettagliata di tutta l'attività del database che può essere utilizzata per identificare e tenere traccia di attività sospette.

Conclusione

Per concludere, un audit trail è una registrazione delle modifiche apportate a un database in un DBMS. Viene utilizzato per garantire l'integrità dei dati, rilevare e correggere gli errori e soddisfare i requisiti normativi. È possibile utilizzare diversi tipi di audit trail, tra cui tabelle basate su log, trigger, versioni e shadow.