Ir para conteúdo
  • Cadastre-se

dev botao

  • Este tópico foi criado há 874 dias atrás.
  • Talvez seja melhor você criar um NOVO TÓPICO do que postar uma resposta aqui.

Recommended Posts

Postado

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.

  • Curtir 5
  • Obrigado 1
  • Consultores
Postado

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;

 

 

  • Este tópico foi criado há 874 dias atrás.
  • Talvez seja melhor você criar um NOVO TÓPICO do que postar uma resposta aqui.

Crie uma conta ou entre para comentar

Você precisar ser um membro para fazer um comentário

Criar uma conta

Crie uma nova conta em nossa comunidade. É fácil!

Crie uma nova conta

Entrar

Já tem uma conta? Faça o login.

Entrar Agora
×
×
  • Criar Novo...

Informação Importante

Colocamos cookies em seu dispositivo para ajudar a tornar este site melhor. Você pode ajustar suas configurações de cookies, caso contrário, assumiremos que você está bem para continuar.

The popup will be closed in 10 segundos...