Rastreando as modificações DDL: Criando logs para modificações do seu schema Oracle

Algumas vezes aqui no trabalho, dois desenvolvedores resolveram alterar a mesma package quase ao mesmo tempo. Um deles estava alterando desde o início da tarde e foi compilando no decorrer dessa mesma tarde. O outro abriu o corpo da package para alterar durante a manhã, fez umas alterações no final do dia e compilou à noite. Não existia processo de CI/CD e obviamente um deles perdeu as modificações que tinha feito. Adivinha quem foi o desenvolvedor que perdeu as alterações? Eu.

Para não passar mais por isso, uma abordagem simples que resolvemos adotar foi a criação de uma trigger que audita todas as modificações feitas no schema do banco e grava numa tabela. Assim, temos bastante controle de alteração e autonomia, sem precisar depender do DBA para restaurar versões anteriores. Outra vantagem, foi o fato de que fica bem fácil de ver quem fez as alterações nos objetos e nos traz uma forma fácil de acompanhar cada alteração no código ou estrutura do schema.

Esse método descrito abaixo foi testado na versão 19c do Oracle Database Standard Edition e os comendos devem ser executados com o próprio OWNER do SCHEMA que você quer auditar.

  CREATE TABLE DDL_LOG
       ("DDL_LOG_ID" NUMBER, 
        "DDL_OPERATION" VARCHAR2(30 BYTE), 
        "OBJECT_TYPE" VARCHAR2(30 BYTE), 
        "OBJECT_NAME" VARCHAR2(128 BYTE), 
        "APP_USER_NAME" VARCHAR2(30 BYTE), 
        "EVENT_TIMESTAMP" TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP, 
        "DDL_SQL_TEXT" CLOB, 
        "DDL_SQL_TEXT_2" CLOB,
        "CLIENT_IP_ADDRESS" VARCHAR2(150 BYTE), 
        "OBJECT_OWNER" VARCHAR2(30 BYTE), 
        "DB_USER_NAME" VARCHAR2(30 BYTE), 
        "OS_USER_NAME" VARCHAR2(50 BYTE), 
        "CLIENT_HOST_NAME" VARCHAR2(50 BYTE), 
        "NOTES" VARCHAR2(4000 BYTE), 
         CONSTRAINT "DDL_LOG_PK" PRIMARY KEY ("DDL_LOG_ID") ) ;
/

CREATE SEQUENCE SEQ_DDL_LOG_ID
   START WITH 1
   INCREMENT BY 1
   NOCACHE
   NOCYCLE;
/

--Se este trecho não rodar quando for inserir via navegador, pode passar via EXECUTE IMMEDIATE, já que não tem como colocar DEFINE OFF no APEX
  CREATE OR REPLACE EDITIONABLE TRIGGER TRG_DDL_LOG_SEQ
BEFORE INSERT ON DDL_LOG FOR EACH ROW 
BEGIN
  BEGIN
    IF INSERTING AND :NEW.DDL_LOG_ID IS NULL THEN
      SELECT SEQ_DDL_LOG_ID.NEXTVAL INTO :NEW.DDL_LOG_ID FROM SYS.DUAL;
    END IF;
  END COLUMN_SEQUENCES;
END;
/
ALTER TRIGGER TRG_DDL_LOG_SEQ ENABLE;
/

Agora você deve criar o trigger que irá fazer a auditoria. Lembre-se de utilizar o OWNER do SCHEMA que você pretende auditar.

create or replace TRIGGER ddl_audit_trigger AFTER
CREATE OR ALTER OR DROP ON SCHEMA

DECLARE
  v_sql_text  CLOB;
  v_text_item ora_name_list_t;

BEGIN

--TRATA A FORMA QUE O DDL SERÁ SALVO NO BLOB
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', TRUE);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', TRUE);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', FALSE);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'STORAGE', FALSE);      
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'EMIT_SCHEMA', FALSE);



-- Captura o SQL executado (DDL real)
  v_sql_text := NULL;
  FOR i IN 1 .. ora_sql_txt(v_text_item) LOOP
    v_sql_text := v_sql_text || v_text_item(i);
  END LOOP;


-- Desconsidera as mudanças feitas nos próprios objetos relacionados a auditoria
        IF ora_dict_obj_name IN ('DDL_LOG', 'DDL_AUDIT_TRIGGER') THEN 
            NULL;
            RETURN;

        ELSE

            INSERT INTO ddl_log (ddl_operation,                     
                                 object_type,
                                 object_name,
                                 app_user_name,          
                                 ddl_sql_text,
                                 ddl_sql_text_2,
                                 client_ip_address,
                                 db_user_name,
                                 object_owner,              
                                 os_user_name,
                                 client_host_name
                                )    
            VALUES              (ora_sysevent,
                                 REPLACE(ora_dict_obj_type, ' ', '_'),
                                 ora_dict_obj_name,
                                 NVL(V('APP_USER') ,SYS_CONTEXT('USERENV','CURRENT_USER')),
                                 dbms_metadata.get_ddl(REPLACE(UPPER(ora_dict_obj_type), ' ', '_'), UPPER(ora_dict_obj_name)),
                                 v_sql_text,
                                SYS_CONTEXT('USERENV', 'IP_ADDRESS'),   
                                ora_login_user,
                                ora_dict_obj_owner, 
                                SYS_CONTEXT('USERENV', 'OS_USER'),  
                                SYS_CONTEXT('USERENV', 'HOST')             
            );
        END IF;
EXCEPTION WHEN OTHERS THEN
      NULL; --Não disparar erro, apenas ignorar. Criar rotina de log de erro.
      RETURN;
END;
/

Agora cada vez que modificar tabela ou outros objetos, o comando será salvo na tabela DDL_LOG, conforme demonstrado abaixo.

Existem outras soluções para isso, porém, essa foi a solução mais prática que encontrei sem depender do DBA. Se tiver sugestões de melhoria, fique a vontade para comentar.