MySQL Bugs: #28377: Add connection auditing log

Bug #28377	Add connection auditing log
Submitted:	11 May 2007 13:58	Modified:	23 Jan 2013 13:02
Reporter:	Baron Schwartz (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S4 (Feature request)
Version:	all	OS:	Any
Assigned to:	Geir Høydalsvik	CPU Architecture:	Any
Tags:	audit, Connection, Contribution, forensics, log, qc

Description:
I would like MySQL Server to provide more features for auditing. Some features I miss after using a proprietary database for a while, and others I just want anyway. I will start by describing what I need.

When I look at a binlog and see a misbehaving application, I need to find out who the user was, when they logged in, from where, and so forth. If this is much after the fact, this can currently be difficult to do. When I use innotop or mytop and see a misbehaving connection, I sometimes need to find out immediately the same information.

For more context, I have written about my own solutions to this problem, which I don't think are very good, here: http://www.xaprb.com/blog/2006/07/23/how-to-track-what-owns-a-mysql-connection/

How to repeat:
Feature Request

Suggested fix:
First, I realize --init-connect does help with this a little, but not that much. It really only lets me persist some limited information beyond the time of the connection closing, and not universally (it doesn't execute for users with SUPER privilege, which is often who I most need to audit).

I can think of two ways to accomplish my goals.

First is to simply log all connections, with all information, to a separate log file. This can be done with the general log, but as far as I know not otherwise, and the general log isn't something I will enable on a busy server. This log would only record connections and disconnections, along with the information about who/what/where/what port/etc. This would not give all the information I want, but it would at least get it to disk in a way that would help me later. As an alternative, perhaps we could just use the general log, but make it configurable, so not all queries go to it; I could just select certain events to write to it (in my case, login and logout).

Second would be to add some arbitrary user-specifiable meta-data when connecting, which the server would just store with the connection. This is something I could do with Microsoft SQL Server; basically a connection-string comment. For instance, when I make a connection from client code, I could identify in the connection-string comment the application, code module, purpose, or other things in the comment. This would ideally go to the logs I've mentioned, but even if not, I'd be happy with being able to see it in SHOW FULL PROCESSLIST.

In terms of difficulty, the first idea seems easiest to me. The second seems hardest because I imagine it would involve changing the client-server protocol.

In general, I think MySQL Server should have much more auditing capability (probably disabled by default), but this is an 80% solution for me with probably 1% of the work.

If I try to make a patch to disable queries in the general query log, and only leave connect/disconnect enabled, would MySQL be interested in the patch?

Thanks for your consideration.

Thank you for a reasonable feature request.

see related bug #30924

+1 from my company. Our information security group is asking us to make sure all user logins are logged to help us understand who was in the system at any given time. I agree with the related issue (binlog who and from what host activity was done from) as well especially since it's important to understand who did something malicious, not just what was done.

This is something that's important enough to us that we'd ask that it get bumped to 5.5.

might help partially to write some audit plugin:
http://dev.mysql.com/doc/refman/5.5/en/writing-audit-plugins.html

Fixed by the addition of connection attributes. See
http://dev.mysql.com/doc/refman/5.6/en/performance-schema-connection-attribute-tables.html for more details.