Auditing delle istruzioni SELECT in SQL Server

Sebbene le istruzioni SELECT non siano distruttive per natura, né tantomeno possano modificare dati o schema, ci sono diversi casi che possono richiedere che vengano messe sotto osservazione (“auditing”). Le istruzioni SQL eseguite, possono indicare vari problemi potenziali o attuali e questa è la ragione per cui è importante sapere chi ha visto cosa e quando

In generale, ci sono due casi principali che richiedono il tracciamento delle istruzioni SELECT:

• Trovare chi e quando sta accedendo ai dati per risolvere problemi o anomalie di sicurezza, applicative o di prestazioni
• Per fornire i requisiti necessari (per esempio per essere conforme con HIPAA o altri regolamenti)

Ci sono diverse soluzioni native di SQL Server per tenere sotto osservazione le istruzioni SELECT

Stored procedures e funzioni

Tracciare chi ha visto che cosa può essere fatto attraverso stored procedures e funzioni dedicate. Per poter utilizzare questo metodo di auditing, l’accesso al database deve essere limitato attraverso l’uso di stored procedures (che permettono soltanto istruzioni EXEC e proibiscono tutte le query e le operazioni DML). Il risultato di una query è restituito alla stored procedure che, contemporaneamente registra gli accessi in una tabella di archiviazione apposita insieme ad altre informazioni utili (per esempio il tempo di esecuzione)

Tuttavia, questo metodo di osservazione richiede il mantenimento di stored procedures e funzioni aggiuntive (per esempio nel caso di cambio di schema del database). Inoltre, le query ad hoc eseguite da soggetti verificati (per esempio i membri del ruolo sysadmin) passano sopra facilmente alla regola per cui le istruzioni SELECT vengono analizzate da una stored procedure, e perciò queste istruzioni non possono essere tracciate. Questo stabilisce che l’uso di stored procedures e funzioni come metodo di auditing non è accettabile per qualsiasi regolamento di conformità

Tracce in SQL Server

SQL Server fornisce il monitoraggio di eventi selezionati attraverso le tracce. È una tecnologia disponibile attraverso Application Programmer Interface (API), e SQL Server Profiler come interfaccia grafica per vedere le tracce in tempo reale, oppure accedendo ai file di traccia salvati.

Per monitorare l’esecuzione delle istruzioni SELECT su un database specifico:

1. Avviare SQL Server Profiler e fornire le credenziali per l’istanza di SQL Server che contiene il database
2. Scrivere il nome della traccia (per esempio TraceSELECTs) che si sta creando

3. Selezionare il modello Blank nel menu a discesa Use the template useremo il modello vuoto poiché vogliamo catturare soltanto le istruzioni SELECT, e non riempire i dati di monitoraggio con altre informazioni

   

4. Spuntare l’opzione Save to table e fornire le informazioni per la tabella di destinazione. La tabella può essere posizionata su altre istanze di SQL Server rispetto a quello sotto osservazione; in questo esempio useremo la stessa

   

Useremo il database AUDITDB (un database vuoto) e nel quale va specificata un’opportuna tabella di destinazione da creare (la tabella TraceSELECTs)

5. Adesso, usando la scheda Events Selection, trovare e spuntare la voce SQL:StmtStarting nella colonna Events

   

6. Cliccare Column Filters per aprire la finestra di dialogo Edit Filter

7. Selezionare la proprietà TextData filter e inserire “SELECT%” come valore per il filtro Like

   

8. Siccome vogliamo monitorare soltanto uno specifico database (il database ACMDB nel nostro esempio), aggiungiamo un filtro aggiuntivo. Selezionare il filtro DatabaseName ed inserire “ACMEDB” come valore per il filtro Like

   

9. Premere OK per confermare i cambi al filtro.
10. Selezionare l’opzione Run per lanciare l’osservazione tramite la traccia definita TraceSELECTs

Qualsiasi istruzione SELECT sul database ACME sarà tracciata e catturata nella tabella TraceSELECTs che se interrogata, restituisce le seguenti informazioni:

I dati sotto osservazione contengono le informazioni più rilevanti per questo tipo di auditing (per esempio il nome del database e del login, il tempo di esecuzione e le esatte istruzioni SELECT). Tuttavia, ci sono diverse controindicazioni per questo tipo di ispezione usando le tracce native in SQL Server

Questo metodo di auditing non è una soluzione pronta per l’uso. Richiede delle precise impostazioni manuali usando filtri, non fornisce una soluzione contro la manomissione delle informazioni catturare da parte di soggetti verificati e non c’è nessuna capacità di archiviazione. In aggiunta, per esportare i dati e creare gli opportuni reports, questo metodo richiede la conoscenza di T-SQL

Funzionalità di SQL Server Audit

La funzionalità di Audit è stata introdotta in SQL Server 2008. Essa utilizzata la tecnologia degli Extended Events e monitora eventi sia sul server che sul database. Però, l’auditing a livello di database è supportato soltanto dalle edizioni Enterprise e Developer.

Usare SQL Server Audit come metodo per tracciare l’esecuzione delle istruzioni SELECT produce meno sovraccarico delle tracce, ma a seconda di quanto il database è occupato, ci potrebbe essere un impatto sulle prestazioni del server.

In questo articolo ci concentreremo su SQL Server Management Studio; si possono facilmente creare e vedere i corrispondenti scripts T-SQL usando l’opzione CREATE To in SQL Server Management Studio

Per osservare l’esecuzione di una istruzione SELECT su uno specifico database:

1. Espandere la cartella Security

2. SelezionareNew Audit e impostare Audit name (per esempio AuditSELECTsServerSpecification) ed il percorso del file (per esempio C:\AUDITs) nella finestra di dialogo Create Audit

   

   Il corrispondente file .sqlaudit sarà generato nella cartella specifica (C:\AUDITs) utilizzata come archivio per i dati di monitoraggio

3. Confermare la creazione dell’oggetto di audit SQL Server cliccando OK
4. Cliccare con il destro sull’audit appena creato e scegliere l’opzione Enable Audit

I seguenti passi descrivono come creare una specifica di audit di database, la quale richiede che sia stato precedentemente creato un oggetto audit di SQL Server (AuditSELECTsServerSpecification). È la specifica di audit del database che ottimizzerà il tracciamento delle istruzioni SELECT

5. Espandere il database che si vuole osservare in Object Explorer
6. Espandere la cartella Sicurezza
7. Cliccare con il destro sulla cartella Database Audit Specification e selezionare New Database Audit Specification
8. Scrivere il nome della nuova specifica di database (per esempio AuditSELECTsDatabaseSpecification) e scegliere l’oggetto di audit prima creato usando il menu a discesa Audit Per ottimizzare l’auditing impostare le seguenti opzioni:
   1. Audit Action Type: SELECT
   2. Object Class: DATABASE
   3. Object Name: ACMEDB

   4. Principal: public – gli audit sono loggati soltanto se il soggetto esegue una istruzione SELECT. Siccome vogliamo monitorare tutti i SELECT, a prescindere da chi li ha eseguiti, il nome del soggetto dovrebbe essere “public”

      

9. Confermare la creazione della specifica di audit del database cliccando OK
10. Finalmente, cliccare con il destro sull’audit creato e selezionare l’opzione Enable Database Audit Specification option

Dopo che l’oggetto di audit e la specifica di audit sono stati impostati ed abilitati, ogni SELECT eseguito su qualsiasi tabella sarà intercettato e salvato nel file .sqlaudit

Per vedere le SELECT oggetto di osservazione:

1. Click con il destro sull’audit AuditSELECTsServerSpecification e selezionare l’opzione View Audit Logs dal menu contestuale per lanciare il visualizzatore del log

2. Il visualizzatore mostra le informazioni sulle istruzioni SELECT rilasciate sul particolare database

   

Naturalmente, questo non è né un metodo conveniente né un buon formato per fornire dati da ispezionare. L’altro modo per esportare e fornire dati catturati è tramite la funzione fn_get_file_audit. Questa funzione legge il file *.sqlaudit creati dalla funzionalità SQL Server Audit

Il seguente script interroga le informazioni catturate relativamente ad una istruzione SELECT eseguita sul database ACMEDB:

SELECT
       event_time ,
       session_server_principal_name AS UserName ,
       server_instance_name ,
       database_name ,
       object_name ,
       statement
  FROM sys.fn_get_audit_file('C:\AUDITs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE
      action_id = 'SL'
  AND
       database_name = 'ACMEDB';

La funzionalità di SQL Server Auditing ha meno impatto sulle prestazioni del server e fornisce un miglior affinamento dell’osservazione rispetto all’uso delle tracce. D’altro canto è disponibile soltanto in due edizioni di SQL Server, non c’è un modo facile né per archiviare i dati né per evitare manomissioni, richiede un setup manuale per ciascuna istanza e database di SQL Server e la conoscenza di T-SQL è necessaria per analisi approfondite e reporting.

La soluzione pronta per l’uso

ApexSQL Audit è uno strumento di auditing e compliance che traccia e riporta eventi su SQL Server attraverso l’osservazione degli accessi e delle modifiche su una istanza di SQL Server ed i suoi oggetti. Esso fornisce un insieme di report predefiniti insieme con un visualizzatore di report per la creazione di report di auditing personalizzati, usando la tecnica drag-and-drop.

Per monitorare l’esecuzione delle SELECT su database specifici su più istanze di SQL Server:

1. Lanciare ApexSQL Audit

2. Selezionare l’istanza di SQL Server, spuntare il database di cui si vuole monitorare le istruzioni SELECT e spuntare l’opzione Query

3. Opzionalmente, spuntare le tabelle che possono essere parte di una istruzione SELECT oggetto di osservazione – al contrario se si vogliono escludere alcune tabelle dal monitoraggio scegliere il bottone Exclude objects allo scopo di preservare lo spazio sul disco fisso occupato dal database di archiviazione centrale di ApexSQL ApexSQL:

   

4. Confermare la propria scelta, selezionando l’opzione Apply sul pop-up giallo

   

I report predefiniti di ApexSQL Audit che forniscono i dati con le SELECT catturate sono Complete audit trail e Access history.

Per vedere le SELECT catturate, premere il bottone View reports nella barra degli strumenti di ApexSQL Audit e usare uno dei report prima elencati

Possiamo anche usare Custom reports per specificare le voci del report con maggiore dettaglio:

Nel processo di auditing delle istruzioni SELECT, ApexSQL Audit fornisce:

• La configurazione delle impostazioni stile “clicca e punta”
• Ispezione dei dati e reportistica senza uso o conoscenza di T-SQL
• Supporto per tutte le edizioni di SQL Server, tranne che per l’edizione SQL Server Express
• Monitoraggio automatico e registrazione delle azioni utente che includono l’esecuzione di SELECT, a prescindere dal ruolo a cui appartiene l’utente
• Rischi di sicurezza e identificazione dell’impatto sulle prestazioni riportando tutte le istruzioni SELECT nella loro originale forma T-SQL
• Un archivio centralizzato con funzionalità di registrazione
• Evidenza di manomissione dei dati, report accurati e completi per le revisioni

L’auditing dei SELECT può anche essere ottenuto o attraverso metodi SQL Server nativi o usando strumenti di terze parti come ApexSQL Audit. Sebbene i metodi nativi siano predefiniti (ma non in tutte le edizioni di SQL Server) essi non offrono archivi che diano evidenza sui dati manomessi e richiedono conoscenze avanzate per le impostazioni e la reportistica. D’altra parte, ApexSQL Audit fornisce un metodo user-friendly ed efficiente per il monitoraggio e la reportistica delle istruzioni SELECT insieme con tutti gli ulteriori benefici elencati.

Risorse utili
Auditing SELECT Statements in SQL Server 2008 Standard using SQL Trace
Auditing in SQL Server 2008
MSDN – SQL Server Audit (Database Engine)
MSDN – SQL Server Audit Action Groups and Actions
MSDN – SQL Server Audit Records
MSDN – Create a Server Audit and Server Audit Specification