Auditing in Sql Server

Tecniche di auditing in Sql Server 2019

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.

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
Auditing in Sql Server - Creazione New Auditing
Auditing in Sql Server – Creazione New Auditing
  • 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.
Auditing in Sql Server - Configurazione New Auditing
Auditing in Sql Server – Configurazione New Auditing
  • 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
Auditing in Sql Server - Configurazione Audit su Database specifico
Auditing in Sql Server – Configurazione Audit su Database specifico

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.

Auditing in Sql Server - Configurazione Audit Specification Database
Auditing in Sql Server – Configurazione Audit Specification Database
  • 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.
Auditing in Sql Server - Enable Audit Specification Database
Auditing in Sql Server – Enable Audit Specification Database

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.

Autore Bartolomeo

Consulente tecnico con la passione per il web e la tecnologia e tutto quello che le ronza intorno.
Nel tempo libero scrivo articoli per il mio blog su argomenti vari, in particolare su configurazione Windows, Linux e WordPress.

Condividi questo articolo!

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.

Ho letto la policy privacy e accetto il trattamento dei miei dati personali in conformità al D.Lgs. 196/2003