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
- The general log records every statement: it grows very fast, enable it only temporarily.
init_connectis not executed for accounts with theSUPER(orCONNECTION_ADMIN) privilege.- The
exit_connectlogoff hook comes from the source article; check that your MySQL version/distribution supports it before relying on it.