El concepto básico de un log transaccional es aquel que la define como las tabla(s) de la base de datos, donde son registrados todos los cambios a los datos.
Toda transacción SQL como insert, update, delete, se deben guardar en la tabla(s) de logs, con el propósito de contar con un repositorio de datos que permita contar con los mecanismos de auditoria e identificar los cambios realizados a la información por parte de los usuarios que interactúan con el sistema de información.
Para las transacciones de tipo Update se requiere guardar en el log el estado antes y después del estado de la información.
Las tablas encargadas de registrar los logs de las transacciones deben permitir:
- Identificar a los diferentes usuarios que interactúan con el sistema.
- La fecha y hora en que los usuarios realiza la transacción, de tal forma de hacerlos responsables por las acciones que ellos realizan en los aplicativos que interactúan con la base de datos.
Existen dos alternativas para registrar en las tablas los logs de las transacciones ejecutadas:
- La primera es llenar las tablas mediante el aplicativo, esto significa mucho esfuerzo en programación, especialmente cuando existen cambios en la Base de Datos.
- La segunda alternativa es llenar las tablas mediante el diseño de triggers, esta opción tiene la ventaja de ser independiente del aplicativo y las tablas se llenarán ya sea cuando se haga modificaciones directamente mediante sentencias SQL o mediante opciones de menú o comandos del aplicativo.
ESTABLECIMIENTO DE LOGS
Basados en las alternativas de la forma para registrar la información en las tablas de logs de las transacciones, se recomienda utilizar el diseño de trigger. Debido a que se ejecutan cuando sucede un evento como Insert, Delete, Update y se disparan antes (Before) y/o después (After) de que la información sea modificada y con ello evitar la implementación a nivel de aplicación.
El control de los logs a través de trigger permite registrar las transacciones realizadas mediante la base de datos como de los aplicativos.
Esquema de logs a utilizar
La estructura de los trigger a utilizar para registrar los logs de las transacciones en las tablas de auditoria, antes de que la información sea modificada es el siguiente:
Descripción del esquema logs
Breve descripción del significado de los campos que componen el esquema del logs de transacciones para una tabla
|
Nombre |
Descripción |
| Tabla de auditoria | Tabla donde se registra la información de la transacción realizada |
| Before Delete Or Insert Or Update | Disparador a utilizar para activar el trigger. Se activa antes que se realice los cambios(insertar o actualizar o borrar) en la tabla auditada y registrar la transacción |
| Tabla Auditada | Tabla donde se crea el trigger |
| Usuario | Usuario que realiza la transacción |
| Tipo | Sentencia DML ejecutada: INSERT, UPDAT, DELETE |
| Fecha | Fecha y hora de la transacción |
| Estación | Equipo desde el cual se ejecuta la transacción |
| Os_user | Usuario del sistema operativo |
| Session_id | Identificador de la sesión |
| Db_name | Instancia donde se ejecuta la transacción |
| Ip | Dirección IP de la estación |
| Module | Información del aplicativo donde se ejecuta la transacción |
| Registros.old | Registro del valor de los campos antiguos |
| Registros.New | Registro del nuevo valor de los campos |
PRUEBAS DE LOGS DE TRANSACCIÓN
Tabla de auditoria y tabla auditada a controlar:
Para probar el esquema de log, se crea dos tablas con la siguiente estructura.
En la tabla auditada se registran los datos de personas
CREATE TABLE T_PRUEBA_LOG
(
CEDULA VARCHAR2(15 BYTE) NOT NULL,
NOMBRE VARCHAR2(30 BYTE) NOT NULL,
APELLIDOS VARCHAR2(30 BYTE) NOT NULL,
ESTADO CHAR(1 BYTE) NOT NULL
FECHA_CREACION DATE DEFAULT SYSDATE
)
ALTER TABLE T_PRUEBA_LOG ADD (
CONSTRAINT T_PRUEBA_LOG__PK
PRIMARY KEY
(CEDULA)
En esta tabla de auditoria se registran el logs de las transacciones generadas en la tabla anterior aplicando el esquema propuesta
CREATE TABLE T_AUDIT_T_RUEBA_LOG
(
USUARIO VARCHAR2(30 BYTE),
TIPO VARCHAR2(1 BYTE),
FECHA DATE,
ESTACION VARCHAR2(50 BYTE),
IP VARCHAR2(50 BYTE),
OS_USER VARCHAR2(50 BYTE),
SESSION_ID VARCHAR2(20 BYTE),
DB_NAME VARCHAR2(30 BYTE),
MODULE VARCHAR2(30 BYTE),
CEDULA_OLD VARCHAR2(15 BYTE),
CEDULA_NEW VARCHAR2(15 BYTE),
NOMBRE_OLD VARCHAR2(30 BYTE),
NOMBRE_NEW VARCHAR2(30 BYTE),
APELLIDOS_OLD VARCHAR2(30 BYTE),
APELLIDOS_NEW VARCHAR2(30 BYTE),
ESTADO_OLD VARCHAR2(1 BYTE),
ESTADO_NEW VARCHAR2(1 BYTE)
)
Trigger de registro de logs de transacciones
Se creó el siguiente tigger en la tabla auditada que se dispara antes de que se realice cualquier modificación y registra el logs de la transacción en la tabla de auditoria correspondiente
CREATE OR REPLACE TRIGGER TRG_AUDIT_T_PRUEBA_LOG
BEFORE DELETE OR INSERT OR UPDATE
ON T_PRUEBA_LOG
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
TP VARCHAR2(1);
v_mod VARCHAR2(48);
v_act VARCHAR2(32);
BEGIN
IF INSERTING THEN TP := ‘I’;
ELSIF UPDATING THEN TP := ‘U’;
Else tp := ‘D’;
END IF;
DBMS_APPLICATION_INFO.READ_MODULE (v_mod, v_act);
INSERT INTO T_AUDIT_T_RUEBA_LOG(USUARIO,TIPO,FECHA,ESTACION,IP,OS_USER,SESSION_ID,DB_NAME,MODULE, CEDULA_OLD,CEDULA_NEW,NOMBRE_OLD,NOMBRE_NEW,APELLIDOS_OLD,APELLIDOS_NEW,
ESTADO_OLD,ESTADO_NEW)
VALUES(USER,TP,SYSDATE,(select sys_context(‘USERENV’,’host’) from dual),(select sys_context(‘USERENV’,’IP_ADDRESS’) from dual),
(select substr(sys_context(‘USERENV’,’OS_USER’),1,50) from dual),(select substr(userenv(‘SESSIONID’),1,10) from dual),(select sys_context(‘USERENV’,’DB_NAME’) from dual),v_mod,:old.cedula,:new.cedula,:old.nombre,:new.nombre,:old.apellidos,:new.apellidos,:old.estado,:new.estado);
END TRG_AUDIT_T_PRUEBA_LOG;
/
Resultados de logs de transacciones
Se inserta varios registros y se realiza modificación en la tabla auditada (T_PRUEBA_LOG), como Insertar, actualizar o borrar datos
Datos tabla auditada
El tigger se ejecuta y registra el logs de las transacciones en la tabla de auditoria correspondiente (T_AUDIT_T_RUEBA_LOG)
Datos tabla Auditoria
En la anterior tabla se registra toda las transacciones que se realicen en la tabla T_PRUEBA_LOG, donde se conserva todo el historial de datos generado por los usuarios
El SITIO DE JCAVELLA 