Access logging

[Dan Gardner]

We (and presumably many other MySQL users) are currently struggling with requirement 10.1 of the Payment Card Industry Data Security Standard ("Is all access to cardholder data, including root/administration access, logged?") without spending large amounts on 3rd party solutions.

It would be of huge benefit to us if such logging were added in MySQL Proxy since there currently appear to be no plans to add it to MySQL itself. Adding the Lua hooks for read_auth and read_auth_result looks beyond my current Lua interface knowledge but I have included a proof-of-concept diff below for simply printing the auth request and result to stdout.

Index: network-mysqld-proxy.c
===================================================================
--- network-mysqld-proxy.c (revision 60)
+++ network-mysqld-proxy.c (working copy)
@@ -1744,6 +1744,17 @@

g_string_assign(con->default_db, auth.db_name ? auth.db_name : "");

+ printf(
+ "(%d.%d.%d.%d:%d) Authentication request for user '%s' to database '%s'\r\n",
+ (ntohl(recv_sock->addr.addr.ipv4.sin_addr.s_addr) >> 24) & 0xff,
+ (ntohl(recv_sock->addr.addr.ipv4.sin_addr.s_addr) >> 16) & 0xff,
+ (ntohl(recv_sock->addr.addr.ipv4.sin_addr.s_addr) >> 8) & 0xff,
+ (ntohl(recv_sock->addr.addr.ipv4.sin_addr.s_addr) >> 0) & 0xff,
+ ntohs(recv_sock->addr.addr.ipv4.sin_port),
+ auth.user,
+ con->default_db->str
+ );
+
if (auth.user) g_free(auth.user);
if (auth.scramble_buf) g_free(auth.scramble_buf);
if (auth.db_name) g_free(auth.db_name);
@@ -1772,6 +1783,21 @@
/* we aren't finished yet */
if (packet->len != recv_sock->packet_len + NET_HEADER_SIZE) return RET_SUCCESS;

+ printf(
+ "(%d.%d.%d.%d:%d) Authentication ",
+ (ntohl(send_sock->addr.addr.ipv4.sin_addr.s_addr) >> 24) & 0xff,
+ (ntohl(send_sock->addr.addr.ipv4.sin_addr.s_addr) >> 16) & 0xff,
+ (ntohl(send_sock->addr.addr.ipv4.sin_addr.s_addr) >> 8) & 0xff,
+ (ntohl(send_sock->addr.addr.ipv4.sin_addr.s_addr) >> 0) & 0xff,
+ ntohs(send_sock->addr.addr.ipv4.sin_port)
+ );
+
+ if (packet->str[4] != 0) {
+ printf("FAIL: %s\r\n", packet->str + 13);
+ } else {
+ printf("OK\r\n");
+ }
+
/* send the auth result to the client */

network_queue_append_chunk(send_sock->send_queue, packet);