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

Post Views 5,610 views

Neste artigo

Esse post é a parte 3 de 21 da série Security and Auditing

Reading time 4 minutes

Olá pessoal,
Bom dia.

Introduction

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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

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

CREATE TRIGGER [trgAlteracao_Objetos]

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

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

IF (OBJECT_ID('Auditoria.dbo.Alteracao_Objetos') IS NULL)

BEGIN

-- DROP TABLE Auditoria.dbo.Alteracao_Objetos

CREATE TABLE Auditing.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 Auditing.dbo.Alteracao_Objetos(Id_Auditoria)

END

INSERT INTO Auditing.dbo.Alteracao_Objetos

SELECT

@Dt_Evento,

@Ds_Tipo_Evento,

@Ds_Database,

@Ds_Usuario,

@Ds_Schema,

@Ds_Objeto,

@Ds_Tipo_Objeto,

@Evento

END

ENABLE TRIGGER [trgAlteracao_Objetos] ON DATABASE

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

USE [Auditing]

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
[...]

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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

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

CREATE TRIGGER [trgAlteracao_Objetos]

ON ALL SERVER