Auditoria usando Alertas no SQL Server 2000

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!