CREATE SERVER AUDIT (Transact-SQL)

Articolo
10/01/2013

Viene creato un oggetto controllo del server utilizzando SQL Server Audit. Per ulteriori informazioni, vedere SQL Server Audit (Database Engine).

Icona di collegamento a un argomento Convenzioni della sintassi Transact-SQL

Sintassi

CREATE SERVER AUDIT audit_name
{
    TO { [ FILE (<file_options> [ , ...n ] ) ] | APPLICATION_LOG | SECURITY_LOG }
    [ WITH ( <audit_options> [ , ...n ] ) ] 
    [ WHERE <predicate_expression> ]
}
[ ; ]

<file_options>::=
{
        FILEPATH = 'os_file_path'
    [ , MAXSIZE = { max_size { MB | GB | TB } | UNLIMITED } ]
    [ , { MAX_ROLLOVER_FILES = { integer | UNLIMITED } } | { MAX_FILES = integer } ]
    [ , RESERVE_DISK_SPACE = { ON | OFF } ] 
}

<audit_options>::=
{
    [   QUEUE_DELAY = integer ]
    [ , ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION } ]
    [ , AUDIT_GUID = uniqueidentifier ]
}

<predicate_expression>::=
{
    [NOT ] <predicate_factor> 
    [ { AND | OR } [NOT ] { <predicate_factor> } ] 
    [,...n ]
}

<predicate_factor>::= 
    event_field_name { = | < > | ! = | > | > = | < | < = } { number | ' string ' }

Argomenti

TO { FILE | APPLICATION_LOG | SECURITY_LOG }
Determina la posizione della destinazione del controllo. Le opzioni possibili sono un file binario, il registro applicazioni di Windows o il registro di sicurezza di Windows. Se non si configurano impostazioni aggiuntive in Windows, non sarà possibile scrivere nel registro di sicurezza di Windows. Per ulteriori informazioni, vedere Scrittura di eventi di controllo di SQL Server nel registro di sicurezza.
FILEPATH ='os_file_path'
Percorso del log di controllo. Il nome del file viene generato in base al nome e al GUID del controllo.
MAXSIZE = { max_size }
Specifica le dimensioni massime consentite per l'aumento di dimensioni del file di controllo. Il valore max_size deve essere un valore intero seguito da MB, GB, TB, or UNLIMITED. Il valore minimo che è possibile specificare per max_size è 2 MB, mentre il valore massimo è 2.147.483.647 TB. Se si specifica UNLIMITED, le dimensioni del file possono aumentare fino a quando non si esaurisce lo spazio su disco. 0 indica UNLIMITED. Se si specifica un valore minore di 2 MB, verrà generato l'errore MSG_MAXSIZE_TOO_SMALL. Il valore predefinito è UNLIMITED.
MAX_ROLLOVER_FILES ={ integer | UNLIMITED }
Indica il numero massimo di file da mantenere nel file system oltre al file corrente. Il valore MAX_ROLLOVER_FILES deve essere di tipo Integer o UNLIMITED. Il valore predefinito è UNLIMITED. Questo parametro viene valutato ogni volta che il controllo viene riavviato (quando l'istanza del Motore di database viene riavviata o quando il controllo viene disabilitato e quindi riabilitato) oppure quando è necessario un nuovo file perché è stato raggiunto il valore MAXSIZE. Quando MAX_ROLLOVER_FILES viene valutato, se il numero di file supera l'impostazione di MAX_ROLLOVER_FILES, viene eliminato il file meno recente. Di conseguenza, quando l'impostazione di MAX_ROLLOVER_FILES è 0, viene creato un nuovo file ogni volta che l'impostazione di MAX_ROLLOVER_FILES viene valutata. Un solo file viene eliminato automaticamente quando viene valutata l'impostazione di MAX_ROLLOVER_FILES, pertanto quando il valore di MAX_ROLLOVER_FILES viene ridotto, il numero di file non verrà ridotto, a meno che i file obsoleti non vengano eliminati manualmente. Il numero massimo di file specificabili è 2.147.483.647.
MAX_FILES =integer
Viene specificato il numero massimo di file di controllo che possono essere creati. Quando si raggiunge il limite, non viene eseguito il rollover del primo file. Quando viene raggiunto il limite MAX_FILES, qualsiasi azione che causa la generazione di eventi di controllo aggiuntivi avrà esito negativo e verrà visualizzato un errore.
RESERVE_DISK_SPACE = { ON | OFF }
Questa opzione consente di preallocare il file sul disco in base al valore MAXSIZE. Viene applicata solo se MAXSIZE non è uguale a UNLIMITED. Il valore predefinito è OFF.
QUEUE_DELAY =integer
Specifica la quantità di tempo in millisecondi che può trascorrere prima che venga forzata l'esecuzione delle azioni di controllo. Il valore 0 indica un recapito sincrono. Il valore minimo di ritardo di query che è possibile impostare è 1000 (1 secondo). Tale valore è quello predefinito. Il valore massimo è 2.147.483.647 (2.147.483,647 secondi o 24 giorni, 20 ore, 31 minuti e 23,647 secondi). Se si specifica un numero non valido, verrà generato l'errore MSG_INVALID_QUEUE_DELAY.
ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION }
Viene indicato se l'istanza tramite cui viene effettuata l'operazione di scrittura nella destinazione deve comportare l'esito negativo, la continuazione o l'arresto di SQL Server qualora la destinazione non consenta di scrivere nel log di controllo. Il valore predefinito è CONTINUE.
- CONTINUE
  Le operazioni di SQL Server continuano. I record di controllo non vengono mantenuti. Il controllo consente di continuare il tentativo di registrazione di eventi e verrà ripreso se viene risolta la condizione di errore. Selezionando l'opzione per continuare si potrà consentire un'attività non certificata che potrebbe violare i criteri di sicurezza. Utilizzare questa opzione quando il funzionamento del Motore di database è più importante della gestione di un controllo completo.
- SHUTDOWN
  Viene forzata la chiusura del server quando risulta impossibile scrivere dati nella destinazione di controllo da parte dell'istanza del server preposta a tale compito. L'account di accesso che esegue questa operazione deve disporre dell'autorizzazione SHUTDOWN. In caso contrario, questa funzione non verrà eseguita e verrà generato un messaggio di errore. Non viene generato alcun evento controllato. Utilizzare l'opzione quando un errore a livello di controllo potrebbe compromettere la sicurezza o l'integrità del sistema.
- FAIL_OPERATION
  Le azioni del database non vengono completate se provocano eventi controllati. Le azioni che non provocano eventi controllati possono continuare, ma non si potrà verificare alcun evento controllato. Il controllo consente di continuare il tentativo di registrazione di eventi e verrà ripreso se viene risolta la condizione di errore. Utilizzare questa opzione quando la gestione di un controllo completo è più importante dell'accesso completo al Motore di database.
AUDIT_GUID =uniqueidentifier
Per supportare alcuni tipi di scenari, ad esempio il mirroring del database, a un controllo deve essere associato un GUID specifico corrispondente a quello presente nel database con mirroring. Dopo che il controllo è stato creato, il GUID non può essere modificato.
predicate_expression
Viene specificata l'espressione del predicato utilizzata per determinare se un evento deve essere o meno elaborato. Le espressioni del predicato possono essere composte da un massimo di 3000 caratteri, pertanto gli argomenti di tipo stringa risultano limitati.
event_field_name
Nome del campo relativo all'evento che consente di identificare l'origine del predicato. I campi del controllo sono descritti in sys.fn_get_audit_file (Transact-SQL). È possibile controllare tutti i campi eccetto file_name e audit_file_offset.
number
Qualsiasi tipo numerico incluso decimal. Le limitazioni sono la mancanza di memoria fisica disponibile o un numero troppo grande per essere rappresentato come un numero intero a 64 bit.
' string '
Stringa ANSI o Unicode come richiesto dal paragone del predicato. Non viene eseguita alcuna conversione del tipo di stringa implicita per le funzioni del paragone del predicato. Il passaggio del tipo non corretto comporta un errore.

Osservazioni

Quando viene creato un controllo del server, il relativo stato è disabilitato.

L'istruzione CREATE SERVER AUDIT è nell'ambito di una transazione. L'esecuzione del rollback della transazione comporta il rollback anche per l'istruzione.

Autorizzazioni

Per creare, modificare o eliminare un controllo del server, le entità devono disporre dell'autorizzazione ALTER ANY SERVER AUDIT o CONTROL SERVER.

Quando si salvano informazioni di controllo in un file, per contribuire a impedirne l'alterazione, limitare l'accesso al percorso del file.

Esempi

A.Creazione di un controllo del server con un file come destinazione

Nell'esempio seguente viene creato un controllo del server denominato HIPPA_Audit con un file binario come destinazione e nessuna opzione.

CREATE SERVER AUDIT HIPAA_Audit
    TO FILE ( FILEPATH ='\\SQLPROD_1\Audit\' );

B.Creazione di un controllo del server con il registro applicazioni di Windows come destinazione e con opzioni

Nell'esempio seguente viene creato un controllo del server denominato HIPPA_Audit con il registro applicazioni di Windows come destinazione. Nella coda viene eseguita un'operazione di scrittura al secondo e il motore di SQL Server viene arrestato in caso di errore.

CREATE SERVER AUDIT HIPAA_Audit
    TO APPLICATION_LOG
    WITH ( QUEUE_DELAY = 1000,  ON_FAILURE = SHUTDOWN);

C.Creazione di un controllo del server contenente una clausola WHERE

Nell'esempio seguente vengono creati un database, uno schema e due tabelle per l'esempio. Nella tabella denominata DataSchema.SensitiveData saranno contenuti i dati riservati mentre l'accesso alla tabella deve essere registrato nel controllo. Nella tabella denominata DataSchema.GeneralData non sono contenuti dati riservati. La specifica del controllo del database consente di controllare l'accesso a tutti gli oggetti nello schema DataSchema. Il controllo del server viene creato con una clausola WHERE che consente di limitare il controllo del server solo alla tabella SensitiveData. Per il controllo del server si presuppone l'esistenza di una cartella del controllo in C:\SQLAudit.

CREATE DATABASE TestDB;
GO
USE TestDB;
GO
CREATE SCHEMA DataSchema;
GO
CREATE TABLE DataSchema.GeneralData (ID int PRIMARY KEY, DataField varchar(50) NOT NULL);
GO
CREATE TABLE DataSchema.SensitiveData (ID int PRIMARY KEY, DataField varchar(50) NOT NULL);
GO
-- Create the server audit in the master database
USE master;
GO
CREATE SERVER AUDIT AuditDataAccess
    TO FILE ( FILEPATH ='C:\SQLAudit\' )
    WHERE object_name = 'SensitiveData' ;
GO
ALTER SERVER AUDIT AuditDataAccess WITH (STATE = ON);
GO
-- Create the database audit specification in the TestDB database
USE TestDB;
GO
CREATE DATABASE AUDIT SPECIFICATION [FilterForSensitiveData]
FOR SERVER AUDIT [AuditDataAccess] 
ADD (SELECT ON SCHEMA::[DataSchema] BY [public])
WITH (STATE = ON);
GO
-- Trigger the audit event by selecting from tables
SELECT ID, DataField FROM DataSchema.GeneralData;
SELECT ID, DataField FROM DataSchema.SensitiveData;
GO
-- Check the audit for the filtered content
SELECT * FROM fn_get_audit_file('C:\SQLAudit\AuditDataAccess_*.sqlaudit',default,default);
GO