Como criar uma trigger de Auditoria para logar a manipulação de objetos no SQL Server

Olá pessoal,
Bom dia.

Introdução

Hoje venho trazer pra vocês um recurso muito útil na vida dos DBA’s SQL Server, tanto pra auditoria de alteração/criação/exclusão de objetos quanto para um “versionamento” de SP’s, Functions, etc, que é a utilização de triggers para logar todas as alterações a nível de DDL em objetos, inclusive sendo possível gravar a query utilizada para tal. Isso é especialmente útil no caso de objetos de programação, como Stored Procedures e Functions, onde pode-se visualizar cada alteração realizada nesses objetos.

Como um ponto adicional, adicionei uma mensagem de alerta para quando alguém criar um objeto em um database de sistema (model, master e msdb). Esse alerta pode ser incrementado para enviar um e-mail para a equipe de banco de dados além de notificar visualmente o usuário que criou o objeto, entre outros.

Implementação para um database específico

Visualizar código-fonte

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 os usuários tenham acesso para gravar nessa tabela, será necessário executar os comandos abaixo:

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

Uma outra observação nesse código, é que essa trigger grava dados em algumas tabelas no banco, ou seja, o usuário que for alterar qualquer objeto no banco precisará de permissões para gravar os dados nessa tabela. Por este motivo, adicionei o comando de grant na tabela para a role public.

Uma outra forma de contornar isso, é utilizar a cláusula EXECUTE AS ‘login_com_permissao’, de modo que a trigger será executada com a permissão desse usuário do EXECUTE AS, mas vai gravar os dados do usuário real que está se contando, evitando a necessidade de ter que criar todos os usuários no database e liberar as permissões, ficando desta forma:

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

Implementação para todos os databases

A solução apresentada acima, deve ser aplicada em cada database do seu servidor em que você deseja logar as operações de DDL. No trecho de código abaixo, vou demonstrar como aplicar essa trigger em todos os databases do servidor (Server trigger).

Visualizar código-fonte

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

Assim como no database Trigger, para que todos os usuários tenham acesso para gravar nessa tabela, será necessário executar os comandos abaixo:

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

Uma outra observação nesse código, é que essa trigger grava dados em algumas tabelas no banco, ou seja, o usuário que for alterar objetos no banco precisará de permissões para gravar os dados nessa tabela, além de ter o usuário criado no database dessa tabela. Por este motivo, adicionei o comando de grant na tabela para a role public.

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

Resultados

Tabela de auditoria criada

Mensagem de alerta quando criam objetos em databases de sistema

Dirceu Resende

Arquiteto de Banco de Dados e BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.

WhatsApp Telegram Veja minhas certificações

Comentários (0)

Carregando comentários…