Cómo crear un trigger de auditoría para registrar la manipulación de objetos en SQL Server

Hola, chicos,
Buen día.

Introducción

Hoy vengo a traerles un recurso muy útil en la vida de los DBA de SQL Server, tanto para auditar cambios/creación/eliminación de objetos como para “versionar” SP’s, Funciones, etc, que es el uso de triggers para registrar todos los cambios a nivel DDL en objetos, incluyendo poder registrar la consulta utilizada para ello. Esto es especialmente útil en el caso de objetos de programación, como funciones y procedimientos almacenados, donde puede ver cada cambio realizado en estos objetos.

Como punto adicional, agregué un mensaje de alerta para cuando alguien crea un objeto en una base de datos del sistema (modelo, master y msdb). Esta alerta se puede incrementar para enviar un correo electrónico al equipo de la base de datos además de notificar visualmente al usuario que creó el objeto, entre otros.

Implementación para una base de datos específica.

Ver código fuente

IF ((SELECT COUNT(*) FROM sys.triggers WHERE name = 'trgAlteracao_Objetos') > 0) DROP TRIGGER [trgAlteracao_Objetos] ON DATABASE
GO

CREATE TRIGGER [trgAlteracao_Objetos]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN


    SET NOCOUNT ON

    
    DECLARE 
        @Evento XML, 
        @Mensagem VARCHAR(MAX),

        @Dt_Evento DATETIME,
        @Ds_Tipo_Evento VARCHAR(30),
        @Ds_Database VARCHAR(50),
        @Ds_Usuario VARCHAR(100),
        @Ds_Schema VARCHAR(20),
        @Ds_Objeto VARCHAR(100),
        @Ds_Tipo_Objeto VARCHAR(20),
        @Ds_Query VARCHAR(MAX)


    SET @Evento = EVENTDATA()

    SELECT 
        @Dt_Evento = @Evento.value('(/EVENT_INSTANCE/PostTime/text())[1]','datetime'),
        @Ds_Tipo_Evento = @Evento.value('(/EVENT_INSTANCE/EventType/text())[1]','varchar(30)'),
        @Ds_Database = @Evento.value('(/EVENT_INSTANCE/DatabaseName/text())[1]','varchar(50)'),
        @Ds_Usuario = @Evento.value('(/EVENT_INSTANCE/LoginName/text())[1]','varchar(100)'),
        @Ds_Schema = @Evento.value('(/EVENT_INSTANCE/SchemaName/text())[1]','varchar(20)'),
        @Ds_Objeto = @Evento.value('(/EVENT_INSTANCE/ObjectName/text())[1]','varchar(100)'),
        @Ds_Tipo_Objeto = @Evento.value('(/EVENT_INSTANCE/ObjectType/text())[1]','varchar(20)'),
        @Ds_Query = @Evento.value('(/EVENT_INSTANCE/TSQLCommand/CommandText/text())[1]','varchar(max)')

    
    IF (@Ds_Database IN ('master', 'model', 'msdb'))
    BEGIN
    
        IF (LEFT(@Ds_Tipo_Evento, 6) = 'CREATE')
        BEGIN

            SET @Mensagem = 'Você (' + @Ds_Usuario + ') acabou de criar um objeto na database de sistema ' + @Ds_Database + ' e essa operação foi logada. 

Favor excluir e criar na database correta. 

A equipe de Banco de Dados agradece a sua colaboração.'

            
            PRINT @Mensagem


        END
        ELSE BEGIN

            IF (LEFT(@Ds_Tipo_Evento, 5) = 'ALTER')
            BEGIN

                SET @Mensagem = 'Você (' + @Ds_Usuario + ') acabou de modificar um objeto na database de sistema ' + @Ds_Database + ' e essa operação foi logada. 

Favor criar o objeto na database correta. 

A equipe de Banco de Dados agradece a sua colaboração.'

    
                PRINT @Mensagem

            END

        END
        
    END
    

    IF (OBJECT_ID('Auditoria.dbo.Alteracao_Objetos') IS NULL)
    BEGIN
    
        -- DROP TABLE Auditoria.dbo.Alteracao_Objetos
        CREATE TABLE Auditoria.dbo.Alteracao_Objetos (
            Id_Auditoria INT IDENTITY(1,1),
            Dt_Evento DATETIME,
            Ds_Tipo_Evento VARCHAR(30),
            Ds_Database VARCHAR(50),
            Ds_Usuario VARCHAR(100),
            Ds_Schema VARCHAR(20),
            Ds_Objeto VARCHAR(100),
            Ds_Tipo_Objeto VARCHAR(20),
            Ds_Query XML
        )
        
        CREATE CLUSTERED INDEX SK01 ON Auditoria.dbo.Alteracao_Objetos(Id_Auditoria)

    END


    INSERT INTO Auditoria.dbo.Alteracao_Objetos
    SELECT 
        @Dt_Evento,
        @Ds_Tipo_Evento,
        @Ds_Database,
        @Ds_Usuario,
        @Ds_Schema,
        @Ds_Objeto,
        @Ds_Tipo_Objeto,
        @Evento


END
GO

ENABLE TRIGGER [trgAlteracao_Objetos] ON DATABASE
GO

Para que todos los usuarios tengan acceso a escribir en esta tabla, será necesario ejecutar los siguientes comandos:

USE [Auditoria]
GO
GRANT CONNECT TO [guest]
GRANT INSERT ON dbo.Alteracao_Objetos TO PUBLIC

Otra observación en este código es que este disparador escribe datos en algunas tablas de la base de datos, es decir, el usuario que va a cambiar cualquier objeto en la base de datos necesitará permisos para escribir los datos en esta tabla. Por este motivo, agregué el comando de concesión a la tabla para el rol público.

Otra forma de evitar esto es usar la cláusula EXECUTE AS ‘login_com_permissao’, de modo que el disparador se ejecutará con el permiso de ese usuario EXECUTE AS, pero registrará los datos del usuario real que está contando, evitando la necesidad de crear todos los usuarios en la base de datos y liberar los permisos, quedando así:

CREATE TRIGGER [trgAlteracao_Objetos]
ON DATABASE
WITH EXECUTE AS 'dirceu.resende'
FOR DDL_DATABASE_LEVEL_EVENTS
[...]

Implementación para todas las bases de datos.

La solución presentada anteriormente debe aplicarse a cada base de datos de su servidor en la que desee registrar operaciones DDL. En el fragmento de código a continuación, demostraré cómo aplicar este activador a todas las bases de datos del servidor (activador del servidor).

Ver código fuente

IF ((SELECT COUNT(*) FROM sys.server_triggers WHERE name = 'trgAlteracao_Objetos') > 0) DROP TRIGGER [trgAlteracao_Objetos] ON ALL SERVER
GO

CREATE TRIGGER [trgAlteracao_Objetos]
ON ALL SERVER
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
 
 
    SET NOCOUNT ON
 
 
    DECLARE 
        @Evento XML, 
        @Mensagem VARCHAR(MAX),
 
        @Dt_Evento DATETIME,
        @Ds_Tipo_Evento VARCHAR(30),
        @Ds_Database VARCHAR(50),
        @Ds_Usuario VARCHAR(100),
        @Ds_Schema VARCHAR(20),
        @Ds_Objeto VARCHAR(100),
        @Ds_Tipo_Objeto VARCHAR(20),
        @Ds_Query VARCHAR(MAX)
 
 
    SET @Evento = EVENTDATA()
 
    SELECT 
        @Dt_Evento = @Evento.value('(/EVENT_INSTANCE/PostTime/text())[1]','datetime'),
        @Ds_Tipo_Evento = @Evento.value('(/EVENT_INSTANCE/EventType/text())[1]','varchar(30)'),
        @Ds_Database = @Evento.value('(/EVENT_INSTANCE/DatabaseName/text())[1]','varchar(50)'),
        @Ds_Usuario = @Evento.value('(/EVENT_INSTANCE/LoginName/text())[1]','varchar(100)'),
        @Ds_Schema = @Evento.value('(/EVENT_INSTANCE/SchemaName/text())[1]','varchar(20)'),
        @Ds_Objeto = @Evento.value('(/EVENT_INSTANCE/ObjectName/text())[1]','varchar(100)'),
        @Ds_Tipo_Objeto = @Evento.value('(/EVENT_INSTANCE/ObjectType/text())[1]','varchar(20)'),
        @Ds_Query = @Evento.value('(/EVENT_INSTANCE/TSQLCommand/CommandText/text())[1]','varchar(max)')
 
 
    IF (@Ds_Database IN ('master', 'model', 'msdb'))
    BEGIN
 
        IF (LEFT(@Ds_Tipo_Evento, 6) = 'CREATE')
        BEGIN
 
            SET @Mensagem = 'Você (' + @Ds_Usuario + ') acabou de criar um objeto na database de sistema ' + @Ds_Database + ' e essa operação foi logada. 
 
Favor excluir e criar na database correta. 
 
A equipe de Banco de Dados agradece a sua colaboração.'
 
 
            PRINT @Mensagem
 
 
        END
        ELSE BEGIN
 
            IF (LEFT(@Ds_Tipo_Evento, 5) = 'ALTER')
            BEGIN
 
                SET @Mensagem = 'Você (' + @Ds_Usuario + ') acabou de modificar um objeto na database de sistema ' + @Ds_Database + ' e essa operação foi logada. 
 
Favor criar o objeto na database correta. 
 
A equipe de Banco de Dados agradece a sua colaboração.'
 
 
                PRINT @Mensagem
 
            END
 
        END
 
    END
 
 
    IF (OBJECT_ID('Auditoria.dbo.Alteracao_Objetos') IS NULL)
    BEGIN
 
        -- DROP TABLE Auditoria.dbo.Alteracao_Objetos
        CREATE TABLE Auditoria.dbo.Alteracao_Objetos (
            Id_Auditoria INT IDENTITY(1,1),
            Dt_Evento DATETIME,
            Ds_Tipo_Evento VARCHAR(30),
            Ds_Database VARCHAR(50),
            Ds_Usuario VARCHAR(100),
            Ds_Schema VARCHAR(20),
            Ds_Objeto VARCHAR(100),
            Ds_Tipo_Objeto VARCHAR(20),
            Ds_Query XML
        )
        
        CREATE CLUSTERED INDEX SK01 ON Auditoria.dbo.Alteracao_Objetos(Id_Auditoria)
 
    END
 
 
    IF (@Ds_Database NOT IN ('tempdb'))
    BEGIN
 
        INSERT INTO Auditoria.dbo.Alteracao_Objetos
        SELECT 
            @Dt_Evento,
            @Ds_Tipo_Evento,
            @Ds_Database,
            @Ds_Usuario,
            @Ds_Schema,
            @Ds_Objeto,
            @Ds_Tipo_Objeto,
            @Evento
            
    END
 
 
END
GO
 
ENABLE TRIGGER [trgAlteracao_Objetos] ON ALL SERVER
GO

Al igual que con la base de datos Trigger, para que todos los usuarios tengan acceso a escribir en esta tabla, será necesario ejecutar los siguientes comandos:

USE [Auditoria]
GO
GRANT CONNECT TO [guest]
GRANT INSERT ON dbo.Alteracao_Objetos TO PUBLIC

Otra observación en este código es que este disparador escribe datos en algunas tablas de la base de datos, es decir, el usuario que va a cambiar objetos en la base de datos necesitará permisos para escribir datos en esta tabla, además de tener el usuario creado en la base de datos de esa tabla. Por este motivo, agregué el comando de concesión a la tabla para el rol público.

CREATE TRIGGER [trgAlteracao_Objetos]
ON ALL SERVER
WITH EXECUTE AS 'dirceu.resende'
FOR DDL_DATABASE_LEVEL_EVENTS
[...]

Resultados

Tabla de auditoría creada

Mensaje de alerta al crear objetos en bases de datos del sistema.

Dirceu Resende

Arquitecto de Bases de Datos y BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.

WhatsApp Telegram Ver mis certificaciones

Comentários (0)

Carregando comentários…