Auditoria de Banco de Dados [FireBird 2.5+]

[Roberto Henrique Borges Machado]

Olá! Recentemente houve a necessidade por parte de meus clientes de uma forma mais detalhada de auditoria de inserções, atualizações e deleções de informações.

Depois de muita dedicação consegui uma estrutura de fácil manuseio para aqueles que utilizam o FireBird da versão 2.5 em diante.

A auditoria é feita em uma database externa, dessa forma o banco de dados continua independente e não sofre uma expansão referente a esses dados.

Fato importante é que o banco da empresa não é afetado com possíveis exceções provenientes do banco de auditoria, ou seja, caso algo dê errado externamente o fluxo de dados na empresa não é afetado.

Segue procedimento:

1) Criar banco de dados para auditoria (LOGGERAL.FDB).
2) Criação das tabelas de auditoria, etc:

CREATE GENERATOR GEN_LOGOPERACAO_ID;

CREATE TABLE LOGOPERACAO (
    CD_LOGOPERACAO  BIGINT NOT NULL,
    DS_TABELA       VARCHAR(31) NOT NULL,
    FG_OPERACAO     CHAR(1) NOT NULL,
    CD_USUARIO      VARCHAR(10) NOT NULL,
    DT_OPERACAO     TIMESTAMP NOT NULL,
    DS_CHAVE        VARCHAR(255) NOT NULL
);

ALTER TABLE LOGOPERACAO ADD CONSTRAINT PK_LOGOPERACAO PRIMARY KEY (CD_LOGOPERACAO);

SET TERM ^ ;

/* Trigger: LOGOPERACAO_BI */
CREATE OR ALTER TRIGGER LOGOPERACAO_BI FOR LOGOPERACAO
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.cd_logoperacao is null) then
    new.cd_logoperacao = gen_id(gen_logoperacao_id,1);
end
^

SET TERM ; ^

CREATE GENERATOR GEN_LOGDATA_ID;

CREATE TABLE LOGDATA (
    CD_LOGDATA      BIGINT NOT NULL,
    CD_LOGOPERACAO  BIGINT NOT NULL,
    DS_CAMPO        VARCHAR(31) NOT NULL,
    DS_OLD          VARCHAR(4000),
    DS_NEW          VARCHAR(4000)
);

ALTER TABLE LOGDATA ADD CONSTRAINT PK_LOGDATA PRIMARY KEY (CD_LOGDATA);

ALTER TABLE LOGDATA ADD CONSTRAINT FK_LOGDATA_LOGOPERACAO FOREIGN KEY (CD_LOGOPERACAO) REFERENCES LOGOPERACAO (CD_LOGOPERACAO);

SET TERM ^ ;

/* Trigger: LOGDATA_BI */
CREATE OR ALTER TRIGGER LOGDATA_BI FOR LOGDATA
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.cd_logdata is null) then
    new.cd_logdata = gen_id(gen_logdata_id,1);
end
^

SET TERM ; ^

Explicações rápidas:

1) A tabela LOGOPERACAO registra: qual tabela (DS_TABELA) passou por uma inserção (FG_OPERACAO = 'I'), atualização (FG_OPERACAO = 'U') ou deleção (FG_OPERACAO = 'D'), qual foi o usuário responsável (CD_USUARIO), quando ocorreu a operação (DT_OPERACAO), e qual foi a PK (DS_CHAVE) afetada.

2) Vocês vão perceber que nesse processo não importa quantas chaves primárias sua tabela tem, pois o código foi pensado para concatenar as chaves separadas por pipe ('|').

3) A tabela LOGDATA armazena informações mais detalhadas sobre a LOGOPERACAO. Nesse caso seus registros contemplam qual foi o campo afetado na tabela (DS_CAMPO) apresentando seu valor antigo (DS_OLD) e o novo (DS_NEW).

3) Criação das procedures para auditoria no banco da empresa:

SET TERM ^ ;

create or alter procedure PROC_LOG (
    DS_TABELA varchar(31) not null)
as
declare variable "TYPE" smallint;
declare variable DS_CAMPO varchar(31);
declare variable SQL varchar(32765);
declare variable AUXOPERACAO varchar(255);
declare variable DS_CHAVEAUX varchar(31);
declare variable DS_CHAVENEW varchar(255);
declare variable DLL blob sub_type 1 segment size 256;
declare variable DS_CHAVEOLD varchar(255);
begin
   if (user = 'BACKUP') then
      exit;

   sql = '';
   ds_chavenew = '';
   ds_chaveold = '';
   auxoperacao = '';
   dll = '';

   for
      select
         upper(trim(F.RDB$FIELD_NAME)),
         FS.RDB$FIELD_TYPE
      from
         RDB$RELATION_FIELDS F
      left join RDB$FIELDS FS on FS.RDB$FIELD_NAME = F.RDB$FIELD_SOURCE
      where
         F.RDB$RELATION_NAME = :ds_tabela
      order by
         F.RDB$FIELD_POSITION
      into
         :ds_campo,
         :type
   do
   begin
      if ((:type <> 261) and (:ds_campo not in ('CD_USRINCALT', 'DT_INCALT'))) then /* IGNORAR 261 = BLOB */
      begin
         sql = sql || '   if (new.' || :ds_campo || ' is distinct from old.' || :ds_campo || ') then' || ascii_char(13) ||
                      '      execute procedure proc_logdata(' || ascii_char(13) ||
                      '         :cd_logoperacao, ' || ascii_char(13) ||
                      '         ''' || :ds_campo || ''',' || ascii_char(13) ||
                      '         old.' || :ds_campo || ',' || ascii_char(13) ||
                      '         new.' || :ds_campo || ');' || ascii_char(13);
      end
   end

   auxoperacao = '   if (inserting) then fg_operacao = ''I'';' || ascii_char(13) ||
                 '   else if (updating) then fg_operacao = ''U'';' || ascii_char(13) ||
                 '   else if (deleting) then fg_operacao = ''D'';' || ascii_char(13);

   for
      select
         trim(I.RDB$FIELD_NAME)
      from
         RDB$RELATION_CONSTRAINTS RC
      join RDB$INDEX_SEGMENTS I on (I.RDB$INDEX_NAME = RC.RDB$INDEX_NAME)
      join RDB$INDICES IDX on (IDX.RDB$INDEX_NAME = RC.RDB$INDEX_NAME)
      where
         (RC.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY') and
         (RC.RDB$RELATION_NAME = :ds_tabela)
      order by
         I.RDB$FIELD_POSITION
      into
         :ds_chaveaux
   do
   begin
      ds_chavenew = ds_chavenew || 'new.' || :ds_chaveaux || '||''|''||';
      ds_chaveold = ds_chaveold || 'old.' || :ds_chaveaux || '||''|''||';
   end

   ds_chavenew = substring(ds_chavenew from 1 for char_length(ds_chavenew) - 7);
   ds_chaveold = substring(ds_chaveold from 1 for char_length(ds_chaveold) - 7);

   dll = 'CREATE OR ALTER TRIGGER LOG_' || :ds_tabela || ' for ' || :ds_tabela || ascii_char(13) ||
         'ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 32767' || ascii_char(13) ||
         'as' || ascii_char(13) ||
         '   declare variable fg_operacao char(1) = null;' || ascii_char(13) ||
         '   declare variable cd_logoperacao bigint;' || ascii_char(13) ||
         'begin' || ascii_char(13) ||
         auxoperacao || ascii_char(13) ||
         '   if (:fg_operacao is null) then exit;' || ascii_char(13) || ascii_char(13) ||
         '   if (:fg_operacao = ''I'') then' || ascii_char(13) ||
         '      execute procedure proc_logoperacao(' || ascii_char(13) ||
         '         ''' || :ds_tabela || ''',' || ascii_char(13) ||
         '         ''I'',' || ascii_char(13) ||
         '         ' || :ds_chavenew || ')' || ascii_char(13) ||
         '      returning_values :cd_logoperacao;' || ascii_char(13) ||
         '   else' || ascii_char(13) ||
         '      execute procedure proc_logoperacao(' || ascii_char(13) ||
         '         ''' || :ds_tabela || ''',' || ascii_char(13) ||
         '         :fg_operacao,' || ascii_char(13) ||
         '         ' || :ds_chaveold || ')' || ascii_char(13) ||
         '      returning_values :cd_logoperacao;' || ascii_char(13) || ascii_char(13) ||
         sql || ascii_char(13) ||
         '   when any do' || ascii_char(13) ||
         '   begin' || ascii_char(13) || ascii_char(13) ||
         '   end' || ascii_char(13) ||
         'end';

   if (sql <> '') then
      execute statement :dll;
end^

SET TERM ; ^

SET TERM ^ ;

create or alter procedure PROC_LOGOPERACAO (
    DS_TABELA varchar(31) not null,
    FG_OPERACAO char(1) not null,
    DS_CHAVE varchar(255) not null)
returns (
    CD_LOGOPERACAO bigint)
as
declare variable SQL varchar(16384);
begin
   sql = 'insert into logoperacao(' ||
            'ds_tabela, ' ||
            'fg_operacao, ' ||
            'cd_usuario, ' ||
            'dt_operacao, ' ||
            'ds_chave) ' ||
         'values(' ||
            ':ds_tabela, ' ||
            ':fg_operacao, ' ||
            ':cd_usuario, ' ||
            ':dt_operacao, ' ||
            ':ds_chave) ' ||
         'returning ' ||
            'cd_logoperacao';

   execute statement (:sql) (
      ds_tabela := :ds_tabela,
      fg_operacao := :fg_operacao,
      cd_usuario := user,
      dt_operacao := current_timestamp,
      ds_chave := :ds_chave)
   on
      external 'c:\sysfire\loggeral.fdb'
   as
      user 'SYSDBA'
      password 'sys@#$'
   with
      common transaction
   into
      :cd_logoperacao;
end^

SET TERM ; ^

SET TERM ^ ;

create or alter procedure PROC_LOGDATA (
    CD_LOGOPERACAO bigint not null,
    DS_CAMPO varchar(31) not null,
    DS_OLD varchar(4000),
    DS_NEW varchar(4000))
as
declare variable SQL varchar(16384);
begin
   sql = 'insert into logdata(' ||
            'cd_logoperacao, ' ||
            'ds_campo, ' ||
            'ds_old, ' ||
            'ds_new) ' ||
         'values(' ||
            ':cd_logoperacao, ' ||
            ':ds_campo, ' ||
            ':ds_old, ' ||
            ':ds_new)';

   execute statement (:sql) (
      cd_logoperacao := :cd_logoperacao,
      ds_campo := :ds_campo,
      ds_old := :ds_old,
      ds_new := :ds_new)
   on
      external 'c:\sysfire\loggeral.fdb'
   as
      user 'SYSDBA'
      password 'sys@#$'
   with
      common transaction;
end^

SET TERM ; ^

Explicações rápidas:

1) A PROC_LOGOPERACAO insere os registros no banco externo na tabela LOGOPERACAO.

2) A PROC_LOGDATA insere os registros no banco externo na tabela LOGDATA.

3) A PROC_LOG gera automaticamente uma trigger para after insert, update e delete (na última posição possível 32767) em uma tabela que seja necessário a auditoria. Aqui não importa quais são os campos da tabela, a geração é autônoma e independe do tipo, tamanho, etc.

3.1) Caso a tabela possua campos tipo BLOB (261) eles serão ignorados.

3.2) Se for necessário auditar campos VARCHAR com tamanho superior a 4000 será necessário rever a criação da tabela LOGDATA e da procedure PROC_LOGDATA pois na forma atual esse é o limite (o maior campo da minha estrutura de dados possui 4000 posições, esse é o motivo da minha escolha).

3.3) Os campos CD_USRINCALT e DT_INCALT estão sendo ignorados pois fazem parte das minhas tabelas e não precisam ser auditados, retirar ou deixá-los no código não afetará em nada no funcionamento. Caso existam campos que você quer ignorar, faça a inserção manual no código, a mesma coisa para tipos de campos, atualmente apenas BLOB é ignorado (você pode pesquisar no fonte para ver os códigos referentes a outros tipos).

4) Na PROC_LOGOPERACAO e PROC_LOGDATA verificar o caminho do banco de auditoria, como teste está fixo c:\sysfire\loggera.fdb. Verificar também a senha do user master SYSDBA, pois a conexão com o banco externo será feita por ele.

Após realizar os passos acima só será necessário escolher as tabelas que precisam ser auditadas e chamar a PROC_LOG para geração das triggers (o nome da tabela deve ser exato, com letras maiúsculas e minúsculas, caso seja informada uma tabela que não existe em sua database uma exceção é lançada).

execute procedure PROC_LOG('nome_da_tabela');

A partir daqui qualquer tipo de insert, update e delete nas tabelas executas com a PROC_LOG vão gerar registros no banco de auditoria.

Bom proveito, obrigado!

Edit1: caso uma tabela que já está sendo auditada sofra alterações de campos (novos campos ou drop de campos antigos) é só fazer a chamadada mesma com a PROC_LOG que as triggers serão atualizadas automaticamente. Processo fácil e rápido para manutenção.

[Renato Rubinho]

Olá,

Parabéns pela iniciativa.

Caso seja uma cópia "oficial" do que você está usando, cuidado que o nome do usuário 'BAKCUP' está errado.

1 hora atrás, Roberto Henrique Borges Machado disse:

3) Criação das procedures para auditoria no banco da empresa:

SET TERM ^ ;

create or alter procedure PROC_LOG (
    DS_TABELA varchar(31) not null)
as
.
.
.
begin
   if (user = 'BAKCUP') then  <<<< AQUI 
      exit;

 

 

[Roberto Henrique Borges Machado]

38 minutos atrás, Renato Rubinho disse:

Olá,

Parabéns pela iniciativa.

Caso seja uma cópia "oficial" do que você está usando, cuidado que o nome do usuário 'BAKCUP' está errado.

 

Corrigido, só não consegui editar no post. Obrigado.

[Renato Rubinho]

Corrigido no post.