2

I have an audit requirement where they want all database access (logins) logged. We have a very large distributed system of hundreds of mySQL servers (shards, slaves, slaves of slaves) that are serving a thousand queries per second. As a result, I can't just turn on general query logging as recommended in Audit logins on MySQL database, it'll be multiple GB/day per instance and kill our disk IO.

I've looked and see two options to do discretionary logging with filtering - an Oracle plugin and a McAfee plugin. The Oracle one requires you to be paying them $ for enterprise mySQL, and with hundreds of db servers I'm not really prepared to start doing that today. The McAfee one is brand new and I'm worried about stability on a large scale system like we have.

Any other good solutions to log only logins to our mySQL databases? It doesn't have to be integral to mySQL, we'd consider UNIX level port sniffing shenanigans if they'd scale.

Community
  • 1
Ernest Mueller
  • 121
  • 3

2 Answers2

3

I really wish I could take credit for this, but I can't. I chanced across this while thinking about your question:

http://www.fromdual.com/mysql-logon-and-logoff-trigger-for-auditing

It never occurred to me to call a stored procedure from init_connect... but I (somewhat enthusiastically, I might add) tested this solution before posting it here, and found that it works exactly as described, on off-the-shelf MySQL 5.5.30.

It logs every login of every user who lacks the SUPER privilege. This is a bit of a limitation, except, of course, for the fact that a SUPER user could manipulate the audit table anyway.

To capture the logouts with similar logic, you have to modify the server source, since init_connect does not have a built-in counterpart that gets executed at the end of the session.

Michael - sqlbot
  • 22,595
  • 2
  • 47
  • 75
  • 1
    The link is simply brilliant. It's bookmarked. I am glad you found it because it is the open source alternative to the audit plugin for MySQL (unfortunately available in MySQL Enterprise only : http://dev.mysql.com/doc/refman/5.5/en/audit-log-plugin-options-variables.html). Cheers and +1 !!! – RolandoMySQLDBA Mar 08 '13 at 02:36
  • That is pretty awesome and I will be playing around with this. One bug I noticed though: he even commented on the schema that thread id isn't suitable for a PK due to collisions that would ensure on a restart but then uses his admitidly non unique field to update at the end. I dislike the apparent need to grant execute on that for every single user. – atxdba Mar 08 '13 at 03:14
  • You shouldn't need to grant execute explicitly to every user. An entry in the mysql.db privilege table with Db set to 'audit', Host set to '%', and User set to '' (empty string) followed by FLUSH PRIVILEGES accomplishes the same purpose, because the empty string is a wildcard. Curiously and conveniently, if you don't have a ''@'%' user in your mysql.user table (and you shouldn't), then this grant doesn't show up in SHOW GRANTS for the user... but it still works. – Michael - sqlbot Mar 08 '13 at 04:30
  • Sweet, let me try this! – Ernest Mueller Mar 08 '13 at 22:36
1

I can imagine a way to manage the general logging for harvesting the logins could be accomplished with a script. The process is:

  • Turn on general logging
  • Your script runs on cron or is daemonized to periodically do the following
  • mv the general log to a temp name.
  • connect to mysql and runs flush logs. This will release the file handle it had on the old one and recreate a new log file
  • parse/grep through to harvest the information you want from Connect lines; store it somewhere
  • rm the temporary file
atxdba
  • 5,273
  • 5
  • 40
  • 60
  • This wouldn't really reduce the additional disk I/O incurred from full query logging though. It's not disk space that's the problem... – Ernest Mueller Mar 08 '13 at 01:48
  • Is the I/O problem something you've actually measured on your own setup? I know the docs say there will be a performance hit but until you measure for yourself you can't be certain. All the same, you could put logging on a separate array so not to impact disk i/o of the datadir. Do you have memory to spare? You could setup a memory ramdisk directory. – atxdba Mar 08 '13 at 01:53
  • Although I like both your answer and that of @Michaelsqlbot I lean a little towards your answer but with one caveat : Try making the general log a FIFO to another server : http://dba.stackexchange.com/a/5106/877. Then, there is never a blip for doing FLUSH LOGS;. – RolandoMySQLDBA Mar 08 '13 at 02:31
  • @RolandoMySQLDBA I actually like the answer Michaelsqlbot gave better. I'll be playing w/ it as well. – atxdba Mar 08 '13 at 03:19