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.
