WL#3771: Pluggable Audit Interface

Affects: Server-5.5   —   Status: Complete   —   Priority: Low

Make it possible to log server "events" (using a customizable plugin).
"Events" include things like a) queries executed, b) query errors, 
c) summary of result set.

Declare a new kind of plug-in to handle audit events. Audit plug-ins are able to 
specify a subset of classes of events that they wish to receive.

The scope of this task is to:
* Provide an abstract interface for audit plug-ins.
* Provide the mysqld implementation of the interface.

It is not expected that any audit plug-ins are to be created as a result of this 
worklog other than the minimum example required to demonstrate the proper 
functioning of the interface.

As part of the mysqld implementation of the interface, it is expected that:
* minimal intrusion within the existing code.
* minimal runtime costs: Benchmark result differences should be indiscernible
from noise.

Further worklogs shall be created for optional log plug-ins as required.

There should be an interface where at any time, code within mysqld may call a 
function to notify that some significant auditable event has occurred.

The server should examine if there are any installed audit plug-ins which are 
interested in the event and broadcast the event to them as required.

At the time when the server is to wait upon the client, the server should 
that all acquired resources are appropriately released.

All auditable events are divided into event classes and each event class may 
have one or more event subtypes. For each event class, the parameters to the 
event shall be the same for every subtype.

All audit plug-ins shall provide a bit-mask to indicate which event classes 
require to be notified. Every audit plug-in will service all event subtypes for 
all event classes which they have indicated interest. An audit plug-in will not 
be notified for any event class of which they have not indicated interest.


* Connection class: connect, login, logout, disconnect, etc.
* Alter Privilege class: GRANT, REVOKE etc.
* General Query class

(additional classes to be declared as required based upon requirements)


an example audit plugin will be created to demonstrate the proper 
functioning of the interface and to allow benchmarking. It'll listen for all
events and will discard everything it receives (blackhole).

COMMENT: from Brian Miezejewski

We have storage engine partners that would like to use this interface to capture
statements that do not normally make it to the storage engine such as GRANT,
REVOKE, create VIEW, etc. They would like to get the parse tree so that they do
not have to reparse the SQL to use it. They also need access to the thd->command
value to help to quickly determine if the statement is of interest or not. 

Those storage engine partners can not use the GQ log for many reasons, 
primarily the security issues that it raises in what it writes to disk, the 
overhead of managing it and its impact on performance. It will be easy for us 
to create a  plugin using the logging plugin API that does what they want. We 
have been sitting on this work for several months simply waiting to get this 
API implmented. 

They want to audit the following information below:

- Login/Access Timestamp
- Database username
- Client IP/Hostname or terminal name
- Client OS username
- Client program name and process ID
- Accessed table/view/other objects name and action
- Full executed query string

- Failed login/access information
- The number of affected/retrieved rows
- Values of prepared statement bind variables

I suspect that logging OS username,program name and process ID is not worth
implementation because it seems difficult to implement and easy-to-fake.


This looks to resolve the needs of the clients. The only potential issue is if
some of the needed event classes are missing or if they might not be collected
at the right point in time. As long as we can audit every auditable event
everywhere it fails or succeeds we will be fine.

Within the mysqld server, the audit interface visible within the server is 
declared as:

void mysql_audit_notify(THD *, event_class, event_subtype, error_code, ...);

This may be encapsulated in a macro for conditional compialation for specific 
builds which have no need for auditing functionality - such as embedded builds.
Macros and/or inline functions should be considered for each audit class to 
ensure correctness of parameters.

The server code is free to call mysql_audit_notify() at any time during its 
execution as long as all arguments are valid.


we will create two hooks in this WL:

- general query log, a hook where a general query logging happens, with the same
set of parameters.

- error/security log - in the my_message_sql. my_error* functions that call it
(via error_handler_hook) will pass va_list args to my_message_sql. The latter
will call mysql_audit_notify().  If the error is security related - unpacking
the va_list into an appropriate structure (to help in the common case),
otherwise sending va_list as is.


struct audit_plugin_interface {
  uint64 *class_mask;
  void (*release_thd)(MYSQL_THD);
  void (*event_notify)(MYSQL_THD, struct mysql_event *);

  uint64 bitmask of event classes

  release any resources which may have been allocated for client thread.

  Notify the plugin of an event for auditing.
  This function shall only be called when the class of the event is of interest 
to the plug-in by having its bit set to 1 in the class mask.

There will be a set of struct mysql_event_xxx structures, where an xxx depends o
n the event class. Any of these structures may be passed as the second argument
to event_notify(). The structure is self-describing, its first field is
event_class (same as in the class_mask).


When a Audit Plug-in is installed or uninstalled, a global mask is maintained 
which is the logical disjunction of all audit classes that all audit plug-ins 
have interest.

The macro/notify function first checks if there are any interested parties of 
the audit class and returns quickly if there is no interest.
The current thd is associated with the audit subsystem iff it has not already 
occurred. Association provides the thd with a list of active audit plug-ins 
which have no risk of 'going away' during execution. This list is iterated 
through and to notify when an audit class of interest has triggered. When an 
audit plug-in is to be notified for the first time in the current thd, the 
associate-thd function is first called. Success or failure is recorded and if 
failure has occurred, no further attempt to call that specific audit plug-in 
shall be made.
At the end of statement execution before the current thd sleeps, all associated 
audit plug-ins are called to release before the list of audit plug-ins are 

This strategy ensures that there is no more than 1 mutex contention event upon 
during typical execution of a statement.