Trilha de auditoria no SQL Server

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...