MySQL : auditer les connexions et déconnexions
Auditer les connexions MySQL avec init_connect, une table d'audit et des procédures, ou avec le general log.
MySQL n'a pas de trigger de connexion/déconnexion natif. On peut toutefois auditer les connexions avec la variable init_connect, qui exécute une instruction à chaque connexion d'un utilisateur, ou activer le general_log pour une trace simple. Principe inspiré de cet article FromDual.
Lister les triggers existants
SELECT trigger_schema,
trigger_name,
event_object_table AS table_name,
event_manipulation,
action_statement AS trigger_statement
FROM information_schema.triggers
WHERE trigger_schema = 'your_schema_name'
ORDER BY table_name,
trigger_name;
Table d'audit des connexions
CREATE SCHEMA audit;
USE audit;
-- thread_id n'est pas une bonne clé primaire : il est réinitialisé au redémarrage
CREATE TABLE audit_connect (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, thread_id INT UNSIGNED NOT NULL DEFAULT 0
, user VARCHAR(64) NOT NULL DEFAULT 'unknown'
, login_ts TIMESTAMP NULL DEFAULT NULL
, logout_ts TIMESTAMP NULL DEFAULT NULL
, com_select INT UNSIGNED NOT NULL DEFAULT 0
, bytes_received BIGINT UNSIGNED NOT NULL DEFAULT 0
, bytes_sent BIGINT UNSIGNED NOT NULL DEFAULT 0
, KEY (thread_id)
);
Procédures de login et de logoff
DROP PROCEDURE IF EXISTS audit.login_trigger;
DROP PROCEDURE IF EXISTS audit.logoff_trigger;
DELIMITER //
CREATE PROCEDURE audit.login_trigger()
SQL SECURITY DEFINER
BEGIN
INSERT INTO audit.audit_connect (thread_id, user, login_ts)
VALUES (CONNECTION_ID(), USER(), NOW());
END;
CREATE PROCEDURE audit.logoff_trigger()
SQL SECURITY DEFINER
BEGIN
DECLARE com_select INT DEFAULT 0;
DECLARE bytes_received INT DEFAULT 0;
DECLARE bytes_sent INT DEFAULT 0;
SELECT variable_value
INTO com_select
FROM INFORMATION_SCHEMA.session_status
WHERE variable_name = 'COM_SELECT';
SELECT variable_value
INTO bytes_received
FROM INFORMATION_SCHEMA.session_status
WHERE variable_name = 'BYTES_RECEIVED';
SELECT variable_value
INTO bytes_sent
FROM INFORMATION_SCHEMA.session_status
WHERE variable_name = 'BYTES_SENT';
UPDATE audit.audit_connect
SET logout_ts = NOW(), com_select = com_select
, bytes_received = bytes_received, bytes_sent = bytes_sent
WHERE thread_id = CONNECTION_ID();
END;
//
DELIMITER ;
Vérifier la création des procédures :
SELECT routine_name
FROM information_schema.routines
WHERE routine_type = 'PROCEDURE'
AND routine_schema = 'audit';
Droits et activation
Donner le droit EXECUTE à tous les utilisateurs qui se connectent à l'instance, puis déclarer les hooks :
GRANT EXECUTE ON PROCEDURE audit.login_trigger TO 'testuser'@'%';
GRANT EXECUTE ON PROCEDURE audit.logoff_trigger TO 'testuser'@'%';
SET GLOBAL init_connect = "CALL audit.login_trigger()";
SET GLOBAL exit_connect = "CALL audit.logoff_trigger()";
Alternative simple : le general log (MySQL 8)
SHOW VARIABLES LIKE "general_log%";
SHOW VARIABLES LIKE "%slow_query_log_file%";
SHOW VARIABLES LIKE "%slow_query_log%";
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log = 'OFF';
Lister les utilisateurs qui se sont connectés d'après le log (ici /var/log/mysqld_gen.log) :
cat /var/log/mysqld_gen.log | awk '/Connect/ && ! /Query/ && gsub(/\t\t/, "") {print $3}' | sort | uniq
Remarques
- Le general log enregistre toutes les requêtes : il grossit très vite, à n'activer que ponctuellement.
init_connectn'est pas exécuté pour les comptes disposant du privilègeSUPER(ouCONNECTION_ADMIN).- Le hook de déconnexion
exit_connectvient de l'article source ; vérifier qu'il est disponible dans votre version/distribution de MySQL avant de s'y fier.