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:

CREATE TABLE dbo.LogCambiosDDL (
Nivel char(1) NOT NULL,
Fecha datetime NOT NULL CONSTRAINT DF_LogCambiosDDL_FechaCambio DEFAULT (GETDATE()),
InicioSesion varchar(500) NOT NULL,
Usuario varchar(500) NOT NULL,
Accion varchar(500) NOT NULL,
Objeto varchar(500),
Sentencia varchar(max) NOT NULL,
DetalleEvento xml NOT NULL);
GO

CREATE TABLE dbo.LogCambiosDDL (

Nivel char(1) NOT NULL,

Fecha datetime NOT NULL CONSTRAINT DF_LogCambiosDDL_FechaCambio DEFAULT (GETDATE()),

InicioSesion varchar(500) NOT NULL,

Usuario varchar(500) NOT NULL,

Accion varchar(500) NOT NULL,

Objeto varchar(500),

Sentencia varchar(max) NOT NULL,

DetalleEvento xml NOT NULL);

Trigger:

CREATE TRIGGER trDB_AuditaCambios ON DATABASE 
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE --<-- También se puede usar DDL_TABLE_EVENTS
  AS 
BEGIN
    SET NOCOUNT ON;
	
    --Declaración de variables de proceso
    DECLARE @InfoEvento XML,@Accion varchar(500),@Objeto varchar(500),@Sentencia varchar(max);

    --1.Captura de evento en variable principal
    SET @InfoEvento = EVENTDATA();
    
    --2.Consumir información en variables secundarias usando XQuery
	----Captura la acción DDL (si es CREATE, ALTER o DROP)
    SET @Accion = @InfoEvento.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(500)'); 
    ----Captura el nombre objeto afectado, anteponiendo el nombre del esquema
    SET @Objeto = @InfoEvento.value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(250)') 
                   +'.'
                   +@InfoEvento.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(250)');
    ---Captura la sentencia ejecutada
    SET @Sentencia=@InfoEvento.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')

    --3. Almacenar detalles de la acción en tabla de log
    INSERT dbo.LogCambiosDDL(Nivel,Fecha,InicioSesion,Usuario,Accion,Objeto,Sentencia,DetalleEvento) 
    VALUES (
        'B', --<-- Indica que el evento es de tipo Base de datos
        GETDATE(), 
        SUSER_NAME(),
        CURRENT_USER, 
        @Accion, 
        @Objeto, 
        @Sentencia, 
        @InfoEvento
        );
END;
GO

CREATE TRIGGER trDB_AuditaCambios ON DATABASE

FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE --<-- También se puede usar DDL_TABLE_EVENTS

BEGIN

SET NOCOUNT ON;

--Declaración de variables de proceso

DECLARE @InfoEvento XML,@Accion varchar(500),@Objeto varchar(500),@Sentencia varchar(max);

--1.Captura de evento en variable principal

SET @InfoEvento = EVENTDATA();

--2.Consumir información en variables secundarias usando XQuery

----Captura la acción DDL (si es CREATE, ALTER o DROP)

SET @Accion = @InfoEvento.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(500)');

----Captura el nombre objeto afectado, anteponiendo el nombre del esquema

SET @Objeto = @InfoEvento.value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(250)')

+'.'

+@InfoEvento.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(250)');

---Captura la sentencia ejecutada

SET @Sentencia=@InfoEvento.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')

--3. Almacenar detalles de la acción en tabla de log

INSERT dbo.LogCambiosDDL(Nivel,Fecha,InicioSesion,Usuario,Accion,Objeto,Sentencia,DetalleEvento)

VALUES (

'B', --<-- Indica que el evento es de tipo Base de datos

GETDATE(),

SUSER_NAME(),

CURRENT_USER,

@Accion,

@Objeto,

@Sentencia,

@InfoEvento

);

END;

En el trigger se ven claramente los siguientes pasos:

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:

<EVENT_INSTANCE>
  <EventType>CREATE_TABLE</EventType>
  <PostTime>2017-03-02T16:30:12.727</PostTime>
  <SPID>56</SPID>
  <ServerName>PERMS16\FIWCORP</ServerName>
  <LoginName>PER\A01dblerner</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>AdventureWorks</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>TablaTes1</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>CREATE TABLE dbo.TablaTes1 (C1 int, c2 date)</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

<EVENT_INSTANCE>

<EventType>CREATE_TABLE</EventType>

<ServerName>PERMS16\FIWCORP</ServerName>

<LoginName>PER\A01dblerner</LoginName>

<DatabaseName>AdventureWorks</DatabaseName>

<ObjectName>TablaTes1</ObjectName>

<ObjectType>TABLE</ObjectType>

<CommandText>CREATE TABLE dbo.TablaTes1 (C1 int, c2 date)</CommandText>

</TSQLCommand>

</EVENT_INSTANCE>

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
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:

--Creamos una tabla de prueba
CREATE TABLE dbo.TablaTes1 (C1 int, c2 date);

--Modificamos la tabla de prueba
ALTER TABLE dbo.TablaTes1
ADD c3 bit;

--Eliminamos la tabla de prueba
DROP TABLE dbo.TablaTes1;
GO

--Creamos una tabla de prueba

CREATE TABLE dbo.TablaTes1 (C1 int, c2 date);

--Modificamos la tabla de prueba

ALTER TABLE dbo.TablaTes1

ADD c3 bit;

--Eliminamos la tabla de prueba

DROP TABLE dbo.TablaTes1;

Luego consultamos la tabla de registro:

SELECT * FROM LogCambiosDDL;
GO

1 2	SELECT * FROM LogCambiosDDL; GO

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:

--Deshabilitar el trigger
DISABLE TRIGGER trDB_AuditaCambios ON DATABASE 
GO

--Habilitar el trigger
ENABLE TRIGGER trDB_AuditaCambios ON DATABASE 
GO

--Deshabilitar el trigger

DISABLE TRIGGER trDB_AuditaCambios ON DATABASE

--Habilitar el trigger

ENABLE TRIGGER trDB_AuditaCambios ON DATABASE

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

Comparte Este artículo:

Deja una respuesta Cancelar la respuesta