Whats new? | Login | Parceiros
Cadastre-se | Atendimento | RSS

Live chat by Netwatts
Você está em: / canal SQL [artigos]
+ SQL:
artigos   |   vídeos   |    cursos   |    mais
Este post foi publicado diretamente pelo autor e não foi revisado pela DevMedia.

Implementando Logs de Auditoria em Banco de Dados Oracle

É uma necessidade presente em todas as organizações atualmente que todas as operações eletrônicas (ao menos boa parte delas) executadas em ambientes transacionais possam ser auditadas a posteriori. Este artigo apresenta uma solução simples, porém útil, para a implementação de logs de auditoria em bancos de dados oracle.

Daniel Cardoso Moraes de Oliveira
Obteve o título de Bacharel em Ciência da Computação em 2004 pela Universidade Federal do Rio de Janeiro (IM/DCC) e o de Mestrado em 2008, pela COPPE/UFRJ. Atualmente realiza o seu Doutorado na Linha de Banco de Dados, no PESC - COPPE/UFRJ. Atua em p...


Ver space do autor


Estatísticas:
Visualizações:
5080
Favoritado:
 8 vez(es)
Conteúdo:
Didática:
Utilidade:
2 0
votos: 2

Serviços:



 1. Introdução

É uma necessidade presente em todas as organizações atualmente que todas as operações (ao menos boa parte delas) executadas em ambientes transacionais possam ser auditadas a posteriori. Práticas de auditoria são comuns e muito úteis no processo de identificação de discrepâncias na base ou no processo de correção de problemas gerados por bugs na aplicação, por exemplo. Este artigo apresenta uma solução simples, porém útil, para a implementação de logs de auditoria em bancos de dados OracleTM.

2. Implementação do Log de Auditoria

Tomemos como tabela base do exemplo uma tabela simples (que chamaremos de AGENCIA. Esta tabela tem como finalidade armazenar os dados de agências num sistema qualquer. É composta de quatro campos:

  1. ID: chave primária da tabela. Provavelmente gerado por uma sequence e sem associação com qualquer outra tabela da base de dados.
  2. CODIGO: campo texto que armazenará o código real da agência. Na prática, caso o campo CODIGO fosse único na base, poderia ser utilizado como chave primária. Mas é aconselhável que se use uma chave surrogata (ID) que é invariável. No caso do código, o mesmo poderia mudar com o tempo necessitando de atualizações na base.
  3. NOME: campo texto que indica o nome da agência.
Abaixo é exibido o script para geração da tabela AGENCIA:

CREATE TABLE AGENCIA
(
  ID                                              NUMBER(38)                  NOT NULL,
  CODIGO                                    CHAR(2 BYTE)                NOT NULL,
  NOME                                        VARCHAR2(2 BYTE)        NOT NULL
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

A idéia principal do log de auditoria é que qualquer operação executada nesta tabela base (AGENCIA) seja registrada em uma nova tabela que pode ser consultada a posteriori. Esta nova tabela deve gravar as alterações, inclusões e exclusões de informações da tabela base. Assim sendo, a nova tabela deve conter todos os campos da tabela base, com a excessão do ID, duplicados (um campo para gravar o valor anterior e outro para o novo valor) mais os campos para armazenamento do PROGRAMA, USUARIO, DATA e OPERACAO. O Script da tabela de auditoria ficaria semelhante ao abaixo explicitado:

CREATE TABLE LOG_AGENCIA
(
  ID                                              NUMBER(38),
  CODIGO_OLD                            CHAR(2 BYTE),
  CODIGO_NEW                           CHAR(2 BYTE),
  NOME_OLD                                VARCHAR2(2 BYTE),
  NOME_NEW                               VARCHAR2(2 BYTE) NOT NULL,
  PROGRAMA                                VARCHAR2(200 BYTE),
  USUARIO                                   VARCHAR2(100 BYTE),
  DATA                                         DATE,
  OPERACAO                                VARCHAR2(1 BYTE)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

O que você pode estar se perguntando neste momento é: minha aplicação deverá gravar estes dados na tabela de auditoria? Sim, esta é uma solução possível, mas fica muito dependente da aplicação, uma vez que operações podem ser "burladas" via aplicação, não sendo registradas devidamente. Desta maneira, o recomendável é que os logs de auditoria sejam preenchidos via SGBD. A solução mais prática para tal é a utilização de TRIGGERS para inserir registros na tabela de log de auditoria toda vez que alguma operação for executada na tabela base. A trigger deve ser de AFTER INSERT, DELETE e UPDATE, ou seja, após qualquer uma destas três operações na tabela base, um registro será inserido na tabela de log de auditoria. Abaixo é exibido um exemplo da trigger que realiza esta tarefa, comentado parte a parte.

OBSERVAÇÃO: foi criado um objeto sequence para gerar os ID da tabela de log de auditoria. O código de geração desta sequence é exibido a seguir:

CREATE SEQUENCE SQ_LOG_AGENCIA
  START WITH 1
  MAXVALUE 1000000000000000000000000000
  MINVALUE 1
  NOCYCLE
  NOORDER;

CREATE OR REPLACE TRIGGER TRG_AIUD_AGENCIA AFTER INSERT OR DELETE OR UPDATE ON AGENCIA FOR EACH ROW

DECLARE
  Operacao  CHAR(1);
  Programa VARCHAR2(50);
  Usuario VARCHAR2(50);
 
BEGIN

    /* A parte a seguir do código captura, via SQL, o usuário do sistema operacional, a nome da máquina que o usuário está utilizando a aplicação, o usuário do Banco e o programa a partir do qual os dados estão sendo modificados. Você pode se perguntar porque armazenar o programa, mas é importante saber quando os dados foram modificados via aplicação ou diretamente na base de dados via SQL. */


    SELECT PROGRAM , ' ( '||OSUSER||' ) '||MACHINE  MAQUINA
    INTO Programa, Usuario
    FROM V$SESSION
    WHERE AUDSID = USERENV('SESSIONID');


/* Utilizando as variáveis do Oracle, podemos identificar a operação que disparou a trigger. Utilizar estas varáveis é uma boa prática para evitar que se criem três triggers (uma de after insert, uma de after update e outra de after delete) */

  IF INSERTING THEN
    Operacao := 'I';
  ELSIF UPDATING THEN
    Operacao := 'U';
  ELSE    /* Há Deleção */
    Operacao := 'D';
  END IF;


/*  A partir da operação armazenada, a trigger insere os registros na tabela de auditoria. A regra é a seguinte: para inserções, se insere o valor novo. Para deleções apenas o último valor que existia na tabela e para updates o valor antigo e o novo valor */

IF Operacao = 'D' THEN /*DELETE*/

    INSERT INTO LOG_AGENCIA
    (ID,CODIGO_OLD,CODIGO_NEW,NOME_OLD,NOME_NEW,PROGRAMA,USUARIO,DATA,OPERACAO) VALUES
    (SQ_LOG_AGENCIA.NEXTVAL,:OLD.CODIGO,NULL,:OLD.NOME, NULL, Programa, Usuario, SYSDATE, Operacao);

END IF;   


IF Operacao = 'I' THEN /*INSERT*/

    INSERT INTO LOG_AGENCIA
    (ID,CODIGO_OLD,CODIGO_NEW,NOME_OLD,NOME_NEW,PROGRAMA,USUARIO,DATA,OPERACAO) VALUES
    (SQ_LOG_AGENCIA.NEXTVAL,NULL,:NEW.CODIGO,NULL,:NEW.NOME, Programa, Usuario, SYSDATE, Operacao);

END IF;   


IF Operacao = 'U' THEN /*UPDATE*/

    INSERT INTO LOG_AGENCIA
    (ID,CODIGO_OLD,CODIGO_NEW,NOME_OLD,NOME_NEW,PROGRAMA,USUARIO,DATA,OPERACAO) VALUES
    (SQ_LOG_AGENCIA.NEXTVAL,:OLD.CODIGO,:NEW.CODIGO,:OLD.NOME, :NEW.NOME, Programa, Usuario,                 SYSDATE, Operacao);

END IF;   

END;

3. Conclusões

Conclui-se que a criação de um log de auditoria facilita a interpretação da informação e torna sua visualização mais clara e abrangente. Além disto, fornece subsídios para que os DBA`s e auditores possam realizar diagnósticos mais precisos do que está acontecendo com os dados da base assim como identificar possíveis problemas na aplicação que estejam refletindo no banco.













Participe! Inclua um comentário
[Fechar]

Este post é fechado - você precisa ter acesso ao post para incluir um comentário.


Nenhum comentário foi postado - seja o primeiro a comentar ;-)




 
 

[Este post ainda não foi associado a uma sequência]


[Fechar]
Este post está disponível somente para quem possui Créditos DevMedia. (Ele não está associado a nenhuma publicação DevMedia).


  Conheça os planos de créditos DevMedia e visualize esse post agora mesmo!

Plano conveniência – Neste plano este post custa R$ 0,00 (Compre agora)
Esse plano permite que você compre somente um post, pagando por ele seu preço sem desconto.

Plano ocasional: Aqui este post custa: R$ -1,00 (assinante) ou R$ -1,00 (não-assinante)
Este plano é ideal para quem tem interesse em mais de um post. Você compra um mínimo de R$ 50,00 em créditos e ganha, em média, 50% de desconto no preço do post. Compre Créditos agora!

Assinatura de Créditos (Plano econômico) – Aqui este post custa R$ -1,00
Este plano é ideal para quem tem interesse em muitos posts. Com esse plano você compra R$ 180,00 em créditos e ganha, em média, 80% de desconto no preço do post. Assine este plano agora!

> Saiba mais sobre o Sistema de Créditos DevMedia
DevMedia Group   http://www.devmedia.com.br/   |   http://www.javafree.org/   |   http://www.mrbool.com/
2010 - Todos os Direitos Reservados a DevMedia Group - (21) 3382-5038