MySQL logon and logoff trigger for auditing

A while ago I did some research about MySQL audit functionality and logon a and logoff triggers. MySQL and MariaDB provide a logon trigger in the form of the init_connect variable but no logoff trigger where most of the work for auditing would be done. When we would have a logoff trigger we could track the login and possibility some activity of a user and implement auditing functionality.

Yesterday when I was looking into the code for an answer to the question of one of my customers this research came into my mind again. Today I was a bit more curious and I tried to find a way to patch the MySQL code to get a logoff trigger. Luckily I was successful right away and I created the exit_connect variable which acts as the logoff trigger.

The patches for the logoff trigger you can find here.

What you can do with these patches you will see in the following example. First we create an audit schema with an audit table:

CREATE SCHEMA audit;
USE audit;

-- thread_id is no good PK, because of 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)
);

Then we create a stored procedure:

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 ;

Then we grant the EXECUTE privilege to ALL users which have to connect to this database:

GRANT EXECUTE ON PROCEDURE audit.login_trigger TO 'testuser'@'%';
GRANT EXECUTE ON PROCEDURE audit.logoff_trigger TO 'testuser'@'%';

And last we have to hook our login and logoff triggers into MySQL:

mysql> SET GLOBAL init_connect="CALL audit.login_trigger()";
mysql> SET GLOBAL exit_connect="CALL audit.logoff_trigger()";

This you should also make permanent in the my.cnf.

Then you can start and connecting and running some statements against you database and some reports against you audit table:

Which user connected most

SELECT user, COUNT(user) AS count
  FROM audit_connect
 GROUP BY user
 ORDER BY count DESC;

+-----------+-------+
| user      | count |
+-----------+-------+
| u3@master |   169 |
| u2@master |     2 |
| u1@master |     1 |
+-----------+-------+

Total, average, max and min connect time per user

SELECT user, MAX(logout_ts-login_ts) AS max, MIN(logout_ts-login_ts) AS min
     , AVG(ROUND(logout_ts-login_ts, 0)) AS avg, SUM(logout_ts-login_ts) AS total
  FROM audit_connect
 GROUP BY user;

+-----------+------+------+----------+-------+
| user      | max  | min  | avg      | total |
+-----------+------+------+----------+-------+
| u1@master |  220 |  220 | 220.0000 |   220 |
| u2@master |   17 |    0 |   8.5000 |    17 |
| u3@master |    2 |    0 |   0.0414 |     7 |
+-----------+------+------+----------+-------+

Which user did the most SELECT queries

SELECT user, SUM(com_select) AS cnt
  FROM audit_connect
 GROUP BY user
 ORDER BY cnt DESC;

+-----------+------+
| user      | cnt  |
+-----------+------+
| u3@master |  503 |
| u2@master |   29 |
| u1@master |    6 |
+-----------+------+

Which user sent the most traffic over the network

SELECT user, SUM(bytes_received) AS rcvd, SUM(bytes_sent) AS sent
  FROM audit_connect
 GROUP BY user;

+-----------+-------+-----------+
| user      | rcvd  | sent      |
+-----------+-------+-----------+
| u1@master |   242 | 358488916 |
| u2@master |  1046 |     16753 |
| u3@master | 23259 |     70808 |
+-----------+-------+-----------+

Which user was doing what in a certain time range

SELECT user, COUNT(*) AS cnt, SUM(com_select) AS sel
     , SUM(bytes_received) AS rcvd, SUM(bytes_sent) AS sent
  FROM audit_connect
 WHERE login_ts <= '2010-12-10 22:54:59' and logout_ts >= '2010-12-10 22:54:00'
 GROUP BY user
;

+-----------+-----+------+------+-----------+
| user      | cnt | sel  | rcvd | sent      |
+-----------+-----+------+------+-----------+
| u1@master |   1 |    6 |  242 | 358488916 |
| u3@master |  13 |   37 | 1721 |      5119 |
+-----------+-----+------+------+-----------+