You are here

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 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 |
+-----------+-----+------+------+-----------+

Comments

Does the statement run in its own transaction after the main connection terminates? For example: conn_begin BEGIN insert into some_table (...) /* connection is lost*/ (implicit rollback) audit_trigger_begin: BEGIN -- hope this doesn't commit the old work! INSERT INTO some_audit_table (...) COMMIT Does the SQL properly increment global status variables, etc? Also your triggers access the information schema a lot. Consider reading from STATUS_VARIABLES/GLOBAL_VARIABLES and pulling multiple values at once. The entire structure is materialized when you access the table so you might as well access as much data as you can in one pass.
Anonymouscomment

Hello Justin, Does the statement run in its own transaction after the main connection terminates? It looks like not! :( I tried 4 different cases:
  • KILL QUERY
  • KILL CONNECTION
  • kill <pid>
  • kill -9 <pid>
An in all 4 cases the changed values were still there after the termination (working with InnoDB). Does the SQL properly increment global status variables, etc? Yes. It looks like. And referring to your last comment: You are absolutely right! This was just a pilot or prototype. So far away from being optimal.
olicomment

Answering your question on IRC - yes, we can get it in MariaDB, although I'd prefer a slightly different interface. But anyway - we cannot discuss that in the blog, it's the wrong medium, right? But if you write to maria-developers@ we can continue there.
Anonymouscomment

Hi Sergei, OK I will do so... The problem with maria-developers@ is just that there is so much traffic I can oversee something which concerns me... Oli
admincomment

Hi, I have completed your audit_connect for logon but its not working for super users,any idea to log super user attempts in this process? Thank you, Selva
selvacomment