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.
