Criando Logs de Auditoria para Eventos do Tipo DDL para Objetos dos Esquemas do BD Oracle

Criando Logs de Auditoria para Eventos do Tipo DDL para Objetos dos Esquemas do BD Oracle Através de Trigger

Quando trabalhamos como DBA dando suporte ao pessoal da aplicação, e principalmente quando mais de uma pessoa “mexe” no banco de dados, é comum acontecer de alguns objetos que dependem ou referenciam algumas tabelas simplesmente ficarem inválidos, inviabilizando o trabalho. Em outras situações mais críticas, bancos de dados são atacados por hackers que, dependendo do nível de acesso que estes conseguiram obter, podem fazer um estrago muito grande na estrutura de toda base de dados.

Visto o cenário acima, não existe como evitar este transtorno, pois em um projeto de dimensão média, é necessário mais de uma pessoa dedicada a trabalhar com o banco de dados, ou então, como acontece na maioria das vezes, elege-se um programador mais experiente e que gosta de trabalhar com banco de dados para executar essa tarefa. Quanto aos hackers, estamos longe de conseguir pará-los.

Assim sendo, se não podemos evitar este transtorno, uma boa dica é “loggar” toda e qualquer operação do tipo DDL, que torna o banco capaz de mostrar quem fez o que e quando, não somente para fins de punição, mas para coibir algumas ações que são feitas somente para testar o sistema (sem a análise prévia das consequencias) e também para identificar a origem de possíveis ataques criminosos às bases de dados.

Para que seja possível armazenar estes logs, obviamente vamos criar uma tabela. A dica sempre é deixar a tabela em um esquema dedicado à auditoria, pois facilita sua manutenção e fica mais fácil gerenciar os acessos à ela.

Inicialmente, vamos criar um usuario para ser o owner da tabela de auditoria:

create user USU_AUDIT identified by SouCruel135*;

grant DBA to USU_AUDIT;

Depois de criarmos o novo usuário, logamos no Oracle com este usuário para criarmos a tabela de auditoria:

conn USU_AUDIT/SouCruel135*

CREATE TABLE TB_AUDITA_DDL (
  data date,
  usu_so varchar2(255),
  usu_bd varchar2(255),
  ident_host varchar2(255),
  ident_terminal varchar2(255),
  esquema varchar2(30),
  tipo varchar2(30),
  nome varchar2(30),
  evento varchar2(30)
);

create or replace trigger TG_AUDITA_DDL after ddl on schema  
 begin
  if (ora_sysevent='TRUNCATE')
  then
    null; -- não audita o comando truncate, pois ele não altera a estrutura das tabelas

  else
    insert into TB_AUDITA_DDL values(
      sysdate,
      sys_context('USERENV','OS_USER') ,
      sys_context('USERENV','CURRENT_USER') ,
      sys_context('USERENV','HOST') , 
      sys_context('USERENV','TERMINAL') ,
      ora_dict_obj_owner,
      ora_dict_obj_type,
      ora_dict_obj_name,
      ora_sysevent
    );

  end if;

end;
/

Depois de criada a tabela e o trigger, podemos brincar de criar tabelas, alterar e dropar. Todas essas ações vão ser registradas na tabela que audita tais operações. Segue um exemplo para teste:

create table tb_teste_01(
   id number(5) primary key,
   descricao varchar2(100)
);

alter table tb_teste_02 modify (descricao varchar2(255) not null);

drop table tb_teste_02;

Os dados registrados poderão ser consultados com o comando a seguir:

select * from TB_AUDITA_DDL;

Referências

• http://psoug.org/reference/ddl_trigger.html
• https://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#i1007170
• http://www.devmedia.com.br/implementando-logs-de-auditoria-em-banco-de-dados-oracle/9992
• http://www.dba-oracle.com/t_ddl_triggers.htm