Auditoria PARTE I

Operação Lava Jato no banco de dados…Bom dia, boa tarde, boa noite!
Hoje vou falar de um assunto muito atual e interessante, e que tem tirado o sono de muita gente…
NÃO GENTE!!! Não vou falar de impeachment, corrupção, crise, etc. Chega! Na verdade eu não aguento mais ouvir tantos assuntos deprimentes. Sei que eles existem! Não podemos fechar os olhos, mas JAMAIS deveremos mergulhar na depressão que isto pode causar (fica a dica!)

Bem, falando nisso, você já sabe, o interesse deste espaço é justamente o contrário, compartilhar conhecimentos interessantes mas acima de tudo com muito bom humor, e num clima agradável.
Coloquei o texto inicial como Lava Jato no banco de dados, pois o assunto que começa hoje é justamente voltado para um processo muito importante que é a auditoria no banco de dados.
Criei uma série sobre auditoria e irei fazer diversos posts voltados a ela.

Todas as tecnologias de bancos de dados tem alguma forma de auditoria sobre o que acontece nos seus processos. Alguns de forma mais detalhada, com falamos, com uma granularidade maior, outros mais simples, mas o fato é que
sempre terá como auditar.

Mas o que é especificamente auditoria em banco de dados?
Auditar um banco de dados é saber todas as operações que estão sendo feitas, por determinados usuários em determinado momento, podendo registrar inclusive no nível de instruções select.
Porém antes de mais nada, como todos sabem que sou voltado muito para a área de performance, gostaria de fazer um parênteses aqui para comentar a respeito de um termo que coloquei acima:”granularidade”
Granularidade é o nível de detalhamento que você deseja ter no seu processo de auditoria. E porque eu citei performance em um post que fala de auditoria?
Simples, quando decidimos criar/instaurar um processo de auditoria no banco de dados, temos que ter em mente que isso poderá, e com certeza vai, impactar na performance do ambiente como um todo, poderá aumentar drasticamente a utilização dos espaços no banco e no Sistema Operacional, pois auditoria consiste em gerar arquivos de logs em sistema operacional, registros em tabelas de log, e isso é claro deve ser programado/planejado. Já tive diversos problemas de bancos de dados que congelaram por falta de espaço.

Mesmo assim, com risco calculado, muitas vezes se faz necessário para segurança e consistência dos dados que se tenha este tipo de controle.
Nem sempre o processo de auditoria tem como função punir alguém. Ela pode ser usada apenas para registrar, para descobrir qual aplicação está alterando determinada tabela.

Hoje vou comentar um tipo de auditoria muito interessante, que é genérica, podendo ser utilizada em diversos tipos de banco de dados, com pequenas adaptações.

Vou comentar hoje, nesta primeira parte a auditoria baseada em trigger.

Primeiramente o que é uma trigger?? Lembra dos filmes de faroeste? Num duelo ganhava aquele que era mais rápido na trigger. Ops… no gatilho.rsrsrs Lembra??

Isso aí! Trigger é um objeto de banco de dados que sempre estará associada a uma ação que a fará disparar.

No exemplo de hoje, vou compartilhar um processo de auditoria que fiz certa vez, não com a intenção de punir, achar culpados.
Eu tinha um problema: Descobrir qual aplicação estava fazendo alteração em uma determinada tabela e de onde vinha esta alteração(qual servidor de aplicação).
Nestes casos a melhor forma de se fazer isso é com auditoria baseada em valores, que utiliza-se das triggers para isso.

Coloquei abaixo o exemplo que criei e irei explicar passo a passo o que foi feito:

1- Primeiramente precisará criar uma tabela que irá guardar os dados da auditoria.
Nela teremos os dados antigos (antes da alteração) e os novos(pós alteração)

CREATE TABLE RF.LOG_RF_CONTAB_LIVRO_NF
( COD_IMOBILIZADO_OLD                     VARCHAR2(24),
  COD_IMOBILIZADO_NEW                     VARCHAR2(24),
  PROGRAMA                                                  VARCHAR2(50),
  TEXTSQL                                                       VARCHAR2(1000),
  USUARIO                                                       VARCHAR2(100),
  DATA                                                               DATE,
  OPERACAO                                                    VARCHAR2(1));

2- Criada a tabela precisaremos criar a trigger que será ativada quando algumas situações ocorrerem e que irá gravar os dados na tabela criada acima.
No caso da trigger abaixo eu coloquei que ela seja disparada após insert, delete ou update.

CREATE OR REPLACE TRIGGER RF.TRG_AUD_CONTAB_LIVRO
AFTER INSERT OR DELETE OR UPDATE ON RF.RF_CONTAB_LIVRO_NF
FOR EACH ROW
DECLARE
  Operacao  CHAR(1);
  Programa VARCHAR2(50);
  Usuario VARCHAR2(50);
  TEXTSQL varchar2(1000);
BEGIN
 
    SELECT sys_context(‘USERENV’, ‘SESSION_USER’), sys_context(‘USERENV’, ‘MODULE’) || ‘  ‘ || sys_context(‘USERENV’, ‘HOST’) , sys_context(‘USERENV’, ‘CURRENT_SQL’)
    INTO Usuario, Programa, TEXTSQL FROM dual;

–Estou utilizando um método com a função sys_context de extração de dados. Ela me traz dados referentes à conexão e o que ela está a fazer.

  IF INSERTING THEN
    Operacao := ‘I’;
  ELSIF UPDATING THEN
    Operacao := ‘U’;
  ELSE  
    Operacao := ‘D’;
  END IF;

  INSERT INTO RF.LOG_RF_CONTAB_LIVRO_NF
    (COD_IMOBILIZADO_OLD,COD_IMOBILIZADO_NEW,PROGRAMA,TEXTSQL,USUARIO,DATA,OPERACAO) VALUES
    (:OLD.COD_IMOBILIZADO,:NEW.COD_IMOBILIZADO, Programa, TEXTSQL,Usuario,SYSDATE, Operacao);

END;

Observação:
Quando eu coloco .OLD significa que irá trazer valores anteriores a mudança
Quando eu coloco .NEW significa que irá trazer valores após a mudança

Com a trigger acima criada e “atrelada” a tabela RF.RF_CONTAB_LIVRO_NF, qualquer alteração feita na tabela será logada na tabela de logs RF.LOG_RF_CONTAB_LIVRO_NF.
Assim fica fácil e com apenas um select conseguirá verificar as alterações feitas e quem as fez.

Assim conseguimos recuperar diversas informações e manter controle maior no banco de dados.

ALGUNS CUIDADOS: Como já falei acima, uma trigger como esta colocada em uma tabela que tem muitas operações deverá gerar um aumento rápido da tabela que está gravando os logs, no nosso caso a “RF.LOG_RF_CONTAB_LIVRO_NF”.
Sendo assim, se faz extremamente necessário que se faça um processo de limpeza da tabela. Este poderá ser até automatizado – Olha! Mais um assunto legal para futuro post.
Outra coisa a ser observada é que todo processo deve ser primeiramente testado em ambiente de NÃO-PRODUÇÃO antes para evitar problemas. Trigger com falha poderá parar um processo.

É isso que  eu tinha para hoje meu amigo! Espero que tenha gostado, que continue acompanhando pois teremos diversos outros posts sobre auditoria. Como você viu no início coloquei “PARTE I”

Grande abraço! Força e FOCO NA META!
Caso tenha alguma dúvida, dica, reclamação, sugestão, comenta aí!!!!