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

Visualizações: 5.610 views

Neste artigo

Esse post é a parte 3 de 21 da série Segurança e Auditoria

Auditoria no SQL Server (Server Audit)
Como criar uma auditoria para monitorar a criação, modificação e exclusão de Jobs no SQL Server
Como criar uma trigger de Auditoria para logar a manipulação de objetos no SQL Server
SQL Server – Como implementar auditoria e controle de logins (Trigger de Logon)
Monitorando operações de DDL e DCL utilizando a fn_trace_gettable do SQL Server
Utilizando o trace padrão do SQL Server para auditar eventos (fn_trace_gettable)
SQL Server – Trigger de auditoria de permissões e privilégios a nível de database e instância (GRANT e REVOKE)
SQL Server – Como monitorar e auditar alterações de dados em tabelas utilizando Change Data Capture (CDC)
SQL Server 2016 – Como “viajar no tempo” utilizando o recurso Temporal Tables
SQL Server – Como utilizar auditoria para mapear permissões necessárias reais em um usuário
SQL Server – Trigger para prevenir e impedir alterações em tabelas
SQL Server – Como criar um histórico de alterações de dados para suas tabelas (logs para auditoria)
SQL Server – Como evitar ataques de força bruta no seu banco de dados
SQL Server – Checklist de Segurança – Uma SP com mais de 70 itens de segurança para validar seu banco de dados
SQL Server – Como saber a data do último login de um usuário
SQL Server – Como evitar e se proteger de ataques de Ransomware, como WannaCry, no seu servidor de banco de dados
SQL Server – Cuidado com a server role securityadmin! Utilizando elevação de privilégios para virar sysadmin
SQL Server – Como evitar SQL Injection? Pare de utilizar Query Dinâmica como EXEC(@Query). Agora.
SQL Server – Entendendo os riscos da propriedade TRUSTWORTHY habilitada em um database
SQL Server – Políticas de Senhas, Expiração de Senha, Troca de Senha Obrigatória e Bloqueio de Login após N tentativas
SQL Server – Como criar uma auditoria de logins utilizando os logs da instância

Tempo de Leitura: 4 minutos

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

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

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

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