Auditar Cambios a Estructuras de la BD

¿Cómo podemos tener una auditoría automática de los cambios estructurales en una base de datos Oracle?

Esto es importante saber en el caso de que se tenga que realizar una auditoría, saber que ocurrió antes de que se presentaran errores en el sistema o ver que usuarios han realizado los cambios.

El ejemplo siguiente consiste en la creación de una tabla (para mantener el log de los cambios) y un trigger (se encargará de registrar los cambios realizados). Esto nos permitirá el script antes del cambio y el SQL ejecutado que generó el cambio.

Procedimiento para la generación de la auditoríaCreación de la tabla.

CREATE TABLE RESPALDOS_OBJETOS ( FECHA TIMESTAMP(6), EVENTO VARCHAR2(30 BYTE), PROPIETARIO VARCHAR2(30 BYTE), TIPO_OBJETO VARCHAR2(64 BYTE), NOMBRE_OBJETO VARCHAR2(60 BYTE), USUARIO VARCHAR2(30 BYTE), MAQUINA VARCHAR2(100 BYTE), TERMINAL VARCHAR2(20 BYTE), PROGRAMA VARCHAR2(64 BYTE), MODULO VARCHAR2(60 BYTE), CLIENTE VARCHAR2(64 BYTE), OBJETO CLOB, SQL CLOB ) TABLESPACE TS_DATOS PCTUSED 0 PCTFREE 0 INITRANS 1 MAXTRANS 255 NOCACHE NOPARALLEL MONITORING; COMMENT ON TABLE RESPALDOS_OBJETOS IS 'Auditoria y respaldos de objetos de un esquema de base de datos'; COMMENT ON COLUMN RESPALDOS_OBJETOS.FECHA IS 'Fecha en que se realiza el cambio'; COMMENT ON COLUMN RESPALDOS_OBJETOS.EVENTO IS 'si es un create, drop o replace'; COMMENT ON COLUMN RESPALDOS_OBJETOS.PROPIETARIO IS 'propietario del objeto'; COMMENT ON COLUMN RESPALDOS_OBJETOS.TIPO_OBJETO IS 'si es una tabla, vista, procedimiento, funcion, paquete, secuencia, etc'; COMMENT ON COLUMN RESPALDOS_OBJETOS.NOMBRE_OBJETO IS 'nombre del objeto'; COMMENT ON COLUMN RESPALDOS_OBJETOS.MAQUINA IS 'maquina en donde se realizo el cambio'; COMMENT ON COLUMN RESPALDOS_OBJETOS.TERMINAL IS 'terminal en donde se realizo el cambio'; COMMENT ON COLUMN RESPALDOS_OBJETOS.PROGRAMA IS 'programa en donde se realizo el cambio'; COMMENT ON COLUMN RESPALDOS_OBJETOS.MODULO IS 'modulo en donde se realizo el cambio'; COMMENT ON COLUMN RESPALDOS_OBJETOS.CLIENTE IS 'cliente en donde se realizo cambio'; COMMENT ON COLUMN RESPALDOS_OBJETOS.OBJETO IS 'ddl del objeto antes del cambio'; COMMENT ON COLUMN RESPALDOS_OBJETOS.SQL IS 'Script que realiza el cambio al objeto';

Creación del trigger de base de datos

CREATE OR REPLACE TRIGGER CTRL_CONTROL_CAMBIOS BEFORE ALTER OR CREATE OR DROP ON DATABASE --Este trigger se disparara cada vez q se realicen alteraciones en los objetos --de base de datos indicados DECLARE /****************************************************************************** Realizado por Endara Manosalvas Victor Objetivo : Registra los cambios DDL realizados en la base de datos -- ora_sysevent : Tipo de operacion realizada ora_dict_obj_owner : Propietario de los objetos afectados ora_dict_obj_name : Nombre del objeto que interviene en la transaccion DDL ora_dict_obj_type : Tipo de objeto ******************************************************************************/ --Variables Ln_Existe NUMBER(5); lb_tipo_objeto user_objects.object_type%type; lcl_script clob:=null; --almacena el script antes de la modificacion del objeto BEGIN --Respaldos a objeto de bd if Ora_Dict_Obj_Type IN ('FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'VIEW', 'TRIGGER','TABLE','SEQUENCE', 'TRIGGER','JOB','SINONYM', 'INDEX') AND Ora_Sysevent IN ('CREATE','DROP','ALTER') then for c_usr in ( select USERNAME, TERMINAL, PROGRAM, MODULE, MACHINE, CLIENT_INFO, SQL_ID from v$session where Sid = Sys_Context('USERENV','SID'))loop if ora_dict_obj_type='PACKAGE BODY' then lb_tipo_objeto:='PACKAGE'; else lb_tipo_objeto:=Ora_Dict_Obj_Type; end if; --Con la ayuda de la utilidades del DBMS_METADATA obtendremos el DDL del objeto begin lcl_script:=DBMS_METADATA.GET_DDL(lb_tipo_objeto, ora_dict_obj_name,ora_dict_obj_owner); exception when others then lcl_script:=sqlerrm; end; insert into respaldos_objetos( FECHA, EVENTO, PROPIETARIO, TIPO_OBJETO, NOMBRE_OBJETO, USUARIO, MAQUINA, TERMINAL, PROGRAMA, MODULO, CLIENTE, OBJETO, SQL) values( SYSTIMESTAMP, ora_sysevent, ora_dict_obj_owner, ora_dict_obj_type, ora_dict_obj_name, c_usr.USERNAME, c_usr.MACHINE, c_usr.TERMINAL, c_usr.PROGRAM, c_usr.MODULE, c_usr.CLIENT_INFO, lcl_script, (select SQL_FULLTEXT from v$sql where sql_id=c_usr.SQL_ID)); end loop; end if; EXCEPTION WHEN OTHERS THEN RAISE; END; /

Ejemplo para la prueba de la auditoria
En este ejemplo procederemos a crea la tabla VENTAS, luego agregar el campo IMPUESTO y finalmente eliminaremos el campo ESTADO.

CREATE TABLE ventas ( id_venta NUMBER, id_persona NUMBER, fecha DATE, valor NUMBER, valor_neto NUMBER, valor_total NUMBER, estado VARCHAR2(1) ); ALTER TABLE ventas ADD impuesto NUMBER; ALTER TABLE ventas DROP COLUMN estado;

Ahora veamos lo que se registró en la tabla de auditoría creada RESPALDOS_OBJETOS.

• FECHA: Fecha y hora en la que se ejecutó la sentencia
• EVENTO: Tipo de evento ejecutado Alter, Replace, Create o Drop
• MAQUINA: Desde que terminal se realizó el cambio.
• SQL: Es la sentencia ejecutada que realizó la alteración.
  
   

Vemos como se registró de manera automática los cambios realizados en la base, primero la creación de la tabla, luego la creación del campo y por último la eliminación del campo.
Solo se muestran los campos FECHA, EVENTO, MAQUINA y SQL pero hay otras que muestran mas información como la OBJETO que contiene el script de cómo estaba el objeto antes del cambio.


Consideraciones:
El usuario que cree el trigger deberá tener los privilegios suficientes para crear y accesar a las vistas de datos (v$session y v$sql) y procedimientos DBMS_METADATA