LLI Wiki — My Digital Knowledge Base

MySQL: audit logins and logouts

Audit MySQL connections with init_connect, an audit table and stored procedures, or with the general log.

MySQL has no native logon/logoff trigger. You can still audit connections with the init_connect variable, which runs a statement each time a user connects, or enable the general_log for a simple trace. The approach follows this FromDual article.

List existing triggers

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;

Connection audit table

CREATE SCHEMA audit;
USE audit;

-- thread_id is not a good primary key: it restarts after a server restart
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)
);

Login and logoff procedures

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 ;

Check that the procedures exist:

SELECT routine_name
FROM information_schema.routines
WHERE routine_type = 'PROCEDURE'
  AND routine_schema = 'audit';

Privileges and activation

Grant EXECUTE to every user who connects to the instance, then register the 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()";

Simple alternative: the 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';

List the users who connected, from the log (here /var/log/mysqld_gen.log):

cat /var/log/mysqld_gen.log | awk '/Connect/ && ! /Query/ && gsub(/\t\t/, "") {print $3}' | sort | uniq

Notes

Mastodon LLIMastodon SocialFosstodon