This section describes how to write a server-side audit
plugin, using the example plugin found in the
plugin/audit_null
directory of MySQL source
distributions. The audit_null.c
source
file in that directory implements a simple example audit
plugin named NULL_AUDIT
.
Within the server, the pluggable audit interface is
implemented in the sql_audit.h
and
sql_audit.cc
files in the
sql
directory of MySQL source
distributions. Additionally, several places in the server call
the audit interface when an auditable event occurs, so that
registered audit plugins can be notified about the event if
necessary. To see where such calls occur, search the server
source files for invocations of functions with names of the
form
mysql_audit_
.
Audit notification occurs for server operations such as these:
xxx
()
Client connect and disconnect events
Writing a message to the general query log (if the log is enabled)
Writing a message to the error log
Sending a query result to a client
To write an audit plugin, include the following header file in the plugin source file. Other MySQL or general header files might also be needed, depending on the plugin capabilities and requirements.
#include <mysql/plugin_audit.h>
plugin_audit.h
includes
plugin.h
, so you need not include the
latter file explicitly. plugin.h
defines
the MYSQL_AUDIT_PLUGIN
server plugin type
and the data structures needed to declare the plugin.
plugin_audit.h
defines data structures
specific to audit plugins.
An audit plugin, like any MySQL server plugin, has a general
plugin descriptor (see
Section 4.4.2.1, “Server Plugin Library and Plugin Descriptors”) and a
type-specific plugin descriptor. In
audit_null.c
, the general descriptor for
audit_null
looks like this:
mysql_declare_plugin(audit_null)
{
MYSQL_AUDIT_PLUGIN, /* type */
&audit_null_descriptor, /* descriptor */
"NULL_AUDIT", /* name */
"Oracle Corp", /* author */
"Simple NULL Audit", /* description */
PLUGIN_LICENSE_GPL,
audit_null_plugin_init, /* init function (when loaded) */
audit_null_plugin_deinit, /* deinit function (when unloaded) */
0x0003, /* version */
simple_status, /* status variables */
NULL, /* system variables */
NULL,
0,
}
mysql_declare_plugin_end;
The first member, MYSQL_AUDIT_PLUGIN
,
identifies this plugin as an audit plugin.
audit_null_descriptor
points to the
type-specific plugin descriptor, described later.
The name
member
(NULL_AUDIT
) indicates the name to use for
references to the plugin in statements such as
INSTALL PLUGIN
or
UNINSTALL PLUGIN
. This is also
the name displayed by
INFORMATION_SCHEMA.PLUGINS
or
SHOW PLUGINS
.
The general plugin descriptor also refers to
simple_status
, a structure that exposes
several status variables to the SHOW
STATUS
statement:
static struct st_mysql_show_var simple_status[]=
{
{ "Audit_null_called",
(char *) &number_of_calls,
SHOW_INT },
{ "Audit_null_general_log",
(char *) &number_of_calls_general_log,
SHOW_INT },
{ "Audit_null_general_error",
(char *) &number_of_calls_general_error,
SHOW_INT },
{ "Audit_null_general_result",
(char *) &number_of_calls_general_result,
SHOW_INT },
{ "Audit_null_general_status",
(char *) &number_of_calls_general_status,
SHOW_INT },
{ "Audit_null_connection_connect",
(char *) &number_of_calls_connection_connect,
SHOW_INT },
{ "Audit_null_connection_disconnect",
(char *) &number_of_calls_connection_disconnect,
SHOW_INT },
{ "Audit_null_connection_change_user",
(char *) &number_of_calls_connection_change_user,
SHOW_INT },
{ 0, 0, 0}
};
The audit_null_plugin_init
initialization
function sets the status variables to zero when the plugin is
loaded. The audit_null_plugin_deinit
function performs cleanup when the plugin is unloaded. During
operation, the plugin increments the first status variable for
each notification it receives. It also increments the others
according to the event class and subclass. In effect, the
first variable is the aggregate of the counts for the event
subclasses.
The audit_null_descriptor
value in the
general plugin descriptor points to the type-specific plugin
descriptor. For audit plugins, this descriptor has the
following structure:
struct st_mysql_audit
{
int interface_version;
void (*release_thd)(MYSQL_THD);
void (*event_notify)(MYSQL_THD, unsigned int, const void *);
unsigned long class_mask[MYSQL_AUDIT_CLASS_MASK_SIZE];
};
The type-specific plugin descriptor for audit plugins has these members:
interface_version
: By convention, type-specific plugin descriptors begin with the interface version for the given plugin type. The server checksinterface_version
when it loads the plugin to see whether the plugin is compatible with it. For audit plugins, the value of theinterface_version
member isMYSQL_AUDIT_INTERFACE_VERSION
(defined inplugin_audit.h
).release_thd
: A function that the server calls to inform the plugin that it is being dissociated from its thread context. This should beNULL
if there is no such function.event_notify
: A function that the server calls to notify the plugin that an auditable event has occurred. This function should not beNULL
; that would not make sense because no auditing would occur.class_mask
: A bitmask that indicates the event classes for which the plugin wants to receive notification. If this value is 0, the server passes no events to the plugin.
The server uses the event_notify
and
release_thd
functions together. They are
called within the context of a specific thread, and a thread
might perform an activity that produces several event
notifications. The first time the server calls
event_notify
for a thread, it creates a
binding of the plugin to the thread. The plugin cannot be
uninstalled while this binding exists. When no more events for
the thread will occur, the server informs the plugin of this
by calling the release_thd
function, and
then destroys the binding. For example, when a client issues a
statement, the thread processing the statement might notify
audit plugins about the result set produced by the statement
and about the statement being logged. After these
notifications occur, the server releases the plugin before
putting the thread to sleep until the client issues another
statement.
This design enables the plugin to allocate resources needed
for a given thread in the first call to the
event_notify
function and release them in
the release_thd
function:
event_notify function:
if memory is needed to service the thread
allocate memory
... rest of notification processing ...
release_thd function:
if memory was allocated
release memory
... rest of release processing ...
That is more efficient than allocating and releasing memory repeatedly in the notification function.
For the NULL_AUDIT
audit plugin, the
type-specific plugin descriptor looks like this:
static struct st_mysql_audit audit_null_descriptor=
{
MYSQL_AUDIT_INTERFACE_VERSION, /* interface version */
NULL, /* release_thd function */
audit_null_notify, /* notify function */
{ (unsigned long) MYSQL_AUDIT_GENERAL_CLASSMASK |
MYSQL_AUDIT_CONNECTION_CLASSMASK } /* class mask */
};
The server calls audit_null_notify()
to
pass audit event information to the plugin. The plugin has no
release_thd
function.
The event class mask indicates an interest in all events of
the “general” and “connection”
classes. plugin_audit.h
defines symbols
for these classes and their corresponding class masks:
#define MYSQL_AUDIT_GENERAL_CLASS 0
#define MYSQL_AUDIT_GENERAL_CLASSMASK (1 << MYSQL_AUDIT_GENERAL_CLASS)
#define MYSQL_AUDIT_CONNECTION_CLASS 1
#define MYSQL_AUDIT_CONNECTION_CLASSMASK (1 << MYSQL_AUDIT_CONNECTION_CLASS)
In the type-specific plugin descriptor, the second and third
parameters of the event_notify
function
prototype represent the event class and a generic pointer to
an event structure:
void (*event_notify)(MYSQL_THD, unsigned int, const void *);
Events in different classes may have different structures, so the notification function should use the event class value to determine how to interpret the pointer to the event structure.
If the server calls the notification function with an event
class of MYSQL_AUDIT_GENERAL_CLASS
, it
passes the event structure as a pointer to a
mysql_event_general
structure:
struct mysql_event_general
{
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;
};
Audit plugins can interpret
mysql_event_general
members as follows:
-
event_subclass
: The event subclass, one of the following values:#define MYSQL_AUDIT_GENERAL_LOG 0 #define MYSQL_AUDIT_GENERAL_ERROR 1 #define MYSQL_AUDIT_GENERAL_RESULT 2 #define MYSQL_AUDIT_GENERAL_STATUS 3
general_error_code
: The error code. This is a value like that returned by themysql_errno()
C API function; 0 means “no error.”general_thread_id
: The ID of the thread for which the event occurred.general_user
: The current user for the event.general_user_length
: The length ofgeneral_user
, in bytes.general_command
: For general query log events, the type of operation. Examples:Connect
,Query
,Shutdown
. For error log events, the error message. This is a value like that returned by themysql_error()
C API function; an empty string means “no error.” For result events, this is empty.general_command_length
: The length ofgeneral_command
, in bytes.general_query
: The SQL statement that was logged or produced a result.general_query_length
: The length ofgeneral_query
, in bytes.general_charset
: Character set information for the event.general_time
: ATIMESTAMP
value indicating the time just before the notification function was called.general_rows
: For general query log events, zero. For error log events, the row number at which an error occurred. For result events, the number of rows in the result plus one. For statements that produce no result set, the value is 0. This encoding enables statements that produce no result set to be distinguished from those that produce an empty result set. For example, for aDELETE
statement, this value is 0. For aSELECT
, the result is always 1 or more, where 1 represents an empty result set.general_host
: For general query log events, a string representing the client host name.general_sql_command
: For general query log events, a string that indicates the type of action performed, such asconnect
ordrop_table
.general_external_user
: For general query log events, a string representing the external user (empty if none).general_ip
: For general query log events, a string representing the client IP address.
The general_host
,
general_sql_command
,
general_external_user
, and
general_ip
members are new in MySQL 5.6.14.
These are MYSQL_LEX_STRING
structures that
pair a string and its length. For example, if
event_general
is a pointer to a general
event, you can access the members of the
general_host
value as follows:
event_general->general_host.length
event_general->general_host.str
If the server calls the notification function with an event
class of MYSQL_AUDIT_CONNECTION_CLASS
, it
passes the event structure as a pointer to a
mysql_event_connection
structure, which is
similar to and interpreted much the same way as the
mysql_event_general
structure.
The NULL_AUDIT
plugin notification function
is quite simple. It increments a global event counter,
determines the event class, then looks at the event subclass
to determine which subclass counter to increment:
static void audit_null_notify(MYSQL_THD thd __attribute__((unused)),
unsigned int event_class,
const void *event)
{
/* prone to races, oh well */
number_of_calls++;
if (event_class == MYSQL_AUDIT_GENERAL_CLASS)
{
const struct mysql_event_general *event_general=
(const struct mysql_event_general *) event;
switch (event_general->event_subclass)
{
case MYSQL_AUDIT_GENERAL_LOG:
number_of_calls_general_log++;
break;
case MYSQL_AUDIT_GENERAL_ERROR:
number_of_calls_general_error++;
break;
case MYSQL_AUDIT_GENERAL_RESULT:
number_of_calls_general_result++;
break;
case MYSQL_AUDIT_GENERAL_STATUS:
number_of_calls_general_status++;
break;
default:
break;
}
}
else if (event_class == MYSQL_AUDIT_CONNECTION_CLASS)
{
const struct mysql_event_connection *event_connection=
(const struct mysql_event_connection *) event;
switch (event_connection->event_subclass)
{
case MYSQL_AUDIT_CONNECTION_CONNECT:
number_of_calls_connection_connect++;
break;
case MYSQL_AUDIT_CONNECTION_DISCONNECT:
number_of_calls_connection_disconnect++;
break;
case MYSQL_AUDIT_CONNECTION_CHANGE_USER:
number_of_calls_connection_change_user++;
break;
default:
break;
}
}
}
To compile and install a plugin library file, use the
instructions in Section 4.4.3, “Compiling and Installing Plugin Libraries”.
To make the library file available for use, install it in the
plugin directory (the directory named by the
plugin_dir
system variable).
For the NULL_AUDIT
plugin, it is compiled
and installed when you build MySQL from source. It is also
included in binary distributions. The build process produces a
shared object library with a name of
adt_null.so
(the .so
suffix might differ depending on your platform).
To register the plugin at runtime, use this statement,
adjusting the .so
suffix for your
platform as necessary:
INSTALL PLUGIN NULL_AUDIT SONAME 'adt_null.so';
For additional information about plugin loading, see Installing and Uninstalling Plugins.
To verify plugin installation, examine the
INFORMATION_SCHEMA.PLUGINS
table
or use the SHOW PLUGINS
statement. See Obtaining Server Plugin Information.
While the audit plugin is installed, it exposes status variables that indicate the events for which the plugin has been called:
mysql> SHOW STATUS LIKE 'Audit_null%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Audit_null_called | 1388 |
| Audit_null_connection_change_user | 0 |
| Audit_null_connection_connect | 22 |
| Audit_null_connection_disconnect | 21 |
| Audit_null_general_error | 1 |
| Audit_null_general_log | 513 |
| Audit_null_general_result | 415 |
| Audit_null_general_status | 416 |
+-----------------------------------+-------+
Audit_null_called
counts all events, and
the other variables count instances of event subclasses. For
example, the preceding SHOW
STATUS
statement causes the server to send a result
to the client and to write a message to the general query log
if that log is enabled. Thus, a client that issues the
statement repeatedly causes
Audit_null_called
and
Audit_null_general_result
to be incremented
each time, and Audit_null_general_log
to be
incremented. (Before MySQL 5.6.24, notification of events for
the general query log are received only if the general query
log is enabled. As of 5.6.24, notifications are received
regardless of whether that log is enabled.)
To disable the plugin after testing it, use this statement to unload it:
UNINSTALL PLUGIN NULL_AUDIT;