29.2.4.8 Writing Audit Plugins

An audit plugin, like any MySQL server plugin, has a general plugin descriptor (see Section 29.2.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                */
  system_variables,           /* 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 audit_null_plugin_init initialization function performs plugin initialization when the plugin is loaded. The audit_null_plugin_deinit function performs cleanup when the plugin is unloaded.

The general plugin descriptor also refers to simple_status and system_variables, structures that expose several status and system variables. When the plugin is enabled, these variables can be inspected using SHOW statements (SHOW STATUS, SHOW VARIABLES) or the appropriate Performance Schema tables.

The simple_status structure declares several status variables with names of the form Audit_null_xxx. NULL_AUDIT increments the Audit_null_called status variable for every notification that it receives. The other status variables are more specific and NULL_AUDIT increments them only for notifications of specific events.

system_variables is an array of system variable elements, each of which is defined using a MYSQL_THDVAR_xxx macro. These system variables have names of the form null_audit_xxx. These variables can be used to communicate with the plugin at runtime.

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 (defined in plugin_audit.h):

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

The type-specific descriptor for audit plugins has these members:

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_ALL,
    (unsigned long) MYSQL_AUDIT_CONNECTION_ALL,
    (unsigned long) MYSQL_AUDIT_PARSE_ALL,
    (unsigned long) MYSQL_AUDIT_AUTHORIZATION_ALL,
    (unsigned long) MYSQL_AUDIT_TABLE_ACCESS_ALL,
    (unsigned long) MYSQL_AUDIT_GLOBAL_VARIABLE_ALL,
    (unsigned long) MYSQL_AUDIT_SERVER_STARTUP_ALL,
    (unsigned long) MYSQL_AUDIT_SERVER_SHUTDOWN_ALL,
    (unsigned long) MYSQL_AUDIT_COMMAND_ALL,
    (unsigned long) MYSQL_AUDIT_QUERY_ALL,
    (unsigned long) MYSQL_AUDIT_STORED_PROGRAM_ALL }
};

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

The class_mask member is an array that indicates which event classes the plugin subscribes to. As shown, the array contents subscribe to all subclasses of all event classes that are available. To ignore all notifications for a given event class, specify the corresponding class_mask element as 0.

The number of class_mask elements corresponds to the number of event classes, each of which is listed in the mysql_event_class_t enumeration defined in plugin_audit.h:

typedef enum
{
  MYSQL_AUDIT_GENERAL_CLASS          = 0,
  MYSQL_AUDIT_CONNECTION_CLASS       = 1,
  MYSQL_AUDIT_PARSE_CLASS            = 2,
  MYSQL_AUDIT_AUTHORIZATION_CLASS    = 3,
  MYSQL_AUDIT_TABLE_ACCESS_CLASS     = 4,
  MYSQL_AUDIT_GLOBAL_VARIABLE_CLASS  = 5,
  MYSQL_AUDIT_SERVER_STARTUP_CLASS   = 6,
  MYSQL_AUDIT_SERVER_SHUTDOWN_CLASS  = 7,
  MYSQL_AUDIT_COMMAND_CLASS          = 8,
  MYSQL_AUDIT_QUERY_CLASS            = 9,
  MYSQL_AUDIT_STORED_PROGRAM_CLASS   = 10,
  /* This item must be last in the list. */
  MYSQL_AUDIT_CLASS_MASK_SIZE
} mysql_event_class_t;

For any given event class, plugin_audit.h defines bitmask symbols for individual event subclasses, as well as an xxx_ALL symbol that is the union of the all subclass bitmasks. For example, for MYSQL_AUDIT_CONNECTION_CLASS (the class that covers connect and disconnect events), plugin_audit.h defines these symbols:

typedef enum
{
  /** occurs after authentication phase is completed. */
  MYSQL_AUDIT_CONNECTION_CONNECT          = 1 << 0,
  /** occurs after connection is terminated. */
  MYSQL_AUDIT_CONNECTION_DISCONNECT       = 1 << 1,
  /** occurs after COM_CHANGE_USER RPC is completed. */
  MYSQL_AUDIT_CONNECTION_CHANGE_USER      = 1 << 2,
  /** occurs before authentication. */
  MYSQL_AUDIT_CONNECTION_PRE_AUTHENTICATE = 1 << 3
} mysql_event_connection_subclass_t;

#define MYSQL_AUDIT_CONNECTION_ALL (MYSQL_AUDIT_CONNECTION_CONNECT | \
                                    MYSQL_AUDIT_CONNECTION_DISCONNECT | \
                                    MYSQL_AUDIT_CONNECTION_CHANGE_USER | \
                                    MYSQL_AUDIT_CONNECTION_PRE_AUTHENTICATE)

To subscribe to all subclasses of the connection event class (as the NULL_AUDIT plugin does), a plugin specifies MYSQL_AUDIT_CONNECTION_ALL in the corresponding class_mask element (class_mask[1] in this case). To subscribe to only some subclasses, the plugin sets the class_mask element to the union of the subclasses of interest. For example, to subscribe only to the connect and change-user subclasses, the plugin sets class_mask[1] to this value:

MYSQL_AUDIT_CONNECTION_CONNECT | MYSQL_AUDIT_CONNECTION_CHANGE_USER

Most of the work for an audit plugin occurs in the notification function (the event_notify member of the type-specific plugin descriptor). The server calls this function for each auditable event. Audit plugin notification functions have this prototype:

int (*event_notify)(MYSQL_THD, mysql_event_class_t, const void *);

The second and third parameters of the event_notify function prototype represent the event class and a generic pointer to an event structure. (Events in different classes have different structures. The notification function can use the event class value to determine which event structure applies.) The function processes the event and returns a status indicating whether the server should continue processing the event or terminate it.

For NULL_AUDIT, the notification function is audit_null_notify(). This function increments a global event counter (which the plugin exposes as the value of the Audit_null_called status value), and then examines the event class to determine how to process the event structure:

static int audit_null_notify(MYSQL_THD thd __attribute__((unused)),
                             mysql_event_class_t event_class,
                             const void *event)
{
  ...

  number_of_calls++;

  if (event_class == MYSQL_AUDIT_GENERAL_CLASS)
  {
    const struct mysql_event_general *event_general=
                                    (const struct mysql_event_general *)event;
    ...
  }
  else if (event_class == MYSQL_AUDIT_CONNECTION_CLASS)
  {
    const struct mysql_event_connection *event_connection=
                                (const struct mysql_event_connection *) event;
    ...

  }
  else if (event_class == MYSQL_AUDIT_PARSE_CLASS)
  {
    const struct mysql_event_parse *event_parse =
                                      (const struct mysql_event_parse *)event;
    ...
  }
  ...
}

The notification function interprets the event argument according to the value of event_class. The event argument is a generic pointer to the event record, the structure of which differs per event class. (The plugin_audit.h file contains the structures that define the contents of each event class.) For each class, audit_null_notify() casts the event to the appropriate class-specific structure and then checks its subclass to determine which subclass counter to increment. For example, the code to handle events in the connection-event class looks like this:

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;
  case MYSQL_AUDIT_CONNECTION_PRE_AUTHENTICATE:
    number_of_calls_connection_pre_authenticate++;
      break;
  default:
    break;
  }
}

For some event classes, the NULL_AUDIT plugin performs other processing in addition to incrementing a counter. In any case, when the notification function finishes processing the event, it should return a status indicating whether the server should continue processing the event or terminate it.

Audit plugin notification functions can report a status value for the current event two ways:

Some events cannot be terminated:

If an audit plugin returns nonzero status for a nonterminable event, the server ignores the status and continues processing the event. This is also true if an audit plugin uses the my_message() function to terminate a nonterminable event.

To compile and install a plugin library file, use the instructions in Section 29.2.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 (adjust the .so suffix for your platform as necessary):

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

To verify plugin installation, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement. See Section 5.6.2, “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:

Audit_null_called counts all events, and the other variables count instances of specific 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, Audit_null_general_result, and Audit_null_general_log to be incremented each time. Notifications occur whether or not that log is enabled.

The status variables values are aggregated across all sessions. There are no counters for individual sessions.

NULL_AUDIT exposes several system variables that enable communication with the plugin at runtime:

To check the order of audit API calls, set the null_audit_event_order_check variable to the expected event order. For example:

The statement takes advantage of the SQL syntax that concatenates adjacent strings into a single string.

The format of the value is:

'event_name;event_data;command' [';event_name;event_data;command'] ...

After the event order is matched, the null_audit_event_order_check value is replaced with a value of EVENT-ORDER-OK.

Specifying a command value of ABORT_RET makes it possible to abort the audit API call on the specified event. The following example aborts INSERT statement execution when its MYSQL_AUDIT_QUERY_STATUS_END event occurs:

After the audit plugin matches the preceding sequence, it aborts event processing and sends an error message to the client:

ERROR 3164 (HY000): Aborted by Audit API ('MYSQL_AUDIT_QUERY_STATUS_END';1).

Returning a nonzero value from the audit API notification routine is the standard way to abort event execution. It is also possible to specify a custom error code by setting the null_audit_abort_value variable to the value that the notification routine should return:

Aborting a sequence results in a standard message with the custom error code. Suppose that you set audit log system variables like this:

Then execution of SELECT 1 results in this error:

ERROR 3164 (HY000): Aborted by Audit API ('MYSQL_AUDIT_QUERY_START';123).

An event can be also aborted with a custom message, specified by setting the null_audit_abort_message variable: Suppose that you set audit log system variables like this:

Then aborting a sequence results in the following error:

ERROR 3164 (HY000): Custom error text.

For test-creation purposes, it is possible to record events that pass through the plugin. Recording starts by specifying start and end events in the null_audit_event_record_def variable:

Statement execution results in storing the events that occur in the null_audit_event_record variable.

To disable the plugin after testing it, use this statement to unload it: