Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

How to create an Audit trigger to log object manipulation in SQL Server

Post Views 5,362 views
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

Para que todos os usuários tenham acesso para gravar nessa tabela, será necessário executar os comandos abaixo:

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:

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

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

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.

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:

Resultados

Tabela de auditoria criada
trgAlteracao_Objetos_Final

Mensagem de alerta quando criam objetos em databases de sistema
trgAlteracao_Objeto2