Besides many other features, MySQL 5.5 adds a new plugin type—Audit plugin. As the name suggests, it allows us to do auditing and logging of whatever happens in the server. At certain points, the MySQL server emits audit events. An audit plugin can subscribe to receive them, all or only a subset, for further processing. Let's look at what the audit event looks like:

struct mysql_event
{
unsigned int event_class;
};

Every audit event is characterized by its class. The event structure may have more members, but what they are depends on the event class.

Now, what makes the audit plugin API different from all other plugin type APIs—it is not feature complete. It does not try to anticipate all possible audit use cases and generate all possible audit events for everything that anyone may want to audit some day. Instead, MySQL developers (including one of the authors of this book) have only implemented one audit event class—general—as new audit classes can be added later, when they will be needed for real, not hypothetical, plugins. The event of the general audit class is defined as:

#define MYSQL_AUDIT_GENERAL_LOG 0
#define MYSQL_AUDIT_GENERAL_ERROR 1
#define MYSQL_AUDIT_GENERAL_RESULT 2
struct mysql_event_general
{
unsigned int event_class;
unsigned int event_subclass;
int general_error_code;
unsigned long general_thread_id;
const char *general_user;
unsigned int general_user_length;
const char *general_command;
unsigned int general_command_length;
const char *general_query;
unsigned int general_query_length;
struct charset_info_st *general_charset;
unsigned long long general_time;
unsigned long long general_rows;
};

All events of the general class are sorted into one of the three subclasses—log, error, and result. Events of the log subclass are emitted when a statement is received by the MySQL server, but before its execution starts. It is also the point when logging into the MySQL general query log takes place. Events of the error subclass are emitted when an error happens during the execution of the SQL statements. The general_error_code member of the mysql_event_general structure is non-zero only for the events of this subclass. Finally, events of the result subclass are emitted when the execution of the SQL statement is finished, almost where logging into the slow query log takes place. The general_time and general_rows members are defined only for events of this subclass.

Let's look at a simple audit plugin to understand the API better:

mysql_declare_plugin(securlog)
{
MYSQL_AUDIT_PLUGIN,
&securlog_struct,
"SecurLog",
"Sergei Golubchik",
"Log Security Violations",
PLUGIN_LICENSE_GPL,
NULL,
NULL,
0x0001,
NULL,
NULL,
NULL
}
mysql_declare_plugin_end;

The audit plugin, like any other plugin, must be declared with the help of the mysql_declare_plugin macro. The only element specific to auditing here is the pointer to the securlog_struct structure—the descriptor of our audit plugin:

static struct st_mysql_audit securlog_struct =
{
MYSQL_AUDIT_INTERFACE_VERSION,
NULL,
securlog_log,
{ MYSQL_AUDIT_GENERAL_CLASSMASK }
};

The audit plugin descriptor starts from the API version number—any plugin descriptor structure starts from that, independently from the plugin type. The last member in the structure is the bitmap of the event classes we are interested in. In this way we declare what events we want to see, and MySQL will do the filtering for us. Of course, there is only one event class for now, so there is not much to filter. We specify that we want to see events in the general class.

The third member is the most important one—it is a pointer to the function that will be called for every event, a notification function, securlog_log(). This function takes two arguments, the calling THD and the event descriptor of the mysql_event type. The second member is a pointer to the release function—the function that should remove any dependency that exists between THD and the plugin, as if the THD would be destroyed the very next moment. For example, if our notification function wants to cache data in the THD, it needs to invalidate the cache here. If we ever allocate the memory and store the pointer in THD, we would have to free it now. And if we use the thd_alloc service to allocate memory in the current thread memory pool, we should consider this memory to be gone after the release function is called.

However, we are not going to do anything like that in this example, and our release function pointer is NULL. We just want to log all of the attempts to violate a security policy—that is, all cases when somebody tries to access a database, a table, or a column that he has no right to. It can be done easily by intercepting the error subclass of events and looking for all error codes that can be used to deny access to a resource:

static void securlog_log(MYSQL_THD thd,
const struct mysql_event *ev)
{
struct tm t;
const struct mysql_event_general *event = ev;
switch (event->general_error_code) {
case ER_ACCESS_DENIED_ERROR:
case ER_DBACCESS_DENIED_ERROR:
case ER_TABLEACCESS_DENIED_ERROR:
case ER_COLUMNACCESS_DENIED_ERROR:
localtime_r(&event->general_time, &t);
fprintf(stderr, "%04d-%02d-%02d %2d:%02d:%02d " "[%s] ERROR %d: %s\n", t.tm_year + 1900, t.tm_mon + 1, t.tm_mday, t.tm_hour, t.tm_min, t.tm_sec, event->general_user, event->general_error_code, event->general_command);
}
}

As we can see, it is as simple as it can be—for all matching error codes it prints a log entry with the time of the event, username, the error code, and the error message.