MySQL / MariaDB login audit

once in a while i need to review which database accounts are used, from what IPs connections arrive. MySQL / MariaDB does not have built-in mechanism [ unless you want to allow full query log ], but there’s quite easy way to get the data.

it’s done by:

creating a stored procedure writing basic info [ which user, from which IP, when logged in ] to a separate table,
calling that procedure on each login [ applies only to not-administrators ]

CREATE DATABASE mysql_audit;
USE mysql_audit;
CREATE TABLE `login_audit` (
  `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `who` varchar(128) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DELIMITER //
CREATE OR REPLACE PROCEDURE login_audit_trigger()
SQL SECURITY DEFINER
BEGIN
  INSERT INTO mysql_audit.login_audit(ts,who) VALUES (now(),user());
END;
//

DELIMITER ;

-- clean up old entries after 3 months
CREATE OR REPLACE EVENT cleanup_login_audit ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE ON COMPLETION PRESERVE DO DELETE FROM mysql_audit.login_audit WHERE ts<DATE_SUB(NOW(), INTERVAL 3 MONTH) ;

-- make sure to have events enabled - event_scheduler=ON in [mysqld] section of db's config

-- allow each existing user to call that procedure
SELECT User,Host FROM mysql.user;
-- repeat for each user
GRANT EXECUTE ON PROCEDURE mysql_audit.login_audit_trigger TO 'app'@'%';
-- ....
FLUSH PRIVILEGES;

SET GLOBAL init_connect='call mysql_audit.login_audit_trigger()';

-- once confirmed it works [ log in as non-admin user, run some query, check if mysql_audit.login_audit has new rows]
-- add this to my.cnf in mysqld section: 
-- init_connect="call mysql_audit.login_audit_trigger()"
-- and restart mysql

important – for as long as audit is enabled – we have to expicitly run GRANT EXECUTE as above for all database users created in the future; there’s no way to do it in a future-proof way.

based on https://fromdual.com/last-login-off-mysql-database-users and https://stackoverflow.com/questions/47034690/mysql-user-last-login-access-date-time

there’s also audit plugin for MySQL / PerconaDB – https://docs.percona.com/percona-server/8.0/management/audit_log_plugin.html

Leave a Reply Cancel reply