Tecniche di auditing per database SQL Server

Mettere sotto osservazione (auditing) un database SQL Server non serve soltanto per essere conformi ai requisiti previsti dalle regole di conformità. È 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.

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 oservazione 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:

1. In SQL Server Management Studio, espandi Security e clicca con il destro su Audits

2. Seleziona New Audit

   

3. 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.

   

4. Clicca OK e il tuo audit apparirà nel nodo Audits dell’Object Explorer di Management Studio

5. Di default l’audit è disabilitato e per questo viene mostrato con una freccia rossa. Per abilitarlo, click con il destro e seleziona Enable Audit.

   

6. 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.

7. 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

   

8. 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 in questa 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

Per esempio:

SELECT event_time,action_id,statement,database_name,server_principal_name
  FROM fn_get_audit_file( 'E:\Test\Audit-*.sqlaudit' , DEFAULT , DEFAULT);
      

Restituisce il seguente risultato:

Però, anche selezionando tutte le colonne, 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 user name 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.

ApexSQL Trigger è uno strumento per il database auditing che cattura i cambiamenti intervenuti sui dati e sugli schema in un database comprese le informazioni su chi ha fatto le modifiche, quali oggetti sono stati interessati, quando è stato è stato fatto come pure tutte le informazioni su login SQL, applicazione e host usati per apportare i cambiamenti. Lo strumento archivia tutte informazioni catturate in un deposito centrale e le esporta in formato stampabile. Per creare dei triggers, si devono solamente selezionare le tabelle ed il tipo di operazioni da monitorare.

1. AvviareApexSQL Trigger

2. Connettersi al database da monitorare

   

3. Nella griglia principale selezionare la tabella da mettere sotto osservazione

   

4. Nel pannello Columns selezionare le colonne da mettere sotto osservazione

   

5. Impostare le transazioni da monitorare – INSERT, DELETE, UPDATE

6. Ripetere i passaggi 3 e 5 per tutte le tabelle interessate

7. Nel menu, cliccare Create triggers

8. Lo script che produce i triggers specificati è mostrato nella finestra di dialogo Script. Controllare che sia tutto a posto e premere F5 per eseguirlo

   

Una volta che i triggers sono creati, essi vengono attivati per ogni INSERT, DELETE, UPDATE eseguito nella tabella ed i dettagli delle operazioni sono archiviati dentro le tabelle AUDIT_LOG_DATA e AUDIT_LOG_TRANSACTIONS

Si possono facilmente vedere i dati usando i report predefiniti di Apex SQL Trigger, oppure scrivendo delle queries SQL di proprio pugno.

Sicuramente i triggers forniscono un livello di dettaglio granulare, ma il loro più grande svantaggio è che possono causare un sovraccarico di lavoro su un database molto già molto impegnato.

Leggere il transaction log

Poiché ogni variazione di schema e di dati in un database SQL Server è aggiunta ad un transaction log online come record del log, leggere questi records può essere usata come tecnica di auditing. Aprire il transaction log online di un database, i transaction log archiviati o i backup e leggerli non è proprio semplice. Una delle opzioni è di utilizzare funzioni non documentate come fn_dblog, fn_dump_dblog e DBCC PAGE.

Oltre la complessità ed i problemi con la ricostruzione di UPDATE/BLOB, il loro più grande svantaggio è che riportano valori esadecimali, che devono essere decifrati.

Per leggere il transaction log, usa un lettore di SQL Server transaction log come ApexSQL Log. Esso monitora, ripristina o replica le modifiche ai dati ed agli oggetti che hanno interessato il database, comprese quelle che si sono verificate prima dell’installazione di ApexSQL Log. Il tool inoltre cattura le informazioni su utente, applicazione e host che hanno fatto ogni modifica.

1. Avviare ApexSQL Log

2. Connetters al database da osservare

   

3. Nel punto Select SQL logs to analyze, aggiungere i backup del transaction log ed i transaction logs offline da leggere. Notare che devono formare una catena completa per fornire riscontro effettivo

   

4. Usare l’opzione Filter setup per restringere il risultato usando data, ora, tipo di operazione, nome della tabella, utente ed altre opzioni di filtro

   

5. Cliccare Open

6. I risultati sono mostrati nella griglia principale dove si possono facilmente creare scripts per annullare o rifare le operazioni oppure esportarli in CSV, HTML, XML or file SQL da salvare sul disco fisso

   

I vantaggi di questo metodo sono che non ci sono triggers e nessun processo aggiuntivo per catturare le informazioni che possa influenzare le performances di SQL Server. La storia delle transazioni può essere ottenuta per il periodo precedente all’installazione del tool a differenza di triggers ed Extended Events.
Gli svantaggi sono:

• È richiesto più spazio per molte origini dati, poiché il database deve essere in full recovery mode e deve esistere una catena completa di transaction logs
• Non tutte le azioni che un utente potrebbe voler monitorare sono archiviate nel transaction log. Per esempio, le SELECT eseguite e le queries non lo sono

Using SQL Server Profiler and SQL Server traces

Usare SQL Server Profiler e le tracce per finalità di auditing è una soluzione molto complessa con molto lavoro manuale che pertanto può condurre facilmente all’errore.

ApexSQL Audit è uno strumento di auditing costruito sulle tracce di SQL Server che fornisce informazioni su “chi ha visto che cosa”, revisione sulla tolleranza agli errori, reporting centralizzato, una interfaccia grafica user friendly per le impostazioni di auditing su più di 230 operazioni, ed un archivio centralizzato a prova di manomissione per l’archiviazione di dei record di audit e delle configurazioni. Lo strumento configura le tracce in base alle impostazioni specificate dall’utente, oppure usa la propria configurazione di default che copre la maggior parte delle più comuni richieste di osservazione.

1. Avviare ApexSQL Audit

2. Cliccare il bottone ‘Add server’ per selezionare un server per l’audit.

   

3. Cliccare il bottone ‘Add database’ per selezionare un database per l’auditing, e selezionare il server o il database che vuoi monitorare.

   

   Un’altra opzione è di usare il tipo filtro avanzato:

   

Adesso, ogni volta che una delle operazioni selezionate viene eseguita sul database di SQL Server in osservazione, un record viene salvato nel database di archiviazione centrale – ApexSQLCrd

Per vedere i record di auditing, si possono usare o i report locali predefiniti o la funzionalità di web report.

I vantaggi di ApexSQL Audit sono la facilità di impostazione delle opzioni di auditing, un ampio raggio di operazioni disponibili, l’archivio centrale a prova di manomissione, il web reporting cosicchè ogni utente remoto può facilmente accedere tutti i record di auditing, l’amministrazione facile e centralizzata

Come illustrato sopra, ci sono diverse tecniche di auditing per SQL Server che utilizzano differenti caratteristiche. Si va dal codice scritto a mano che può portare a degli errori, fino a interfacce grafiche user friendly. Alcune di queste possono causare sovraccarico e problemi nelle performance di SQL Server, mentre altre non hanno quasi effetto. Determina quale tipo di operazioni hai bisogno di tenere sotto controllo e scegli un ApexSQL tool di auditing di ApexSQL per impostare il monitoraggio e creare reports con pochi click del mouse

Download

Per favore scarica gli scripts associati a questo articolo dal nostro archivio GitHub

Per favore contattaci per qualsiasi problema o domanda con gli scripts