{"id":6523,"date":"2013-09-17T09:24:09","date_gmt":"2013-09-17T07:24:09","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=6523"},"modified":"2013-09-17T09:24:09","modified_gmt":"2013-09-17T07:24:09","slug":"introducing-audit_login-simple-mysql-login-logfile-based-auditing","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/introducing-audit_login-simple-mysql-login-logfile-based-auditing","title":{"rendered":"Introducing audit_login: simple MySQL login logfile based auditing"},"content":{"rendered":"<p><a href=\"https:\/\/github.com\/outbrain\/audit_login\">audit_login<\/a> is a simple MySQL login auditing plugin, logging any login or login attempt to log file in JSON format.<\/p>\n<p>It seems that audit plugins are <a href=\"http:\/\/serge.frezefond.com\/2013\/09\/news-mariadb-audit-plugin-beta-is-out\/\">all the rage<\/a> lately&#8230; We&#8217;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.<\/p>\n<p>However we quickly realized there is much more to be gathered by this info.<\/p>\n<p>In very short, you install this plugin onto your MySQL server, and your server starts writing into a text file called <strong>audit_login.log<\/strong> entries such as follows:<\/p>\n<blockquote>\n<pre>{\"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\"}\r\n{\"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\"}\r\n{\"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\"}\r\n{\"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\"}\r\n{\"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\"}\r\n{\"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\"}\r\n{\"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\"}\r\n{\"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\"}\r\n{\"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\"}\r\n{\"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\"}\r\n{\"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\"}<\/pre>\n<\/blockquote>\n<p>In the above your MySQL server is on <strong>gromit03<\/strong>, and is accepting connections from other hosts; some successful, some not. What kind of information can you gather from the above?<\/p>\n<ul>\n<li>You can tell how many connections are being created on your server<\/li>\n<li>Where they came from<\/li>\n<li>Where <strong>&#8216;root&#8217;<\/strong> connections come from<\/li>\n<li>Port scans (see last row) can be identified by no credentials. These don&#8217;t have to be port scans <em>per se<\/em>; any <strong>telnet localhost 3006<\/strong> followed by <strong>Ctrl+D<\/strong> will show the same. Typically these would be either load balancer or monitoring tools checks to see that the <strong>3306<\/strong> port is active.<\/li>\n<li>You can tell which accounts connect, and how many times<\/li>\n<li>And you can infer which accounts are stale and can be dropped &#8212; if an account does not connect within a week&#8217;s time, it&#8217;s probably stale (pick your own timeframe)<\/li>\n<\/ul>\n<p>The above is quite interesting on one host; but we have dozens. We&#8217;ve installed this plugin on all our MySQL servers, and we use <a href=\"http:\/\/logstash.net\/\"><strong>logstash<\/strong><\/a> to aggregate them. We aggregate to two destinations:<!--more--><\/p>\n<ul>\n<li>All logs are being sent over to <strong><a href=\"http:\/\/kibana.org\/\">kibana<\/a><\/strong>, where they are easily searchable via <a href=\"http:\/\/lucene.apache.org\/core\/\">lucene<\/a> queries<\/li>\n<li>They are also all aggregated into one CSV formatted logfile, rotating daily; this format makes it easier for me to <strong>grep<\/strong> and <strong>uniq -c<\/strong> and generally produce very valuable data by scripting.<\/li>\n<\/ul>\n<p>By aggregating the logs from multiple hosts, we are able to gather such insight as:<\/p>\n<ul>\n<li>How well our load balancers are distributing connections<\/li>\n<li>How our various applications servers connect on our multiple data centres to our MySQL hosts<\/li>\n<li>Catching misbehaving apps with hardcoded server IPs<\/li>\n<li>Catching naughty developers logging into our masters directly (we have more mechanisms now to prevent and audit such cases)<\/li>\n<li>Identifying differences in behaviour of monitoring tools across our data centres<\/li>\n<li>etc.<\/li>\n<\/ul>\n<h4>Free to use<\/h4>\n<p>The <strong>audit_login<\/strong> plugin has been made free to use, open source, licensed under GNU GPL v2. It is <a href=\"https:\/\/github.com\/outbrain\/audit_login\">publicly available in our github repository<\/a>, where more documentation on build and installation can be found.<\/p>\n<p>As MySQL plugins go, you must compile your plugin with your particular MySQL version. For you convenience, pre-compiled binaries are <a href=\"https:\/\/github.com\/outbrain\/audit_login\/tree\/master\/lib\">available in the repository<\/a> (at this time for <strong>5.5.32<\/strong> and <strong>5.5.21<\/strong> linux 64 bit; likely to add more)<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&#8230; We&#8217;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 [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[120,57,80,16],"class_list":["post-6523","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-development","tag-open-source","tag-plugin","tag-security"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1Hd","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6523","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=6523"}],"version-history":[{"count":16,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6523\/revisions"}],"predecessor-version":[{"id":6578,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6523\/revisions\/6578"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=6523"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=6523"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=6523"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}