LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon