Introducing audit_login: simple MySQL login logfile based auditing

September 17, 2013

audit_login is a simple MySQL login auditing plugin, logging any login or login attempt to log file in JSON format.

It seems that audit plugins are all the rage lately... We've developed out simple plugin a month ago as part of our database securing efforts; by auditing any login or login attempt we could either intercept or later investigate suspicious logins.

However we quickly realized there is much more to be gathered by this info.

In very short, you install this plugin onto your MySQL server, and your server starts writing into a text file called audit_login.log entries such as follows:

{"ts":"2013-09-11 09:11:47","type":"successful_login","myhost":"gromit03","thread":"74153868","user":"web_user","priv_user":"web_user","host":"web-87.localdomain","ip":"10.0.0.87"}
{"ts":"2013-09-11 09:11:55","type":"failed_login","myhost":"gromit03","thread":"74153869","user":"backup_user","priv_user":"","host":"web-32","ip":"10.0.0.32"}
{"ts":"2013-09-11 09:11:57","type":"failed_login","myhost":"gromit03","thread":"74153870","user":"backup_user","priv_user":"","host":"web-32","ip":"10.0.0.32"}
{"ts":"2013-09-11 09:12:48","type":"successful_login","myhost":"gromit03","thread":"74153871","user":"root","priv_user":"root","host":"localhost","ip":"10.0.0.111"}
{"ts":"2013-09-11 09:13:26","type":"successful_login","myhost":"gromit03","thread":"74153872","user":"web_user","priv_user":"web_user","host":"web-11.localdomain","ip":"10.0.0.11"}
{"ts":"2013-09-11 09:13:44","type":"successful_login","myhost":"gromit03","thread":"74153873","user":"web_user","priv_user":"web_user","host":"web-40.localdomain","ip":"10.0.0.40"}
{"ts":"2013-09-11 09:13:51","type":"successful_login","myhost":"gromit03","thread":"74153874","user":"web_user","priv_user":"web_user","host":"web-03.localdomain","ip":"10.0.0.03"}
{"ts":"2013-09-11 09:14:09","type":"successful_login","myhost":"gromit03","thread":"74153875","user":"web_user","priv_user":"web_user","host":"web-40.localdomain","ip":"10.0.0.40"}
{"ts":"2013-09-11 10:55:25","type":"successful_login","myhost":"gromit03","thread":"74153876","user":"web_user","priv_user":"web_user","host":"web-87.localdomain","ip":"10.0.0.87"}
{"ts":"2013-09-11 10:55:59","type":"successful_login","myhost":"gromit03","thread":"74153877","user":"web_user","priv_user":"web_user","host":"web-12.localdomain","ip":"10.0.0.12"}
{"ts":"2013-09-11 10:55:59","type":"failed_login","myhost":"gromit03","thread":"74153878","user":"(null)","priv_user":"(null)","host":"(null)","ip":"10.0.0.1"}

In the above your MySQL server is on gromit03, and is accepting connections from other hosts; some successful, some not. What kind of information can you gather from the above?

  • You can tell how many connections are being created on your server
  • Where they came from
  • Where 'root' connections come from
  • Port scans (see last row) can be identified by no credentials. These don't have to be port scans per se; any telnet localhost 3006 followed by Ctrl+D will show the same. Typically these would be either load balancer or monitoring tools checks to see that the 3306 port is active.
  • You can tell which accounts connect, and how many times
  • And you can infer which accounts are stale and can be dropped -- if an account does not connect within a week's time, it's probably stale (pick your own timeframe)

The above is quite interesting on one host; but we have dozens. We've installed this plugin on all our MySQL servers, and we use logstash to aggregate them. We aggregate to two destinations:

  • All logs are being sent over to kibana, where they are easily searchable via lucene queries
  • They are also all aggregated into one CSV formatted logfile, rotating daily; this format makes it easier for me to grep and uniq -c and generally produce very valuable data by scripting.

By aggregating the logs from multiple hosts, we are able to gather such insight as:

  • How well our load balancers are distributing connections
  • How our various applications servers connect on our multiple data centres to our MySQL hosts
  • Catching misbehaving apps with hardcoded server IPs
  • Catching naughty developers logging into our masters directly (we have more mechanisms now to prevent and audit such cases)
  • Identifying differences in behaviour of monitoring tools across our data centres
  • etc.

Free to use

The audit_login plugin has been made free to use, open source, licensed under GNU GPL v2. It is publicly available in our github repository, where more documentation on build and installation can be found.

As MySQL plugins go, you must compile your plugin with your particular MySQL version. For you convenience, pre-compiled binaries are available in the repository (at this time for 5.5.32 and 5.5.21 linux 64 bit; likely to add more)

tags: , , ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

8 Comments to "Introducing audit_login: simple MySQL login logfile based auditing"

  1. Todd Farmer wrote:

    Hi Shlomi,

    Cool stuff! As a suggestion, you might consider including the error ("status") code in the output, rather than interpreting it as a successful or unsuccessful login - I've found it very useful when using the MySQL Enterprise Audit Log plugin (with audit_log_policy=LOGINS) and I want to identify particular error conditions (is it a bad password, or a blocked host, or is an authentication plugin not supported on the client, or ...).

  2. shlomi wrote:

    Todd -- thanks! Will look into this.

  3. tlastowka wrote:

    Hey shlomi,

    When trying to compile the plugin using the instructions on github, the same error is being thrown using source from 5.5.33 and 5.6.14. This is under Centos 6.4 x64. Any ideas?

    Thanks!

    Scanning dependencies of target audit_login
    [ 68%] Building C object plugin/audit_login/CMakeFiles/audit_login.dir/audit_log
    In file included from /home/tlastowka/src/mysql-5.5.33/include/my_sys.h:19,
    from /home/tlastowka/src/mysql-5.5.33/plugin/audit_login/audit_
    /home/tlastowka/src/mysql-5.5.33/include/my_global.h:522: error: redefinition of
    /home/tlastowka/src/mysql-5.5.33/include/mysql.h:66: note: previous declaration
    In file included from /home/tlastowka/src/mysql-5.5.33/include/my_sys.h:19,
    from /home/tlastowka/src/mysql-5.5.33/plugin/audit_login/audit_
    /home/tlastowka/src/mysql-5.5.33/include/my_global.h:939: error: redefinition of
    /home/tlastowka/src/mysql-5.5.33/include/mysql.h:127: note: previous declaration
    /home/tlastowka/src/mysql-5.5.33/include/my_global.h:995: error: redefinition of
    /home/tlastowka/src/mysql-5.5.33/include/mysql.h:52: note: previous declaration
    make[2]: *** [plugin/audit_login/CMakeFiles/audit_login.dir/audit_login.c.o] Err
    make[1]: *** [plugin/audit_login/CMakeFiles/audit_login.dir/all] Error 2
    make: *** [all] Error 2

  4. shlomi wrote:

    Not off the top of my head, and will take a couple days before I can look into this...

  5. tlastowka wrote:

    I didn't get too deep into the code, but I tried the old "comment out once of the lines that gets mentioned and see how it changes the error messages" trick.

    When I commented out line 46, #include the compile completed just fine... and the plugin worked, again on both versions.

    I wasn't expecting that to happen. I'm a little worried about what else that might have impacted, but I'll run it for a few days and post back if anything unexpected crops up.

  6. egezon wrote:

    This is really cool. I have couple of questions though.
    By default this is logging into data directory.
    1. How can I change the directory? and
    2. How can I rotate log(s)?

  7. shlomi wrote:

    1. Sorry, only logging to data directory; no configuration to modify that
    2. Via logrotate, e.g. like this:

    #
    # Rotate audit_login logs
    #

    /path/to/datadir/audit_login.log {
    daily
    compress
    create 660 mysql mysql
    missingok
    ifempty
    copytruncate
    rotate 7
    }

  8. egezon wrote:

    Awesome! thank you.

Leave Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org