CREATE SERVER AUDIT (Transact-SQL)

Artículo
10/01/2013

Crea un objeto de auditoría de servidor utilizando SQL Server Audit. Para obtener más información, vea SQL Server Audit (motor de base de datos).

Icono de vínculo a temas Convenciones de sintaxis de Transact-SQL

Sintaxis

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 ' }

Argumentos

TO { FILE | APPLICATION_LOG | SECURITY_LOG }
Determina la ubicación del destino de la auditoría. Las opciones son un archivo binario, el registro de la aplicación Windows o el registro de seguridad de Windows. SQL Server no puede escribir en el registro de seguridad de Windows sin configurar valores adicionales en Windows. Para obtener más información, vea Escribir eventos de auditoría de SQL Server en el registro de seguridad.
FILEPATH ='os_file_path'
La ruta de acceso del registro de auditoría. El nombre de archivo se genera en función del nombre de la auditoría y del GUID de la auditoría.
MAXSIZE = { max_size }
Especifica el tamaño máximo que puede alcanzar el archivo de auditoría. El valor de max_size debe ser un entero seguido de MB, GB, TB o UNLIMITED. El tamaño mínimo que puede especificarse para max_size es 2 MB y el máximo es 2.147.483.647 TB. Si se especifica UNLIMITED, el archivo crecerá hasta que se llene el disco. (0 también indica UNLIMITED). Si se especifica un valor inferior a 2 MB, se producirá el error MSG_MAXSIZE_TOO_SMALL. El valor predeterminado es UNLIMITED.
MAX_ROLLOVER_FILES ={ integer | UNLIMITED }
Especifica el número máximo de archivos que se deben conservar en el sistema de archivos además del archivo actual. El valor de MAX_ROLLOVER_FILES debe ser un entero o UNLIMITED. El valor predeterminado es UNLIMITED. Este parámetro se evalúa siempre que se reinicia la auditoría (lo que puede suceder cuando se reinicia la instancia de Motor de base de datos o cuando se desactiva la auditoría y, a continuación, se activa de nuevo) o cuando se necesita un nuevo archivo porque se ha alcanzado el MAXSIZE. Cuando se evalúa MAX_ROLLOVER_FILES, si el número de archivos supera la configuración de MAX_ROLLOVER_FILES, se elimina el archivo más antiguo. Como resultado, cuando la configuración de MAX_ROLLOVER_FILES es 0 se crea un nuevo archivo cada vez que se evalúa la configuración de MAX_ROLLOVER_FILES. Se elimina solo un archivo automáticamente cuando se evalúa la configuración de MAX_ROLLOVER_FILES, de modo que cuando se disminuye el valor de MAX_ROLLOVER_FILES, el número de archivos no se reduce a menos que se eliminen manualmente los archivos antiguos. El número máximo de archivos que se pueden especificar es 2.147.483.647.
MAX_FILES =integer
Especifica el número máximo de archivos de auditoría que pueden crearse. No realiza la sustitución incremental al primer archivo cuando se alcanza el límite. Cuando se alcanza el límite de MAX_FILES, cualquier acción que ocasione la generación de eventos de auditoría adicionales producirá un error y se mostrará un mensaje.
RESERVE_DISK_SPACE = { ON | OFF }
Esta opción preasigna el archivo en el disco al valor de MAXSIZE. Solo se aplica si MAXSIZE no es igual a UNLIMITED. El valor predeterminado es OFF.
QUEUE_DELAY =integer
Determina el tiempo, en milisegundos, que puede transcurrir antes de exigir que se procesen las acciones de auditoría. El valor 0 indica la entrega sincrónica. El valor mínimo que puede establecerse para la cola es 1000 (1 segundo), que es el valor predeterminado. El máximo es 2.147.483.647 (2.147.483,647 segundos, o 24 días, 20 horas, 31 minutos y 23,647 segundos). Si se especifica un número no válido, se producirá el error MSG_INVALID_QUEUE_DELAY.
ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION }
Indica si la escritura de la instancia en el destino debe suspender, continuar o detener SQL Server si el destino no puede escribir en el registro de auditoría. El valor predeterminado es CONTINUE.
- CONTINUE
  Las operaciones de SQL Server continúan. Los registros de auditoría no se conservan. La auditoría continúa intentando el registro de eventos y se reanudará si se resuelve la condición de error. La selección de la opción CONTINUE puede permitir que una actividad no se audite, con lo que se infringirían las directivas de seguridad. Utilice esta opción cuando la operación de continuación del Motor de base de datos sea más importante que el mantenimiento de una auditoría completa.
- SHUTDOWN
  Fuerza el apagado del servidor cuando la instancia de servidor que escribe en el destino no puede escribir datos en el destino de la auditoría. Para poder usarlo, es preciso utilizar un inicio de sesión con el permiso SHUTDOWN. Si el inicio de sesión no tiene dicho permiso, la función generará un error y se mostrará un mensaje de error. No se producirán eventos auditados. Utilice la opción si un error de auditoría puede poner en peligro la seguridad o la integridad del sistema.
- FAIL_OPERATION
  Las acciones de base de datos producen un error si generan eventos auditados. Las acciones que no generan eventos auditados pueden continuar, pero no pueden producirse eventos auditados. La auditoría continúa intentando el registro de eventos y se reanudará si se resuelve la condición de error. Utilice esta opción si el mantenimiento de una auditoría completa es más importante que el acceso total al Motor de base de datos.
AUDIT_GUID =uniqueidentifier
Para que sea compatible con escenarios como la creación de reflejo de la base de datos, una auditoría necesita un GUID específico que coincida con el de la base de datos reflejada. No se puede modificar el GUID una vez creada la auditoría.
predicate_expression
Especifica la expresión de predicado usada para determinar si debe procesarse o no un evento. Las expresiones de predicado se limitan a 3.000 caracteres, lo que limita los argumentos de cadena.
event_field_name
Es el nombre del campo de evento que identifica el origen del predicado. Los campos de auditoría se describen en sys.fn_get_audit_file (Transact-SQL). Todos los campos pueden auditarse excepto file_name y audit_file_offset.
number
Es cualquier tipo numérico, incluido el tipo decimal. Las limitaciones son la falta de memoria física disponible o un número demasiado grande para ser representado como un entero de 64 bits.
' string '
Una cadena ANSI o Unicode según lo requerido por la comparación de predicado. No se realiza ninguna conversión implícita de tipos de cadena para las funciones de comparación de predicado. Si se pasa el tipo incorrecto se producirá un error.

Comentarios

Cuando se crea una auditoría de servidor, está en un estado deshabilitado.

La instrucción CREATE SERVER AUDIT está en el ámbito de una transacción. Si se revierte la transacción, también se revierte la instrucción.

Permisos

Para crear, modificar o quitar una auditoría de servidor, las entidades de seguridad deben tener el permiso ALTER ANY SERVER AUDIT o CONTROL SERVER.

Al guardar información de auditoría en un archivo, para tratar de impedir su alteración, restrinja el acceso a la ubicación del archivo.

Ejemplos

A.Crear una auditoría de servidor con destino a un archivo

En el ejemplo siguiente se crea una auditoría de servidor denominada HIPPA_Audit con un archivo binario como destino y sin ninguna opción.

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

B.Crear una auditoría de servidor con destino al registro de la aplicación Windows y con opciones

En el ejemplo siguiente se crea una auditoría de servidor denominada HIPPA_Audit con destino al registro de la aplicación Windows. La cola se escribe cada segundo y apaga el motor de SQL Server si se produce un error.

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

C.Crear un servidor de auditoría que contiene una cláusula WHERE

En el ejemplo siguiente se crean una base de datos, un esquema y dos tablas para el ejemplo. La tabla denominada DataSchema.SensitiveData contendrá datos confidenciales y el acceso a la tabla debe registrarse en la auditoría. La tabla denominada DataSchema.GeneralData no contiene datos confidenciales. La especificación de auditoría de base de datos audita el acceso a todos los objetos del esquema DataSchema. La auditoría de servidor se crea con una cláusula WHERE que limita la auditoría de servidor solamente a la tabla SensitiveData. La auditoría de servidor presupone que existe una carpeta de auditoría en 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