Implementación de un trigger para auditar cambios en una base de datos

En este artículo vamos a mostrar cómo funciona un disparador (trigger) de tipo DDL y su utilidad en la administración de SQL Server. Antes de entrar en detalle, refresquemos ciertos conceptos básicos sobre los comandos de Transact-SQL.

Usaremos la versión SQL Server 2016, edición Developer

Comandos DML (Data Manipulation Language).- Son comandos para modificar los datos de una tabla. Abarcan los comandos INSERT, UPDATE y DELETE. Los triggers de tipo DML existen desde versiones previas a la 2005

Comandos DDL (Data Definition Language).- Son comandos para definir los objetos a nivel de servidor (base de datos, inicios de sesión, etc.) o a nivel de base de datos (Tablas, vistas, funciones, etc.) Abarcan los comandos CREATE, ALTER y DROP. Los triggers de tipo DDL existen a partir de la versión 2005

Los triggers de tipo DDL son bastante útiles para auditar los comandos de este tipo, ejecutados por los usuarios o aplicaciones que se conectan a SQL Server. Los triggers DDL pueden ser de dos tipos

  • Triggers DDL a nivel de servidor.- Se disparan cuando se ejecuta algún comando de tipo DDL en el servidor. Por ejemplo CREATE DATABASE o ALTER LOGIN
  • Triggers DDL a nivel de base de datos.- Se disparan cuando se ejecuta algún comando de tipo DDL en una base de datos. Por ejemplo CREATE TABLE o ALTER VIEW

En este artículo nos vamos a concentrar en los triggers DDL a nivel de base de datos y en un artículo posterior veremos los triggers DDL a nivel de servidor. El objetivo es implementar una rutina que capture información de los comandos DDL ejecutados en una base de datos para guardarlos en una tabla de registro. Así podemos tener una historia de los comandos ejecutados y realizar auditorías al respecto

En primer lugar, es importante saber que, cada vez que SQL Server procesa un comando DDL, la información de dicho evento se puede obtener mediante la función EVENTDATA. Esta función retorna los detalles del evento en formato XML. Esto puede complicar las cosas porque, para consumir la información, tenemos que utilizar comandos XQuery, que es un conjunto de sentencia que permite manipular documentos XML desde Transact-SQL. En los ejemplos vamos a utilizar ese lenguaje. Si deseamos profundizar más al respecto pueden consultar la Guía de Referencia del lenguaje XQuery.

En segundo lugar, queremos explicar que es posible definir qué eventos DDL queremos capturar:

  • Podemos indicar eventos específicos, como CREATE TABLE o ALTER PROCEDURE
  • Podemos indicar eventos agrupadores de nivel intermedio como DDL_TABLE_EVENTS o DDL_PROCEDURE_EVENTS que abarcan CREATE, ALTER y DROP para un objeto específico
  • Podemos indicar eventos agrupadores de alto nivel como DDL_DATABASE_LEVEL_EVENTS, el cual captura todos los eventos para todos los objetos en la base de datos
  • Por último, se pueden utilizar combinaciones de cualquiera de estos comandos

Para conocer todas las opciones posibles y los niveles existentes, podemos consultar la Guía de referencia de Grupos de eventos DDL. También podemos consultar la vista del sistema sys.trigger_event_types

Aquí el script de creación de la tabla de registro y del trigger. Para este último, incluimos comentarios para explicar cada paso:

Tabla de registro:

Trigger:

En el trigger se ven claramente los siguientes pasos:

  1. Captura información del evento DDL con la función EVENTDATA.- Mediante la declaración de una variable de tipo XML capturamos la información del evento, asignándole el resultado de ejecutar la función EVENTDATA. El formato XML tiene la siguiente estructura:

  1. Consumo de la información capturada en el paso 1, usando XQuery.- En esta parte extraemos datos específicos del evento, leyedo la variable XML que contiene la información completa del evento
  2. Almacenamiento de la información en una tabla.-Con la información obtenida del evento más otros datos como la fecha y el usuario, se graba el cambio en la tabla de registro. En el último campo insertamos el XML completo para tenerlo como referencia

Vamos a ejecutar algunos comandos DDL de prueba para ver como se comporta el trigger:

Luego consultamos la tabla de registro:

Este es el resultado:


Como vemos, tenemos una tabla con el detalle del momento, usuario, acción, objeto y sentencia, por cada comando DDL ejecutado, conformando un registro detallado de los cambios relacionados a tablas. Recordemos que en la definición del trigger podemos definir que cambios quedemos rastrear

Un tema interesante que debemos mencionar es que los trigger se pueden desactivar y activar según se requiera, con comandos muy simples:

En el siguiente artículo veremos la implementación de un trigger similar pero para capturar eventos a nivel de servidor

Hasta la próxima

Deja un comentario

Tu dirección de correo electrónico no será publicada.