Writing Audit Plugins

This section describes how to write audit server plugins, using the example plugin found in the plugin/audit_null directory of MySQL source distributions. Other audit plugins can be written using similar principles. For general information about writing plugins, see Section 23.2.5, “Writing Plugins”.

This description includes references to audit plugin interface elements that are present only as of MySQL 5.5.5:

  • The event_subclass member of struct mysql_event_general

  • Status variables that count notifications for individual event subclasses

A plugin that requires those elements must be compiled against MySQL source for which the value of MYSQL_AUDIT_INTERFACE_VERSION, the type-specific interface version, is 0x0200 or greater.

On the server side, 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, a few other places in the server are modified to 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, look for invocations of functions with names of the form mysql_audit_xxx(). Audit notification occurs for these server operations:

  • Write a message to the general query log (if the log is enabled)

  • Write a message to the error log

  • Send a query result to a client

These events are all treated as subclasses of the “general” event class.

On the plugin side, an audit plugin uses the interface defined in plugin_audit.h, which includes plugin.h for general plugin interface information. These include files are located in the include/mysql directory. The audit_null.c source file in the plugin/audit_null directory implements a simple example audit plugin named NULL_AUDIT.

An audit plugin, like any MySQL plugin, has a general plugin descriptor (see Section, “General Plugin Data Structures and Functions”). In audit_null.c, the general descriptor looks like this:

  MYSQL_AUDIT_PLUGIN,         /* type                            */
  &audit_null_descriptor,     /* descriptor                      */
  "NULL_AUDIT",               /* name                            */
  "Oracle Corporation",       /* author                          */
  "Simple NULL Audit",        /* description                     */
  audit_null_plugin_init,     /* init function (when loaded)     */
  audit_null_plugin_deinit,   /* deinit function (when unloaded) */
  0x0001,                     /* version                         */
  simple_status,              /* status variables                */
  NULL,                       /* system variables                */

The name field (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 SHOW PLUGINS or INFORMATION_SCHEMA.PLUGINS.

The audit_null_descriptor value in the general descriptor points to the type-specific descriptor, which is described later.

The general 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 },
  { 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 with the plugin is unloaded. During operation, the plugin increments the first variable for each notification it receives. It increments the others according to the event subclass. In effect, the first variable is the aggregate of the counts for the event subclasses.

Audit plugins have a type-specific descriptor with this structure:

struct st_mysql_audit
  int interface_version;
  void (*release_thd)(MYSQL_THD);
  void (*event_notify)(MYSQL_THD, const struct mysql_event *);
  unsigned long class_mask[MYSQL_AUDIT_CLASS_MASK_SIZE];

The type-specific descriptor has these members:

  • interface_version: By convention, type-specific plugin descriptors begin with the interface version for the given plugin type. The server checks interface_version when it loads the plugin to see whether the plugin is compatible with it. For audit plugins, the value of the interface_version field is MYSQL_AUDIT_INTERFACE_VERSION (defined in plugin_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 be NULL 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 be NULL; that would not make sense because no auditing would occur.

  • class_mask: A bit mask that indicates the event classes for which the plugin wants to receive notification.

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 example audit plugin, the type-specific 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 } /* class mask           */

The server calls audit_null_notify to pass audit event information to the plugin. There is no release_thd function.

The event class mask indicates an interest in all events of the “general” class. Currently, that is the only event class. plugin_audit.h defines its symbol, MYSQL_AUDIT_GENERAL_CLASS, and a mask with a bit for this class:


In the type-specific descriptor, the second parameter of the event_notify function prototype is a generic mysql_event pointer:

void (*event_notify)(MYSQL_THD, const struct mysql_event *);

The server actually passes the notification function a pointer to a structure that depends on the event class. The first member of all event structures must indicate the event class to enable the notification function to determine what kind of structure it was passed so that it can tell what other structure members exist.

The server passes events in the “general” event class to the event_notify function using this structure:

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;

Audit plugins can interpret mysql_event_general members as follows:

  • event_class: The event class, always MYSQL_AUDIT_GENERAL_CLASS for “general” events.

  • event_subclass: The event subclass, one of the following values:

  • general_error_code: The error code. This is a value like that returned by the mysql_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 of general_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 the mysql_error() C API function; an empty string means “no error.” For result events, this is empty.

  • general_command_length: The length of general_command, in bytes.

  • general_query: The SQL statement that was logged or produced a result.

  • general_query_length: The length of general_query, in bytes.

  • general_charset: Character set information for the event.

  • general_time: A TIMESTAMP 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 a DELETE statement, this value is 0. For a SELECT, the result is always 1 or more, where 1 represents an empty result set.

When you build MySQL from source, the example audit plugin is configured in and built by default. No special configuration options are needed to cause it to be compiled. The build process produces a shared object library with a name of adt_null.so (the extension might be different depending on your platform). This library file is installed in the plugin directory (the directory named by the plugin_dir system variable).

To install the plugin at runtime, use this statement (change the extension as necessary):


Alternatively, install the plugin at server startup. For example, use these lines in a my.cnf file:


In this case, the plugin is not installed permanently. Restarting without the plugin-load option causes the plugin not to be loaded at startup.

For additional information about plugin loading, see Section, “Installing and Uninstalling Plugins”.

Use SHOW PLUGINS to verify plugin installation or examine the INFORMATION_SCHEMA.PLUGINS table.

While the audit plugin is installed, it exposes status variables that indicate how the plugin has been called:

mysql> SHOW STATUS LIKE 'Audit_null%';
| Variable_name             | Value |
| Audit_null_called         | 2     |
| Audit_null_general_error  | 0     |
| Audit_null_general_log    | 1     |
| Audit_null_general_result | 1     |

Audit_null_called counts all events for any subclass of the “general” class, and the others count instances of each event subclass. 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 if the log is enabled.

Copyright © 2010-2020 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout