Artigos Banco de Dados 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.

 


23-01-07pic01.JPG 

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_func                 smallint         not null,

nm_func       varchar(80)   not null,

ds_cargo       varchar(30)   not null,

ds_depto      varchar(30)   not null,

vl_salario      numeric(12,2) not null

)

 

Criando a tabela tb_auditoria:

 

create table tb_auditoria

(

nm_usuario             varchar(100)  not null,

nm_computador       varchar(100)  not null,

dt_historico            datetime       not null,

ds_acao                 varchar(10)   not null,

cd_func                           smallint         not null,

vl_salario                numeric(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...