Rastreando cambios DDL en SQL Server – el ‘Problema con los Desencadenadores’ (4215906)

Rastrear cambios es una tarea de seguridad esencial de SQL Server. Aparte del historial de cambios, que incluye las operaciones DML (por ejemplo, INSERT, UPDATE y DELETE), el rastreo de cambios DDL en SQL Server, cambios que afectan los objetos de la base de datos, es de gran importancia también. Respecto de esto, varias técnicas pueden ser usadas como una solución de auditoría de cambios de esquema. Uno de los métodos más comunes es el de Desencadenadores DDL.

Los desencadenadores DDL SQL Server pueden ser usados para rastrear cambios DDL, ya sea cambios que fueron intencionales o accidentales. Quién envió el cambio, cuándo y qué fue afectado por la acción es la información básica que necesita ser capturada usando desencadenadores. Para usar esta solución, se requiere crear y mantener los desencadenadores y el almacenaje de la información capturada. Veamos cómo rastrear cambios DDL en SQL Server vía trabajos de desencadenadores.

Rastrear cambios DDL usando desencadenadores

Un desencadenador es un tipo especial de objeto de base de datos que es automáticamente ejecutado bajo ciertas condiciones, por ejemplo, acciones realizadas por el usuario. Los desencadenadores DDL son usados para auditar operaciones CREATE, ALTER, DROP y muchas otras que realizan operaciones DDL (GRANT, DENY, REVOKE o UPDATE STATISTICS).

En el siguiente ejemplo, la información acerca del evento que gatilló el desencadenador es capturada usando la función de SQL Server EVENTDATA(). El script SQL crea un desencadenador DDL que captura eventos CREATE, ALTER y DROP a nivel de base de datos (aunque los desencadenadores pueden ser creados a nivel del servidor para capturar eventos para todas las bases de datos en el servidor; la opción ON ALL SERVER debería usarse en lugar de ON DATABASE):

CREATE TRIGGER Audit_DDL
ON DATABASE
    FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
     DECLARE
        @event xml;
     SET
     @event = EVENTDATA();
     INSERT INTO Audit_DDL_Events
     VALUES
     (
     REPLACE(CONVERT(varchar(50),
     @event.query('data(/EVENT_INSTANCE/PostTime)')), 'T', ' ')
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/LoginName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/UserName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/DatabaseName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/SchemaName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/ObjectName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/ObjectType)'))
     ,
     CONVERT(varchar(max),
     @event.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
     );

Una tabla de almacenamiento apropiada para los datos de auditoría desde EVENTDATA XML debe ser creada también:

CREATE TABLE Audit_DDL_Events
(
             DDL_Event_Time            datetime
             ,
             DDL_Login_Name            varchar(150)
             ,
             DDL_User_Name             varchar(150)
             ,
             DDL_Database_Name         varchar(150)
             ,
             DDL_Schema_Name           varchar(150)
             ,
             DDL_Object_Name           varchar(150)
             ,
             DDL_Object_Type           varchar(150)
             ,
             DDL_Command              varchar(max)
);

Ahora que hemos configurado el escenario para rastrear cambios DDL, la información en caso de crear, modificar o eliminar una tabla capturada vía desencadenadores se vería así:

Aunque hemos mostrado que este método puede producir resultados de auditoría viables, rastrear cambios DDL en SQL Server usando desencadenadores tiene muchas desventajas. Primero que nada, un usuario con suficientes permisos puede fácilmente deshabilitar los desencadenadores. Otra manera de evitar el rastreo de cambios DDL e SQL Server con desencadenadores es realizar un cambio de esquema y luego eliminar la información capturad del repositorio de auditoría. En nuestro caso, simplemente eliminar una entrada apropiada de la tabla Audit_DDL_Events. Además, si no existen usuarios maliciosos con privilegios que puedan realizar cambios DDL, este método no puede proveer información acerca de cambios que ocurrieron antes de que se configurara.

Rastrear cambios DDL usando ApexSQL Log

ApexSQL Log es una herramienta de auditoría y recuperación para bases de datos SQL Server que audita, revierte o reproduce cambios de datos o esquema que han afectado a la base de datos. La información auditada puede ser provista incluso para las operaciones que fueron ejecutadas previamente a la instalación de ApexSQL Log, ya que lee el registro de transacciones de la base de datos y las copias de seguridad de registros de transacciones, los cuales contienen registros describiendo los cambios hecho a la base de datos.

Para rastrear cambios DDL en SQL Server usando ApexSQL Log:

1. Conéctese a la base de datos.

   

2. Añada copias de seguridad de registro s de transacciones y/o registros de transacciones sueltos que contienen los datos requeridos para crear la cadena completa.

   

3. En el mismo paso haga clic en Add para proveer la copia de seguridad completa de la base de datos, la cual será usada como un punto de partida desde el cual la cadena completa de transacciones comienza.

   

4. Use Filter setup y la sección Time range para especificar el punto de tiempo objetivo para el proceso de auditoría (el espacio de tiempo que desea auditar para operaciones DDL). Esto reducirá la búsqueda y acelerará el proceso de lectura.

   

5. Finalmente, use el filtro Operations para reducir la búsqueda a sentencias DDL solamente. Para hacer esto, deseleccione todos los DML y seleccione las operaciones DDL deseadas (o todas).

6. Cuando todo haya sido configurado, haga clic en Finish

Cuando el proceso haya finalizado, la cuadrícula principal mostrará las transacciones que pueden ser retrotraídas para revertir los cambios DDL o exportadas para un análisis posterior.

Usted puede exportar la información auditada como: CSV, HTML, un script SQL, un SQL masivo, y XML. Para hacerlo, use la opción Export en la barra de herramientas principal, y seleccione el formato de salida deseado. Las opciones de script SQL y SQL masivo pueblan las tablas SQL del repositorio con datos capturados, proveyendo la fuente para sus propios reportes de auditoría personalizados.

Finalmente, el proceso de rastreo de cambios DDL en SQl Server usando ApexSQL Log puede ser automatizado usando la Interfaz de Línea de Comandos como se explicó en el artículo en línea Automatizando la lectura diaria de registros de transacciones.

Conclusión

Como se describió, tanto los desencadenadores DDL como ApexSQL Log capturan cambios de esquema con la misma información relevante. Pero a diferencia de los desencadenadores DDL, ApexSQL Log ofrece una técnica de auditoría simple de apuntar y hacer clic que no necesita un conocimiento inicial de codificación SQL. Es capaz de leer la información almacenada en las copias de seguridad de los registros de transacciones en línea, lo cual permite el rastreo de cambios DDL en SQL Server para las operaciones ejecutadas previamente a la instalación de la herramienta.