In questo post vedremo come impostare auditing in Sql Server. Mettere sotto osservazione (auditing) un database SQL Server non serve soltanto per essere conformi ai requisiti previsti dalle regole di conformità GDPR.
È una pratica diventata necessaria per l’analisi di azioni sul database, risoluzione di problemi, indagine su attività sospette o fraudolente. Può anche essere utile per prevenire azioni inappropriate da parte degli utenti, come se ci fosse un sistema di TV a circuito chiuso sui propri database.
Sommario
Tipo di auditing per SQL Server
Ci sono differenti tecniche di auditing per SQL Server:
- Auditing manuale – può essere creato per rispondere a requisiti specifici, ma la realizzazione comporta un notevole dispendio di tempo e può portare facilmente ad errori
- Utilizzare gli Extended Events in SQL Server – facile da impostare, si può monitorare un ampio raggio di azioni, ma non fornisce informazioni né di cosa è stato cancellato/inserito né dei vecchi e nuovi valori per gli aggiornamenti; inoltre un auditing dettagliato può causare problemi di performance
- Usare i trigger di SQL Server – facili da impostare ma possono causare problemi di performance per database con un alto numero di transazioni
- Leggere il transaction log – non è richiesto di catturare ulteriori dati poiché SQL Server già traccia le modifiche. Da prevedere spazio di memoria aggiuntivo e tenere presente che alcune azioni (come EXECUTEs) non sono registrate
- Usare SQL Server Profiler e SQL Server traces – flessibile e complesso. Presenta qualche difficoltà nel leggere e filtrare i records
Identificare la soluzione più corretta dipende dal vostro ambiente, da quello che vi serve osservare, da dove pensate di registrare le azioni catturate e dal modo in cui intendete rappresentare i dati.
Auditing manuale
Un database di SQL Server può essere messo sotto osservazione utilizzando stored procedure ad hoc e funzioni per il tracciamento delle variazioni sui dati e sugli oggetti. Questo fornisce una soluzione flessibile che però comporta un grande sforzo di scrittura e di sviluppo codice, aumentando di conseguenza il costo e il periodo di implementazione.
Utilizzare SQL Server Extended Events
SQL Server Audit è una funzionalità, introdotta dalla versione 2008, che usa gli Extended Events per monitorare le azioni di SQL Server. Esso consente differenti azioni di verifica, fornendo molti dettagli durante il processo di setup e coprendo un ampio spettro di attività in SQL Server.
Per creare un nuovo oggetto di Audit in SQL Server:
- In SQL Server Management Studio, espandi Security e clicca con il destro su Audits
- Seleziona New Audit

- Specifica a nome per l’audit, scegli se salvare i dati nel application event log, security event log o in un file e poi imposta una destinazione per il file di audit.

- Clicca OK e il tuo audit apparirà nel nodo Audits dell’Object Explorer di Management Studio
- Di default l’audit è disabilitato e per questo viene mostrato con una freccia rossa. Per abilitarlo, click con il destro e seleziona Enable Audit.
- Scegli tra Server Audit Specification o Database Audit Specification a seconda se vuoi monitorare l’attività a livello di istanza di SQL Server o di database.
- Per creare un Database Audit Specification, espandi il nodo del database che vuoi monitorare vai su Security click con il destro su Database Audit Specification e scegli New Database Audit

Nella finestra di dialogo Create Database Audit Specification indica un nome, associa la specifica con l’oggetto di audit creato al punto #1, specifica le attività da verificare in Audit Action Type. Per un database auditing, indica il database, l’oggetto o schema come Object Class, il nome dell’oggetto di audit ed il login.

- Si possono vedere tutte le attività da monitorare con SQL Server Auditing nel menù a tendina per Audit Action Type
- I Principals da selezionare nella finestra di dialogo, sono in realtà le utenze che saranno oggetto di monitoraggio
- Come per gli audits, le Database Audit Specification sono disabilitate di default. Per abilitarle, seleziona questa opzione nel menu contestuale.

Adesso, tutte le operazioni di DELETE eseguite sulla tabella Person.BusinessEntityAddress saranno registrate ed inserite dentro files il cui nome inizia con Audit-, per esempio Audit-AW2012Test_9D93CA4A-8B90-40B8-8B0B-FCBDA77B431D_0_130161593310500000.sqlaudit che sono salvati su E:\
Siccome potrebbero esserci parecchie azioni da catturare in un database molto occupato, si raccomanda di salvare le informazioni di audit in un file. Il file sqlaudit non può essere aperto con un editor di testo o esadecimale. Usare Reporting Services o la funzione T-SQL fn_get_audit_file per analizzare i dati:
SELECT event_time,action_id,statement,database_name,server_principal_name
FROM fn_get_audit_file( 'E:\Test\Audit-*.sqlaudit' , DEFAULT , DEFAULT);
Da tenere in considerazione che, utilizzando T-SQL non si avrà l’evidenza di cosa è stato effettivamente cancellato, soltanto da chi e quando è stata fatta la cancellazione; questo è uno degli svantaggi di questo metodo.
Altri aspetti negativi sono:
- Siccome SQL Server Audit usa le risorse di SQL Server per l’auditing dettagliato, potrebbero esserci ripercussioni sulle performance totali di SQL Server
- La gestione di SQL Server Audit su istanze multiple di SQL Server non può essere centralizzata
- Analizzare ed archiviare i dati di audit (in un file o un log) implica attività manuali di importazione, reporting ed archiviazione
- È disponibile solo dalla versione 2008 di SQL Server e successive
- L’auditing a livello di database è disponibile soltanto nelle versioni Enterprise, Developer e Evaluations.
Usare SQL Server triggers
I triggers di SQL Server si attivano automaticamente quando si verifica un determinato evento. Di fatto i triggers sono stored procedures eseguite automaticamente quando una condizione è rispettata. I Data Manipulation Languages (DML) triggers possono così essere impiegati per tracciare le operazioni di INSERT, UPDATE e DELETE.
Si possono creare i trigger uno ad uno per ciascuna tabella o istruzione che si vuole monitorare. È anche necessario indicare dove le informazioni tracciate – per esempio una tabella SQL- , con timestamp della transazione, user name, tipo di transazione, ecc. saranno inserite.
Per il tracciamento degli UPDATES può essere utile archiviare sia i vecchi che i nuovi valori.
Per esempio, un trigger che si attiva dopo l’inserimento di un record nella tabella Person.Person inserisce il nome della tabella, data e ora dell’inserimento e lo username utilizzato. La tabella di archiviazione dovrebbe apparire come:
CREATE TRIGGER PersonPerson_I
ON Person.Person
AFTER INSERT
AS
INSERT INTO dbo.repository (
TABLE_NAME,
TABLE_SCHEMA,
AUDIT_ACTION_ID,
MODIFIED_BY,
MODIFIED_DATE,
[DATABASE]
)
values(
'Person',
'Person',
'Insert',
SUSER_SNAME(),
GETDATE(),
'AdventureWorks2012'
) GO
Prima che tali triggers siano creati, si dovrebbe disegnare e creare la tabella dove archiviare i DML intercettati.
Attenzione che con questo metodo si possono facilmente commettere degli errori e inoltre si deve prevedere molto lavoro manuale.
Conclusione
Abbiamo visto insieme come configurare gli auditing in Sql Server mediante SQL Server Extended Events e SQL Server triggers.