Logging users to the MySQL error log

Problem

A customer recently showed up with the following problem:

With your guidelines [1] I am now able to send the MySQL error log to the syslog
and in particular to an external log server.
But I cannot see which user connects to the database in the error log.

How can I achieve this?

Idea

During night when I slept my brain worked independently on this problem and in the morning he had prepared a possible solution for it.

What came out is the following:

• We create an UDF which allows an application to write to the MySQL error log.
  See my previous article about this [2].
  
• We specify in a simple SQL query how the string should look which we want to write to the MySQL error log file.
  
• We use the init_connect [3] hook (= logon trigger) which MySQL provides to log the information to the error log.
  

How to solve it?

The UDF can be taken from [4]. Be not confused by the version number. It just worked with MySQL 5.1.42. Load the UDF according to the article into the MySQL database. Follow the little example there and if it works lets continue to the next step.

The SQL query to form the MySQL error log string looks as follows:

  mysql> SELECT CONCAT('[Security] User ', USER(), ' logged in.');

And if executed with the function:

  mysql> SELECT log_error(CONCAT('[Security] User ', USER(), ' logged in.'));

it produces the following output to the MySQL error log file:

  shell> tail -n 1 error.log
       100215 17:50:16 [Security] User oli@localhost logged in.

And now make this permanent for every user which does not have SUPER privileges:

  #
  # my.cnf
  #
  [mysqld]
  init_connect = 'SELECT log_error(CONCAT("[Security] User ", USER(), " logged in."));'

restart the database and it should work now (it could also work with just SET GLOBAL init_connect=...).

Caution

Please consider the MySQL documentation [3] and be aware of the following:

"Note that the content of init_connect is not executed for users that have the SUPER privilege."

Further I want to warn you that I have NOT tested the impact on stability and performance of this method! Please test it carefully yourself an let me know if you find something or also if it works smoothly for you.

This is part of the MySQL Auditing Package we are currently working on and we hope to finish it soon. If you are interested in this work please let us know and our MySQL consultants are happy to help you implementing your own MySQL auditing in your environment.

Literature

[1] MySQL reporting to syslog
[2] MySQL useful add-on collection using UDF
[3] MySQL documentation: init_connect
[4] UDF collection