ACTIVIDAD #16

Que es una bitacora ( relacionada con SGBD):

Herramienta que permite registrar, analizar detectar y notificar eventos que suceden en cualquier sistemas de informacion utilizado en las organizaciones. La estructura mas ampliamente usada para grabar las modificaciones de la BD.

Cuales son sus funciones

  Recuperacion de informacion ante incidentes de seguridad.de cual es la seguridad, deteccion de comportamiento importancia de inudual, informacion para resolver una bitacora, problemas evidencia legal, es de gran ayuda en las tareas de computo forense

Tipo  de Bitacoras  

MYSQL:

•  el registro de error: Registra problemas encontrados iniciando, ejecutando o parando mysql.
• Registro de consultras: Registra las conexiones de clientes establecidas, y las sentencias ejecutadas.
• Registro de Actualizaciones (The Update Log): Registra las sentencias que cambian datos. este registro esta ya en desuso.
• Registro Binario: Registra todas las sentencias que cambian datos. Tambien utilizando para replicacion.
• Registro de Lentitud: Registra todas las sentencias que tardaron mas de long_query_time segundos en ejecutarse, o no utilizaron indices.

ORACLE:

•  Control (ctl): almacenan informacion acerca de la estructura de archivos de la base.
• Rollback (rbs): cuando se modifica el valor de alguna tupla en una transaccion, los valores nuevos y anteriores se almacenan en un archivo, de modo que si ocurre algun error, se puede regresar (rollback) a un estado anterior.
• Redo (rdo): bitacora de toda transaccion, en muchos dbms incluye todo tipo de consulta incluyendo aquellas que no modifican los datos.
• Datos (dbf): el tipo mas comun, almacena la informacion qye es accesada en la BD.
• Indices (dbi): archivos hermanos de los datos para acceso rapido.
• Temp (tmp): localidades en disco dedicadas a operaciones de ordenamiento o alguna actividad particular que requiera espacio temporal adicional.

Como crearlas en SGBD ( Mysql y Oracle)

Se plantea 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”)

 BITACORA EN ORACLE

Miramos que actualmente no está activada la auditoria en la base de datos.

SQL> select name , value from v$parameter where name like 'audit_trail';
audit_trail NONE

Activamos la auditoría de la base de datos

SQL> alter system set audit_trail = DB scope = spfile;

Reiniciamos la base de datos ( shutdown immediate, startup ) y comprobamos que la auditoría se ha activado.

SQL> select name , value from v$parameter where name like 'audit_trail';
audit_trail DB

Activamos la auditoría para ver la conexión y desconexión de los usuarios a la base de datos, se hace con la siguiente sentencia

SQL> audit connect;

Fuente

http://www.slideshare.net/LaloOsorio1/bitcora-de-base-de-datos 

http://dev.mysql.com/doc/refman/5.0/es/log-files.html 

http://bases-datos-2013.blogspot.mx/2013/02/arquitectura-de-sgbd.html