Con el crecimiento de Internet, y el desarrollo de sistemas de información bajo la arquitectura Cliente/Servidor, los sistemas de cómputo ,en general, están expuestos a múltiples amenazas, vulnerabilidades y ataques cada vez más complejos. Por lo tanto, es importante que las organizaciones implementen bitácoras (o archivos logs) para almacenar los sucesos que ocurren en el sistema. La información contenida en una bitácora es muy importante y útil cuando ocurre un incidente de seguridad o cuando se realiza una auditoría de sistemas.

Una bitácora puede registrar mucha información acerca de eventos relacionados con el sistema que la genera los cuales pueden ser:

  • Fecha y hora.
  • Host origen.
  • Usuario.
  • Actividad realizada.

La importancia de las bitácoras es la de recuperar información ante incidentes de seguridad, detección de comportamiento inusual, información para resolver problemas, evidencia legal, es de gran ayuda en las tareas de cómputo forense.

Enseguida plantearé un ejemplo de una bitácora desarrollada para la siguiente base de datos de MySQL, llamada proyecto, que tiene las tablas carrera, departamento y maestros.

CREATE DATABASE proyecto;

USE proyecto

CREATE TABLE IF NOT EXISTS `carrera` (`clave_carrera` int(11) NOT NULL, `nom_carrera` varchar(20) NOT NULL, `num_depto` int(11) NOT NULL, PRIMARY KEY (`clave_carrera`), KEY `num_depto` (`num_depto`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `departamento` ( `num_departamento` int(11) NOT NULL,`nombre_dept` varchar(20) NOT NULL, `jefe_num_tarjet` int(11) NOT NULL, PRIMARY KEY (`num_departamento`), KEY `jefe_num_tarjet` (`jefe_num_tarjet`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `maestros` (`num_tarjeta` int(11) NOT NULL DEFAULT ‘0’,`nombre` varchar(50) DEFAULT NULL, PRIMARY KEY (`num_tarjeta`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

La estructura de la tabla bitácora sería la siguiente:

CREATE TABLE IF NOT EXISTS `bitacora` (`id` int(11) NOT NULL AUTO_INCREMENT, `operacion` varchar(10) DEFAULT NULL, `usuario` varchar(40) DEFAULT NULL, `host` varchar(30) NOT NULL, `modificado` datetime DEFAULT NULL, `tabla` varchar(40) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

La bitácora debe registrar todos los movimientos (insertar, eliminar y modificar) que se realicen en las tablas de la base de datos. Para lograr lo anterior es necesario crear un trigger para que se ejecute después de la operación de insertar, otro para después de eliminar y el último para después de modificar para cada una de las 3 tablas de la base de datos. Los nueve triggers necesarios para que funcione la bitácora son los siguientes:

DROP TRIGGER IF EXISTS `bit_carr_ins`;
DELIMITER //
CREATE TRIGGER `bitacora` AFTER INSERT ON `carrera`
FOR EACH ROW INSERT INTO bitacora(host, usuario, operacion, modificado, tabla) VALUES (SUBSTRING(USER(), (INSTR(USER(),’@’)+1)), SUBSTRING(USER(),1,(instr(user(),’@’)-1)), “INSERTAR”, NOW(), “CARRERA”)
//

DROP TRIGGER IF EXISTS `bit_carr_upd`;
CREATE TRIGGER `bit_carr_upd` AFTER UPDATE ON `carrera`
FOR EACH ROW INSERT INTO bitacora(host, usuario, operacion, modificado, tabla) VALUES (SUBSTRING(USER(), (INSTR(USER(),’@’)+1)), SUBSTRING(USER(),1,(instr(user(),’@’)-1)), “ACTUALIZAR”, NOW(), “CARRERA”)
//

DROP TRIGGER IF EXISTS `bit_carr_del`;
CREATE TRIGGER `bit_carr_del` AFTER DELETE ON `carrera`
FOR EACH ROW INSERT INTO bitacora(host, usuario, operacion, modificado, tabla) VALUES (SUBSTRING(USER(), (INSTR(USER(),’@’)+1)), SUBSTRING(USER(),1,(instr(user(),’@’)-1)), “ELIMINAR”, NOW(), “CARRERA”)
//

DROP TRIGGER IF EXISTS `bit_depto_ins`;
CREATE TRIGGER `bit_depto_ins` AFTER INSERT ON `departamento`
FOR EACH ROW INSERT INTO bitacora(host, usuario, operacion, modificado, tabla) VALUES (SUBSTRING(USER(), (INSTR(USER(),’@’)+1)), SUBSTRING(USER(),1,(instr(user(),’@’)-1)), “INSERTAR”, NOW(), “DEPARTAMENTO”)
//

DROP TRIGGER IF EXISTS `bit_depto_upd`;
CREATE TRIGGER `bit_depto_upd` AFTER UPDATE ON `departamento`
FOR EACH ROW INSERT INTO bitacora(host, usuario, operacion, modificado, tabla) VALUES (SUBSTRING(USER(), (INSTR(USER(),’@’)+1)), SUBSTRING(USER(),1,(instr(user(),’@’)-1)), “ACTUALIZAR”, NOW(), “DEPARTAMENTO”)
//

DROP TRIGGER IF EXISTS `bit_depto_del`;
CREATE TRIGGER `bit_depto_del` AFTER DELETE ON `departamento`
FOR EACH ROW INSERT INTO bitacora(host, usuario, operacion, modificado, tabla) VALUES (SUBSTRING(USER(), (INSTR(USER(),’@’)+1)), SUBSTRING(USER(),1,(instr(user(),’@’)-1)), “ELIMINAR”, NOW(), “DEPARTAMENTO”)
//

DROP TRIGGER IF EXISTS `bit_mae_ins`;
CREATE TRIGGER `bit_mae_ins` AFTER INSERT ON `maestros`
FOR EACH ROW INSERT INTO bitacora(host, usuario, operacion, modificado, tabla) VALUES (SUBSTRING(USER(), (INSTR(USER(),’@’)+1)), SUBSTRING(USER(),1,(instr(user(),’@’)-1)), “INSERTAR”, NOW(), “MAESTROS”)
//

DROP TRIGGER IF EXISTS `bit_mae_upd`;
CREATE TRIGGER `bit_mae_upd` AFTER UPDATE ON `maestros`
FOR EACH ROW INSERT INTO bitacora(host, usuario, operacion, modificado, tabla) VALUES (SUBSTRING(USER(), (INSTR(USER(),’@’)+1)), SUBSTRING(USER(),1,(instr(user(),’@’)-1)), “ACTUALIZAR”, NOW(), “MAESTROS”)
//

DROP TRIGGER IF EXISTS `bit_mae_del`;
CREATE TRIGGER `bit_mae_del` AFTER DELETE ON `maestros`
FOR EACH ROW INSERT INTO bitacora(host, usuario, operacion, modificado, tabla) VALUES (SUBSTRING(USER(), (INSTR(USER(),’@’)+1)), SUBSTRING(USER(),1,(instr(user(),’@’)-1)), “ELIMINAR”, NOW(), “MAESTROS”)
//

El resultado que se espera de la bitácora se muestra en la siguiente imagen.

Resultado de la bitacora

Espero que puedan implementar sus propias bitacoras en todas  las bases de datos que implementen de ahora en adelante.

Powered By DT Author Box

Written by Mtro. Gustavo Reyes Hernández

Maestro en Gestión de Tecnologías de la Información
Investigador sobre el uso de las TIC’s en la educación

Crear una bitácora en MySQL
Tagged on:                             

32 thoughts on “Crear una bitácora en MySQL

  • ya se me venía figurando que era un rollo eso de la bitácora.. puesto que carga cada operación realiazada ya sea, insersión, actualiación y/o borrado… y esto por cada tabla uhhh no andaba tan perdida.. la lógica la tenía.. ahora tengo el código xD jeje
    gracias por el aporte 😉

  • ol@ prof,,,

    kon el rollo de la b.d,, ya hemos creado los disparadores,, ahora keremos probarlos,, pero para ello necesitamos insertar datos en la tabla,, logiko,,,, verad,, jejje,, pero no se puede,, nos manda el sigiente error,,,

    mysql> insert into historial_alumno(matricula,nombre,ape_pat,ape_mat,edad,sexo,id_ciudadnac,id_ciud
    addom,carrera,lugar_her) values (07510425, ‘aldo’, ‘perez’, ‘lopez’, 21, ‘f’, 30023, 3080);
    ERROR 1136 (21S01): Column count doesn’t match value count at row 1

    pensamos k kisas tenia k ver lo de las comillas simples,, hicmos varias pruebas teniamos la idea d ponerlas solo alos campos numericos,,,, pero,, no funciono,,,,

    agradeceriamos su valiosa ayuda,,,

      1. buenas tardes tengo mi tabla usuario y la bitacora ahora creo el trigger y me da este

        error #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘END’ at line 5

        CREATE TRIGGER bitacora AFTER UPDATE ON usuario
        FOR each row
        BEGIN
        INSERT INTO bitacora(codusuario , usuario, operacion , modificado, tabla ) VALUES(OLD.codusuario , OLD.operacion , NEW.codusuario, NEW.operacion, CURRENT_USER(), NOW() )
        END;

  • ola profesor……….. es cierto no los estoy ingresando jejeje ufffff, gracias x su ayuda ,asi pasa si no me corrige no me doy cuenta jjejej, graxias de nuevo

  • hola Profe Gustavo
    gracias al ejemplo de como realizar la bitacora y los triggers pude hacerlo bueno sobre todo gracias a la paciencia que me tubo y el apoyo ke me brindo cada vez que le pedia asesoria, Mil gracias Lic. Gustavo

    1. No tienes nada que agradecer, es un gusto contribuir a la educación y preparación profesional de alumnos como tu y tus compañeros. Las recompensas vendrán en tu desempeño profesional y me sentiré muy satisfecho cuando superes a tus maestros.

      Espero y sigas consultando este blog, voy a poner algunas aportaciones sobre base de datos distribuidas, ya platique con su maestra del proximo semestre y estaré al pendiente de ustedes en base de datos distribuidas, sobre todo en las practicas.

      A ponerle ganas porque tendrán una excelente maestra, pero esos sí, muy estricta.

      Exito en este fin de semestre

  • Saludos Profesor!

    lo felicito por este gran aporte, queria saber si puedo implementar eso mismo pero con php, lo de los trigger, y si el usuario que se ingresa es que uno coloca al ingresar al manejador de BD, pero si es una variable de sesion del sistema. Que me recomienda?

  • tengo un problema
    quiero realizar los registros de insercion modificacion y eliminacion de mi base de datos se que eso se los realiza cin bitacoras pero tambien quisiera saber como puedo saber que usuario a reakizado los cambios ya que los usuarios solo los controlo con sessiones en php gracias por la ayuda

  • hola una vez mas tu ejemplo me sirvio para mi BD , pero tengo un prblema , ya he definido un trigger en una tabla y cuando quiero crear otro me dice : que no esta permitido crear multiples triggers para un evento o algo asi,com solucionar eso??
    gracias

  • acabo de crear el primer trigger para (tuve que eliminar un trigger anterior,ya que no me permitia tener multimples triggeres para un evento en una misma tabla) ahora cuando inserto datos un mi tabla , se insertan en la tabla bitacora,pero la columna la columna host se inserta como NULL ,nose porque, no hubo ningun error en la ocmpilacion del trigger. por favor quisiera que me corrijan ese error. no tengo donde mas preguntar por eso insisto tanto.gracias

  • Excelente aporte Mtro. Gustavo Reyes Hernández!
    Es sumamente útil para el desarrollo de sistemas serios con estandares de calidad, se le agradece de todo corazón!
    Gracias!

  • Excelente aporte. Una consulta.
    Aparte que carga las operaciones Insert,Update y Delete tambien puede guardar los posibles errores(manejo de errores) que ocurre en el sistema web, como se capturaria .

  • disculpe este ejemplo ya que tengo echo toda esta información quiero insertar informacion en la tabla de carrera y departamento y no puedo… expero que me pueda auxiliar

  • Me parece buena sugerencia pero aun carece de mucho,deberia guardar valores insertados asi como valores actualizados y como estaba el registro antes de la actualizacion tambien una copia del registro eliminado. Se que esto genera mucho volumen de informacion a largo plazo, pero para operaciones criticas es necesario.

  • HOLA GUSTAVO
    encontre un bugg en el código justo aquí
    (SUBSTRING(USER(), (INSTR(USER(),’@’)+1)), SUBSTRING(USER(),1,(instr(user(),’@’)-1)), “ACTUALIZAR”, NOW(), “DEPARTAMENTO”)

    en —-> ’@’

    tienes una comilla i un apóstrofe
    se resuelve cambiando la comilla por el apostrofe

    ——–> ‘@’

  • Haber quien puede solucionar este error con mysql-server-5.1
    Error 1241 (21000): Operand should contain 1 column(s).
    Ya intente casi de todo, espero y sea algún error complicado…

  • es posible hacer una bitacora generica o triggers genericos??
    estoy trabajando sobre un base de datos con muchas tablas y crear para cada tabla los triggers para insertar, eliminar y modificar será muy agotador…
    o existe otra forma de hacerlo???

Leave a Reply

Follow

Get every new post delivered to your Inbox

Join other followers:

%d bloggers like this: