MySql: Trigger de conexión para auditoría de conexiones

En mysql los triggers que existen son básicos y solo a nivel de tabla. No existe como en SqlServer o Oracle un trigger que permita cazar las conexiones que se abren y obtener cierta información complementaria referente a las sesiones.
Un mal ejemplo. Se puede dar el caso que en un entorno web tengamos un granja de servidores apache y por lo que sea a alguno se le va la olla. Empieza a abrir threads en nuestro mysql de forma masiva (por el motivo que sea) y nos colapsa el servidor porque no tenemos limitidas el número de conexiones simultaneas para ese usuario. ¿Que mal rollo no? Y si encima esto pasa cuando no estamos en la oficina nos podemos encontrar que no podemos saber mucho de lo que ha pasado, por ejemplo vemos el pico en cacti pero no tenemos detalle.

Para tener algo más y poder auditar cuando y quien abre conexiones, haremos lo siguiente:  

1. Creamos schema un schema (o no).

   create schema auditoria;
   use auditoria;

2. Creamos dentro la tabla donde almacenar datos.
   CREATE TABLE aud_conexiones (
        id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
       , thread_id       INT UNSIGNED NOT NULL DEFAULT 0
       , usuario            VARCHAR(64) NOT NULL DEFAULT 'unknown'
       , login_ts        TIMESTAMP NULL DEFAULT NULL);

3. Crearemos un procedure que inserte los datos de la sesión.
   DELIMITER //
   CREATE PROCEDURE auditoria.login_trigger()
   SQL SECURITY DEFINER
   BEGIN
   INSERT INTO auditoria.aud_conexiones (thread_id, user, login_ts)
   VALUES (CONNECTION_ID(), USER(), NOW());
   END;
   //
   DELIMITER ;

4. Llamaremos el procedure al conectar (variable global init_connect) y daremos permiso a un usuario para auditar.

   SET GLOBAL init_connect="CALL auditoria.login_trigger()";
   
   Hasta aquí bien. Ahora toca probar. Empezaremos con un usuario: le daremos permiso, probamos de hacer de la conexión y vemos si se cargan datos en la tabla.
   GRANT EXECUTE ON PROCEDURE auditoria.login_trigger TO 'user'@'host';
   FLUSH PRIVILEGES;

5. Auditar a todo el mundo. Para ello lo único que hay que hacer es patearse toda la tabla de usuarios o si en nuestra versión de MySql ya existe information_schema podemos ejecutar la siguiente consulta que nos “creara” los grants para todos los usuarios menos root:
   select concat("GRANT EXECUTE ON PROCEDURE auditoria.login_trigger TO '",user,"'@'",host,"';") from mysql.user where user <> 'root';
   Cogeremos el resultado, hacemos copy&paste y ejecutaremos todo el churro de consultas. Volvemos a hacer flush privileges y listo!

Pegas
El sistema es un poco cutre pero es útil y sencillo de poner en marcha. Lo que le falta aquí es montar un mecanismo de limpieza de la tabla para que no acabe adquiriendo un tamaño molesto y otra manera de automatizar el grant para cada nuevo usuario (por ejemplo con un nuevo procedure que nos de de alta los nuevos usuarios y nos haga el grant correspondiente). Tiempo al tiempo...

Aparte de la auditoría, es importante asegurarse de que la base de datos funciona de manera óptima. En este post se propone un método para ajustar los parámetros de MySQL para mejorar el rendimiento.