Auditar cambios del esquema de una base de datos SQL Server

Realizar un seguimiento de los cambios hechos a los objetos de su base de datos es una parte clave de una estrategia de seguridad de bases de datos SQL o una política de cumplimiento de normas, incluyendo, entre otras, Health Insurance Portability and Accountability Act, Sarbanes-Oxley, Payment Card Industry Data Security Standard o European Union Data Protection Directive. De todas maneras, incluso si su ambiente IT no tiene que cumplir con reglas de seguridad rigurosas, quién lo ha cambiado así como el tiempo exacto de este cambio, es invaluable cuando se solucionan problemas relacionados con el esquema, como dependencias rotas. Así que, ¿cómo audita uno cambios en el esquema de SQL Server?

Una opción viable es crear y mantener desencadenadores DDL para cada uno de los objetos de la base de datos, los cuales serán activados cuando sea que un objeto de base de datos es creado, eliminado o alterado. Un desencadenador DDL captura información en el EVENT que lo gatilló usando la función EVENTDATA() la cual retorna la información capturada en formato XML. Para configurar la auditoría del esquema usando desencadenadores DDL:

1. Prepare la infraestructura de auditoría creando una tabla que contendrá la información capturada. Por ejemplo, si usted desea capturar un cambio de objeto así como el tiempo en el que el cambio fue hecho y la información de inicio de sesión, el anfitrión y la aplicación usada para hacer el cambio, la tabla debería tener la siguiente estructura:

   CREATE TABLE Audit_Info
   (
          EventTime            DATETIME,
          LoginName            VARCHAR(255),
          UserName             VARCHAR(255),
          HostName             VARCHAR(255),
          ApplicationName      VARCHAR(255),
          DatabaseName         VARCHAR(255),
          SchemaName           VARCHAR(255),
          ObjectName           VARCHAR(255),
          ObjectType           VARCHAR(255),      
          DDLCommand           VARCHAR(MAX)
   )

2. Defina el alcance de la auditoría. Usted puede auditar todos los cambios de esquema en la base de datos especificando la opción ON DATABASE o en la instancia SQL entera especificando la opción ON ALL SERVER
3. Cree los desencadenadores DDL. Por ejemplo, para auditar cambios hechos a las tablas de la base de datos use lo siguiente:

   CREATE TRIGGER Audit_Table_DDL
   ON DATABASE
   FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
   AS
   DECLARE       @eventInfo XML
   SET           @eventInfo = EVENTDATA()
    
   INSERT INTO Audit_Info VALUES
   (
        REPLACE(CONVERT(VARCHAR(50),
               @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
        CONVERT(VARCHAR(255),
             @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')),
        CONVERT(VARCHAR(255),
             @eventInfo.query('data(/EVENT_INSTANCE/UserName)')),
        CONVERT(VARCHAR(255),
             @eventInfo.query('data(/EVENT_INSTANCE/HostName)')),
        CONVERT(VARCHAR(255),
             @eventInfo.query('data(/EVENT_INSTANCE/ApplicationName)')),
        CONVERT(VARCHAR(255),
             @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')),
        CONVERT(VARCHAR(255),
             @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')),
        CONVERT(VARCHAR(255),
             @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')),
        CONVERT(VARCHAR(255),
             @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')),
        CONVERT(VARCHAR(MAX),
             @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
   )

De todas maneras, este enfoque viene con ciertas advertencias. Por ejemplo, usted puede usar desencadenadores DDL para auditar sentencias CREATE, DROP y ALTER TRIGGER pero un usuario privilegiado podría usar el comando DISABLE TRIGGER y hacer a la auditoría efectivamente inútil, dado que los desencadenadores DDL no pueden capturarlo; es decir, un usuario privilegiado puede cambiar un objeto auditado, luego deshabilitar los desencadenadores DML en la tabla que almacenó los cambios capturados, eliminar las filas que contienen los detalles acerca del objeto cambiado y habilitar los desencadenadores DML de nuevo. De esta manera un usuario malicioso puede efectivamente hacer estragos en el esquema de la base de datos sin ser detectado por el sistema de auditoría. Incluso si usted es el único usuario privilegiado en SQL Server, este tipo de sistema de auditoría tiene la desventaja inherente de no poder reportar acerca de ningún cambio hecho antes de que se ejecutara. Así que, ¿hay alguna manera efectiva de auditar un esquema SQL Server y cambios de objetos?

Afortunadamente sí. El registro de transacciones mantiene un registro de cada cambio hecho a la base de datos incluyendo información acerca de cuándo se hizo el cambio y quién lo hizo. La mejor parte es: debido a su naturaleza, la información no puede ser manipulada. Pero hay un detalle: el registro de transacciones no es legible para un humano. Aquí es donde ApexSQL Log entra en escena

ApexSQL Log es una herramienta de auditoría y recuperación para bases de datos SQL Server que lee registros de transacciones, copias de seguridad de registros, registros de transacciones separados, copias de seguridad de bases de datos y auditorías, revierte o reproduce cambios de objetos y datos que han afectado a la base de datos, incluyendo aquellos que han ocurrido antes de que el producto se instalara.

Para auditar un esquema SQL Server y cambios de objetos con ApexSQL Log:

1. Inicie ApexSQL Log
2. Conéctese a la base de datos

3. Si usted cuenta con cualquier copia de seguridad de registros y/o registros de transacciones separados, haga clic en el botón Add file, seleccione los archivos fuente apropiados y haga clic en Next para avanzar a través del asistente

   

4. En el paso Filter setup del asistente, use varios filtros para reducir los resultados a un rango de tiempo, operación, usuario, objeto o fila de datos específicos. Nota: Las operaciones DDL no son auditadas por defecto, así si uno desea verlas en los resultados auditados, lo mismo debería ser añadido a través del filtro Operations:

   

5. Haga clic en Finish para Abrir los resultados en la cuadrícula

Los cambios que cumplen con el criterio especificado, junto con sus detalles, serán listados en la cuadrícula principal de la aplicación. Para reducir más el conjunto de resultados, use Grid filter en el panel izquierdo.

En resumen, si usted desea asegurar que no se le pase ningún cambio al esquema SQL Server y los objetos, examine su registro de transacciones con ApexSQL Log

Traductor: Daniel Calbimonte