Trilha de auditoria no SQL Server
Uma forma de monitorar os seus dados
por Darci Leandro
Olá pessoal,
Iremos abordar neste artigo uma maneira simples, porém, bem eficiente de fazermos uma trilha de auditoria em nossas principais tabelas.
Muitas vezes temos tabelas importantes nas quais queremos saber realmente quem foi o usuário que incluiu, alterou ou excluiu os registros.
Para isso iremos trabalhar com o objeto Trigger.
1.Ambiente
a.Table
Em nosso ambiente teremos duas tabelas (figura 1):
- tb_funcionario: irá armazenar os dados de todos os funcionários que serão cadastrados;
- tb_auditoria: irá armazenar os dados de qual registro foi incluído, alterado ou excluído, além disso, qual usuário do seu sistema que fez essa atividade e de que máquina e horário veio essa transação.
Figura 1: Tabelas que serão utilizadas neste artigo
b.Trigger
Algumas definições:
- É um objeto e está atrelado a uma tabela específica;
- Poderá ser do tipo Inclusão, Alteração ou Exclusão;
- É acionado automaticamente à medida que ocorrer uma das ações de inclusão, alteração ou exclusão na tabela na qual ele está associado;
- Utilizado para realizar operações de regras de negócio.
2.Auditoria
Agora você verá um passo a passo de como criar uma trilha de auditoria. Sugiro a todos que executem aos poucos, seguindo realmente esse guia e procurando entender não só o comando, mas, principalmente o seu efeito.
Para uma melhor visualização do resultado seria ideal executar os comandos DML em estações diferentes conectadas com usuários diferentes.
Passo 1
Criando a tabela tb_funcionario:
create table tb_funcionario
(
cd_funcsmallintnot null,
nm_funcvarchar(80) not null,
ds_cargovarchar(30) not null,
ds_deptovarchar(30) not null,
vl_salarionumeric(12,2) not null
)
Criando a tabela tb_auditoria:
create table tb_auditoria
(
nm_usuariovarchar(100) not null,
nm_computadorvarchar(100) not null,
dt_historicodatetimenot null,
ds_acaovarchar(10) not null,
cd_funcsmallintnot null,
vl_salarionumeric(12,2) not null
)
Passo 2
Criando trigger de “inclusão” para tabela tb_funcionario:
create trigger tr_Inclusao
on tb_funcionario--On : Nome da Tabela
for insert--For : Nome da Ação
as
insert tb_auditoria
select system_user,
host_name(),
getdate(),
'Insert',
cd_func,
vl_salario
from inserted--System table INSERTED
Passo 3
Incluindo um registro na tabela tb_funcionario:
insert into tb_funcionario VALUES
(1, 'Renata Gomes', 'Analista de Sistemas', 'Informatica', 3500.00)
Visualizando o conteúdo das tabelas:
select * from tb_funcionario
select * from tb_auditoria
Passo 4
Incluindo mais registros na tabela tb_funcionario:
insert into tb_funcionario VALUES
(2, 'Sonia Araujo', 'Vendedora', 'Comencial', 1500.00)
insert into tb_funcionario VALUES
(3, 'Claudia Mendes', 'Secretária', 'Diretoria', 1800.00)
insert into tb_funcionario VALUES
(4, 'Juliana Santos', 'Programadora', 'Informatica', 2500.00)
insert into tb_funcionario VALUES
(5, 'Branca Alves', 'DBA', 'Informatica', 4000.00)
insert into tb_funcionario VALUES
(6, 'Patrícia Garcia', 'Gerente Financeira', 'Financeiro', 6500.00)
insert into tb_funcionario VALUES
(7, 'Daniele Oliveira', 'Diretora', 'Diretoria', 12350.00)
insert into tb_funcionario VALUES
(8, 'Leila Lima', 'Assistente Financeira', 'Financeiro', 1750.00)
insert into tb_funcionario VALUES
(9, 'Natália Fernandes', 'Auxiliar de RH', 'Recursos Humano', 3500.00)
insert into tb_funcionario VALUES
(10, 'Joana Perez', 'Copeira', 'Copa', 3500.00)
Visualizando o conteúdo das tabelas:
select * from tb_funcionario
select * from tb_auditoria
Passo 5
Criando trigger de “alteração” para tabela tb_funcionario
create trigger tr_Alteracao
on tb_funcionario--On : Nome da Tabela
for update--For : Nome da Ação
as
insert tb_auditoria
select system_user,
host_name(),
getdate(),
'Update',
cd_func,
vl_salario
from inserted--system table INSERTED
Visualizando registro antes de alterar
select * from tb_funcionario
where cd_func = 10
Alterando registro
update tb_funcionario
set vl_salario = 5000
where cd_func = 10
Visualizando o conteúdo das tabelas:
select * from tb_funcionario
where cd_func = 10
select * from tb_auditoria
where cd_func = 10
order by dt_historico
Passo 6
Criando trigger de “exclusão” para tabela tb_funconario:
create trigger tr_Exclusao
on tb_funcionario--On : Nome da Tabela
for delete--For : Nome da Ação
as
insert tb_auditoria
select system_user,
host_name(),
getdate(),
'Delete',
cd_func,
vl_salario
from deleted--system table DELETED
Visualizando registro antes de excluir
select * from tb_funcionario
where cd_func = 10
Excluindo registro
delete from tb_funcionario
where cd_func = 10
Visualizando o conteúdo das tabelas:
select * from tb_funcionario
where cd_func = 10
select * from tb_auditoria
where cd_func = 10
order by dt_historico
Conclusão
Após esse passo a passo você poderá implementar em seu sistema uma trilha de auditoria e melhorar a segurança das informações.
O trigger é um objeto muito eficiente, porém, assim como os índices, se utilizados de forma exagerada poderá trazer problemas de performance.
Boa sorte a todos e até a próxima...
