CREATE SERVER AUDIT (Transact-SQL)

  • Artículo

Se aplica a: SQL Server Azure SQL Managed Instance

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

Convenciones de sintaxis de Transact-SQL

Sintaxis

CREATE SERVER AUDIT audit_name
{
    TO { [ FILE (<file_options> [ , ...n ] ) ] | APPLICATION_LOG | SECURITY_LOG | URL | EXTERNAL_MONITOR }
    [ 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 ]
    [ , OPERATOR_AUDIT = { ON | OFF } ]
}

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

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

Argumentos

TO { FILE | APPLICATION_LOG | SECURITY_LOG | URL | EXTERNAL_MONITOR }

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.

El objetivo URL no es compatible con SQL Server.

Importante

En Azure SQL Managed Instance, la auditoría de SQL funciona en el nivel de servidor. Las ubicaciones solo pueden ser URL o EXTERNAL_MONITOR.

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. Si esta ruta de acceso no es válida, no se crea la auditoría.

No se admite el objetivo FILEPATH para Azure SQL Managed Instance. En su lugar, deberá usar PATH.

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 se puede especificar para max_size es 2 MB y el máximo, 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 produce el error MSG_MAXSIZE_TOO_SMALL. El valor predeterminado es UNLIMITED.

No se admite el objetivo MAXSIZE para Azure SQL Managed Instance.

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 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 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_ROLLOVER_FILES no es compatible con Azure SQL Managed Instance.

MAX_FILES = integer

Válido para : SQL Server 2012 (11.x) y versiones posteriores.

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.

No se admite el objetivo RESERVE_DISK_SPACE para Azure SQL Managed Instance.

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 2147483647 (2.147.483,647 segundos, o 24 días, 20 horas, 31 minutos y 23,647 segundos). Al especificar un número no válido, se produce el error MSG_INVALID_QUEUE_DELAY.

ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION }

Indica si la escritura de la instancia en el objetivo debe suspender, continuar o detener SQL Server si el objetivo no puede escribir en el registro de auditoría. El valor predeterminado es CONTINUE.

CONTINUE

SQL Server Las operaciones de continúan. Los registros de auditoría no se conservan. La auditoría continúa intentando el registro de eventos y se reanuda si se resuelve la condición de error. La selección de la opción Continuar 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

Obliga a la instancia de SQL Server a apagarse si SQL Server no puede escribir datos en el destino de auditoría por algún motivo. El inicio de sesión que ejecuta la instrucción CREATE SERVER AUDIT debe tener el permiso SHUTDOWN en SQL Server. El comportamiento de apagado persiste aun cuando el permiso SHUTDOWN se revoque más adelante del inicio de sesión que ejecuta la instrucción. Si el usuario no tiene este permiso, la instrucción producirá un error y la auditoría no se creará. Utilice la opción si un error de auditoría puede poner en peligro la seguridad o la integridad del sistema. Para obtener más información, vea SHUTDOWN.

FAIL_OPERATION

Válido para : SQL Server 2012 (11.x) y versiones posteriores.

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

OPERATOR_AUDIT

Se aplica a: Azure SQL Managed Instance únicamente.

Indica si la auditoría capturará las operaciones de los ingenieros de soporte técnico de Microsoft cuando necesiten acceder al servidor durante una solicitud de soporte técnico.

predicate_expression

Se aplica a: SQL Server 2012 (11.x) y versiones posteriores.

Especifica la expresión de predicado usada para determinar si debe procesarse o no un evento. Las expresiones de predicado se limitan a 3000 caracteres, lo que limita los argumentos de cadena.

event_field_name

Se aplica a: SQL Server 2012 (11.x) y versiones posteriores.

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 se pueden filtrar, excepto file_name, audit_file_offset y event_time.

Nota:

Si bien los campos action_id y class_type son de tipo varchar en sys.fn_get_audit_file, solo se pueden usar con números cuando sean un origen de predicado para el filtrado. Ejecute la siguiente consulta para obtener una lista de los valores que se usarán con class_type:

SELECT spt.[name], spt.[number]
FROM   [master].[dbo].[spt_values] spt
WHERE  spt.[type] = N'EOD'
ORDER BY spt.[name];

number

Se aplica a: SQL Server 2012 (11.x) y versiones posteriores.

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.

'cadena'

Se aplica a: SQL Server 2012 (11.x) y versiones posteriores.

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 un archivo como objetivo

En el ejemplo siguiente se crea una auditoría de servidor denominada HIPAA_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 el registro de la aplicación Windows como objetivo y con opciones

En el ejemplo siguiente se crea una auditoría de servidor denominada HIPAA_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 contiene 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 da por hecho 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

Contenido relacionado