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:

Triggers no SQL Server

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!

Auditoria usando Alertas no SQL Server 2000

Veja neste artigo uma auditoria de dados envolvendo a criação de triggers.

Necessidade de auditar aç�es do usu�rio

Criando a estrat�gia

Conclus�o

Artigos relacionados