Cómo auditar su auditoría en SQL Server – rastreando cuando los desencadenadores están deshabilitados

Los desencadenadores de auditoría de SQL Server son principalmente usados para mantener la integridad de la información en la base de datos o para proveer un rastro de cambios de datos. Un desencadenador es un tipo especial de objeto de base de datos, el cual es automáticamente ejecutado bajo ciertas circunstancias – por ejemplo, las acciones realizadas por el usuario. Lo que los desencadenadores de auditoría deben proveer mientras auditan cambios en los datos son respuestas a las siguientes preguntas forenses:

1. ¿Quién cambió los datos?
2. ¿Cuál era la fecha y la hora cuando el cambio ocurrió?
3. El tipo del cambio de datos – ya sea que fue insertado, actualizado o eliminado
4. En caso de que el cambio haya sido una sentencia de modificación de datos (UPDATE), ¿cuál era el valor del dato antes y después del cambio?
5. Qué objeto de SQL Server fue cambiado – por ejemplo, si fue una tabla, un desencadenador o una sola fila.

Hay un rango amplio de escenarios de cambios maliciosos de datos que pueden ser realizados sin dejar ningún rastro (por ejemplo, cuentas de banco, ventas o datos de sistemas de comisiones). Para hacer eso, un usuario podría deshabilitar cualquier desencadenador de datos, cambiar los datos, y habilitar el desencadenador después.

Un desencadenador puede ser deshabilitado usando T-SQL o la Interfaz Gráfica de SQL Server Management Studio. De cualquier manera, para habilitar o deshabilitar un desencadenador DML, el usuario debe tener un permiso ALTER como mínimo en la tabla en la cual el desencadenador fue creado.

“Deshabilitar un desencadenador no lo borra. El desencadenador aún existe como un objeto en la base de datos actual. De todas maneras, el desencadenador no se activará cuando cualquier sentencia INSERT, UPDATE o DELETE en la cual estaba programado sea ejecutada. Los desencadenadores que están deshabilitados pueden ser rehabilitados. Habilitar un desencadenador no lo recrea. El desencadenador se activa en la misma manera que cuando fue originalmente creado” [1]

Los mismos requerimientos de permisos se aplican para eliminar un desencadenador, pero no consideraremos este como un escenario común – el desencadenador eliminado debe ser recreado después para enmascarar las actividades. Como sea, tales escenarios pueden ser rastreados de la misma manera en que describiremos a continuación.

Usando la característica SQL Server Audit para rastrear cuándo los desencadenadores son deshabilitados

Actualmente, sólo SQL Server Enterprise Edition y SQL Server Developer Edition soportan la característica Audit para rastrear la habilitación o deshabilitación de desencadenadores.

Para capturar estos eventos, usted necesita crear primero una especificación de auditoría de Servidor – el objeto de SQL Server Audit recolecta acciones a nivel de base de datos o servidor y grupos de acciones:

USE master;
GO
CREATE SERVER AUDIT ServerAudit
TO FILE (FILEPATH = 'c:\audits\', MAXSIZE = 2 GB)
WITH (ON_FAILURE = CONTINUE);
GO
ALTER SERVER AUDIT ServerAudit
WITH (STATE = ON);

El siguiente paso es crear una especificación de auditoría de Base de Datos al nivel de la base de datos. El grupo de auditoría que necesitamos capturar es en nuestro caso SCHEMA_OBJECT_CHANGE_GROUP – No hay un grupo de auditoría que exclusivamente capture eventos de habilitación/deshabilitación de desencadenadores.

USE [ACMEDB];
GO
CREATE DATABASE AUDIT SPECIFICATION schema_change
FOR SERVER AUDIT ServerAudit
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);
GO

Consulte la auditoría previamente creada usando el operador LIKE para reducir las entradas capturadas a aquellas relacionadas con la habilitación/deshabilitación de desencadenadores:

SELECT
       event_time AS [Time],
       server_principal_name AS [User],
       object_name AS [Object name],
       Statement
  FROM sys.fn_get_audit_file('c:\audits\ServerAudit*', NULL, NULL)
WHERE
       database_name
       =
       'ACMEDB'
   AND (
       Statement LIKE '%DISABLE%TRIGGER%'
    OR Statement LIKE '%ENABLE%TRIGGER%')ORDER BY
                                          [Time] DESC;

Los resultados mostrarán quién habilitó/deshabilitó un desencadenador y cuándo:

Aunque la solución que describimos es aplicable sólo para usuarios de SQL Server Enterprise Edition y SQL Server Developer Edition, una Auditoría de una Base de Datos SQL Server es bastante simple para ser implementada, y puede ayudar en rastrear cuando los desencadenadores están habilitados/deshabilitados.

Rastreando cuando los desencadenadores están deshabilitados usando un desencadenador DDL

Los desencadenadores DDL de SQL Server pueden ser usados para rastrear operaciones DDL, ya sea que los cambios fueran accidentales o deliberados. La solución de desencadenadores DDL requiere la creación y mantenimiento del almacén de información auditada y los desencadenadores.

En el siguiente ejemplo de un script SQL, el evento del desencadenador DML es capturado vía la función de SQL Server Eventdata (), usada en el desencadenador DDL. El script SQL crea un desencadenador DDL que captura las operaciones ALTER (aunque el desencadenador DDL puede capturar eventos CREATE y DROP también, para el propósito de auditar los desencadenadores DML para habilitar/deshabilitar, capturaremos sólo los eventos ALTER):

CREATE TRIGGER DDL_Audit
ON DATABASE
    FOR ALTER_TABLE
AS
     DECLARE
        @auditevent xml;
     SET
     @auditevent = EVENTDATA();
     INSERT INTO DDL_Audit_Events
     VALUES
     (
     REPLACE(CONVERT(varchar(50),
     @auditevent.query('data(/EVENT_INSTANCE/PostTime)')), 'T', ' ')
     ,
     CONVERT(varchar(150),
     @auditevent.query('data(/EVENT_INSTANCE/LoginName)'))
     ,
     CONVERT(varchar(150),
     @auditevent.query('data(/EVENT_INSTANCE/DatabaseName)'))
     ,
     CONVERT(varchar(150),
     @auditevent.query('data(/EVENT_INSTANCE/ObjectName)'))
     ,
     CONVERT(varchar(max),
     @auditevent.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
     );

Los datos de auditoría de EVENTDATA XML deberían ser almacenados en una tabla apropiada:

CREATE TABLE DDL_Audit_Events
(
             DDL_Event_Time            datetime
             ,
             DDL_Login_Name            varchar(250)
             ,
             DDL_Database_Name         varchar(250)
             ,
             DDL_Object_Name           varchar(250)
             ,
             DDL_Command              varchar(max)
);

El desencadenador DDL_Audit se activa en cada evento ALTER y la información capturada puede ser consultada desde DDL_Audit_Events la tabla de repositorio de auditoría:

SELECT
       DDL_Event_Time
       , DDL_Login_Name
       , DDL_Database_Name
       , DDL_Object_Name
       , DDL_Command
  FROM ACMEDB.dbo.DDL_Audit_Events WHERE
DDL_Command LIKE '%DISABLE%TRIGGER%'
OR DDL_Command LIKE '%ENABLE%TRIGGER%'
ORDER BY
         DDL_event_time DESC;

Los resultados son similares a los resultados de la característica SQL Server Audit:

Aunque este método puede producir resultados de auditoría viables, rastrear cuando los desencadenadores están deshabilitados usando un desencadenador DDL tiene muchas desventajas. Primero que nada, un usuario con suficientes permisos (el mínimo es un permiso ALTER en la tabla en la cual el desencadenador fue creado) puede fácilmente deshabilitar el desencadenador DDL, y deshabilitar el desencadenador DML después. Otra manera de evitar el rastrear por desencadenadores DDL es realizar un cambio de habilitar/deshabilitar en un desencadenador DML y luego eliminar la información capturada desde el repositorio de auditoría del desencadenador DDL.

Ambos métodos descritos, la característica SQL Server Audit y los desencadenadores DDL, capturan más eventos que los necesarios. Todos los cambios en el esquema (por ejemplo, vistas, claves primarias y foráneas, índices, permisos…) son capturados, no sólo se habilita o deshabilita los desencadenadores.

La característica SQL Server Audit no está disponible en todas las ediciones de SQL Server. Adicionalmente, los métodos descritos necesitan ser manualmente aplicados a todas las instancias SQL Server y sus bases de datos, y la auditoría debe ser mantenida, por ejemplo, el mantenimiento de los repositorios de información capturada, y la configuración de la auditoria para cualquier base de datos nueva.

Rastreando cuándo los desencadenadores están deshabilitados con ApexSQL Log

ApexSQL Log es una herramienta de auditoría y recuperación para base de datos SQL Server revierte o reproduce cambios en el esquema y los datos que han afectado a la base de datos. La información auditada puede ser capturada incluso para las operaciones ejecutadas antes de que ApexSQL Log fuera instalado, ya que usa el registro de transacciones de la base de datos y copias de seguridad de registros que ya contienen información acerca de los cambios hechos a la base de datos.

Para rastrear cuándo los desencadenadores están deshabilitados/hablitados usando ApexSQL Log:

1. Conéctese a la base de datos que desea auditar

   

2. En el paso de sesión Select SQL logs to analyze, añada copias de seguridad de registros de transacciones y/o registros de transacciones desasociados conteniendo los datos requeridos para crear la cadena completa, y una copia de seguridad completa de la base de datos como punto de partida/llegada de la cadena.

   

3. Use el paso de sesión Filter setup y la sección Date/time range para especificar el rango de tiempo para el proceso de auditoría. Esto acelerará la lectura reduciendo el proceso de búsqueda.

   

4. Use el filtro Operations para reducir la búsqueda a operaciones de habilitar/deshabilitar desencadenadores: Deseleccione todas las operaciones DML y DDL, y seleccione la opción Enable/disable trigger solamente.

   

5. Cuando todos los filtros han sido configurados apropiadamente, use la opción Open result in grid view para iniciar el proceso.

Después de que el proceso es finalizado, la cuadrícula principal de la aplicación muestra las transacciones que cambiaron el estado deshabilitado/habilitado de los desencadenadores de la base de datos. Las transacciones pueden ser exportadas para un análisis posterior, o retrotraídas vía la opción Create undo script, para revertir los cambios DDL.

Comparado con las herramientas nativas de SQL Server, para auditar cuando los desencadenadores están habilitados/deshabilitados, ApexSQL Log:

• Muestra la información auditada incluso antes para las operaciones ejecutadas antes de que se instalara concatenando diferentes archivos de registros de transacciones en uno solo.
• No requiere ninguna acción adicional en los ajustes por defecto de la instancia/base de datos de SQL Server – es suficiente mantener las bases de datos en el modelo de recuperación Completo y mantener la cadena completa de registros de transacciones.
• No requiere conocimiento de codificación T-SQL.
• No usa ningún sistema adicional o recursos de SQL Server para capturar información de auditoría
• Es independiente de la edición de SQL Server.
• Convierte los datos a CSV, HTML, XML o SQL y le ayuda a grabar el contenido para un análisis posterior.

Recursos útiles:
[1] Delete or Disable DML Triggers
[2] Using the Default Trace in SQL Server 2005 and SQL Server 2008
[3] Fire a DDL TRIGGER when the new syntax «DISABLE TRIGGER» is executed
[4] SQL Server Audit (Database Engine)
[5] Create server audit specification
[6] Create database audit specification

Traductor: Daniel Calbimonte