Hoje mostraremos uma estratégia de auditoria de dados envolvendo a criação de triggers, mensagens de erro definidas pelo usuário, alertas e operadores no SQL Server 2000.
Necessidade de auditar ações do usuário
Há situações no dia a dia de um DBA em que é necessário auditar as atividades realizadas no banco de dados como por exemplo: o quê foi alterado, quando foi alterado, por quem foi alterado, etc. Uma estratégia interessante é a criação de um alerta que monitore a ocorrência de um dado evento, definido pelo usuário como erro, a ser disparado por um trigger.
Nossa estratégia será montada sobre os seguintes passos:
- Criação de uma tabela de log que conterá informações sobre as ações realizadas;
- Criação da mensagem de erro definida pelo usuário;
- Criação do trigger para disparar a mensagem de erro. O trigger deve ser definido na tabela de usuário que se quer monitorar;
- Criação do operador que receberá a mensagem;
- Criação do alerta para monitorar a mensagem de erro definida no passo 2. O alerta usará o serviço mensageiro do Windows para entregar a mensagem.
Sendo assim, segue abaixo o desenho da nossa estratégia:
Resumindo:
- O usuário realiza a ação inserindo, alterando ou excluindo dados de uma determinada tabela (devem ser definidas quais as tabelas que serão monitoradas).
- O trigger da ação em questão, insert, update ou delete, gera uma entrada na tabela de log além de disparar a mensagem de erro que será capturada pelo alerta.
- O alerta verifica que a mensagem de erro definida pelo usuário chegou no log de eventos do windows e dispara a mensagem de alerta para o operador definido anteriormente.
Criando a estratégia
Vamos iniciar criando a tabela tb_tabela, que será monitorada, e a tabela de log, tb_log. Seguem os scripts:
--Tabela TB_TABELA que será monitorada
Create table tb_tabela (codigo integer identity,
nomeempresa varchar(100),
nomecontato varchar(100))
--Tabela TB_LOG que registrará as atividades ocorridas
Create table tb_log ( codigo integer identity,
usuario varchar(100),
data datetime,
acao varchar(100))
Em seguida, adicionaremos a seguinte mensagem de erro definida pelo usuário:
--Tabela TB_TABELA que será monitorada
sp_addmessage '90001',16,'O usuário %s inseriu o contato %s! Verifique a tb_log para maiores informações.','us_english','true','replace'
go
O código do trigger abaixo irá monitorar uma ação de insert na tabela tb_tabela.
create trigger tg_ins_tabela
on tb_tabela for insert
as
declare @lvc_usuario varchar(100),
@ldt_data_evento datetime,
@lvc_nome_contato varchar(100)
set @lvc_usuario = suser_sname()
set @ldt_data_evento = getdate()
select @lvc_nome_contato = nomecontato from inserted
--Registrando o evento na tb_log
insert into tb_log(usuario,data,acao)
values (@lvc_usuario,@ldt_data_evento,'Inserção na tb_tabela')
--Disparando a mensagem de erro que será registrada no log de eventos do Windows
raiserror (90001,16,1,@lvc_usuario,@lvc_nome_contato)
go
Após a criação das tabelas, da mensagem de erro e do trigger criaremos o operador, que receberá a mensagem de alerta e por último o alerta.
Para criar o operador, vá até o Enterprise Manager, expanda a pasta Management e entre na opção Operators. Clique com o botão direito do mouse e escolha a opção New Operator. Informe o nome do operador e indique o tipo de notificação NET SEND ADDRESS. Para simplificar o exemplo usaremos a notificação via NET SEND, que usa o serviço de mensageiro do Windows. Certifique-se de que esse serviço esteja iniciado. As figuras 1.0 e 2.0 mostram o procedimento descrito anteriormente.
Para finalizarmos falta a criação do alerta. Ainda dentro da opção Management, clique em Alerts e com o botão direito do mouse escolha a opção New Alert. Na guia General, informe o nome do alerta, indique para qual mensagem de erro o alerta irá monitorar e o banco de dados à qual a mensagem se refere. Na guia Response, defina qual o operador receberá a mensagem do alerta. As figuras 3.0 e 4.0 mostram os passos.
Para testar a estratégia de auditoria, basta executar um insert na tabela tb_tabela. O resultado é mostrado na figura 5.0.
--Inserindo dados na tb_tabela que disparará trigger
insert into tb_tabela (nomeempresa,nomecontato)
values ('DevMedia','Reydeval')
Conclusão
Apresentamos uma estratégia de auditoria muito útil baseada em mensagens de erro definidas pelo usuário, triggers, alertas e operadores. Espero que usem essa dica no dia a dia. Evitará dor de cabeça para o DBA além de servir como uma ótima opção de log de atividades. Até o próximo artigo!