Monday, February 15, 2010

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

2 comments:

Shlomi Noach said...

Hi,
This is a cool hack!
One can simplify it by not writing to the error log but to some internal log table, in which case you can use a normal stored function, no need for UTF.

To complicate the simplification:
You can later on purge rows from that log file and do stuff with, like appending them to the error log, using external script.

Shinguz said...

Hi Shlomi,

Thanks a lot for your input!

I am not a security specialist so I do not know what those guys who implement audit solutions think about your approach. But I can imagine that moving login information away immediately gives less possibilities to manipulate those data.

But as SUPER users are not logged it is halve baked anyway.

Using your approach in combination with the federated or federatedX Storage Engine would give again the possibility to store the information remote.

Regards,
Oli