Writing Audit Plugins

你的名字 2022-09-20 15:17 142阅读 0赞

本文转载自：  **http://docs.oracle.com/cd/E17952\_01/refman-5.5-en/writing-audit-plugins.html**

#### Writing Audit Plugins ####

This section describes how to write an audit server 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 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 server operations such as these:

*  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
 *  Client connect and disconnect events

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 24.2.4.2.1, “Server Plugin Library and Plugin Descriptors”][Section_24.2.4.2.1_ _Server Plugin Library and Plugin Descriptors]). In `audit_null.c`, the general descriptor 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) */
      0x0002,                     /* version                         */
      simple_status,              /* status variables                */
      NULL,                       /* system variables                */
      NULL,
      0,
    }
    mysql_declare_plugin_end;

The `name` member (`NULL_AUDIT`) indicates the name to use for references to the plugin in statements such as [ `INSTALL PLUGIN`][_INSTALL PLUGIN] or [ `UNINSTALL PLUGIN`][_UNINSTALL PLUGIN]. This is also the name displayed by [ `INFORMATION_SCHEMA.PLUGINS`][_INFORMATION_SCHEMA.PLUGINS] or [ `SHOW PLUGINS`][_SHOW PLUGINS].

The general descriptor also refers to `simple_status`, a structure that exposes several status variables to the [ `SHOW STATUS`][_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 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 descriptor points to the type-specific 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 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` member 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. 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` 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. `plugin_audit.h` defines its symbol, `MYSQL_AUDIT_GENERAL_CLASS`, and a mask with a bit for this class:

#define MYSQL_AUDIT_GENERAL_CLASS 0
    #define MYSQL_AUDIT_GENERAL_CLASSMASK (1 << MYSQL_AUDIT_GENERAL_CLASS)

`plugin_audit.h` also has defines for a  “connection” event class, although the `NULL_AUDIT` plugin does nothing with such events:

#define MYSQL_AUDIT_CONNECTION_CLASS 1
    #define MYSQL_AUDIT_CONNECTION_CLASSMASK (1 << MYSQL_AUDIT_CONNECTION_CLASS)

A plugin could be written to receive both general and connection events by setting its type-specific descriptor class mask like this:

{ (unsigned long) MYSQL_AUDIT_GENERAL_CLASSMASK |
                     MYSQL_AUDIT_CONNECTION_CLASSMASK } /* class mask        */

In the type-specific 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 the [ `mysql_errno()`][_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()`][_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`][_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`][_DELETE] statement, this value is 0. For a [ `SELECT`][_SELECT], 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 as `connect` or `drop_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.5.34. 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

The `NULL_AUDIT` plugin notification function is quite simple. It increments a global event counter, verifies that the event is of the “general” 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)
    {
      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;
        default:
          break;
        }
      }
    }

To compile and install a plugin library object file, use the instructions in [ Section 24.2.4.3, “Compiling and Installing Plugin Libraries”][Section_24.2.4.3_ _Compiling and Installing Plugin Libraries]. To use the library file, it must be installed in the plugin directory (the directory named by the [ `plugin_dir`][_plugin_dir] system variable). For the `AUDIT_NULL` 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 suffix might differ depending on your platform).

To register the plugin at runtime, use this statement (change the suffix as necessary):

mysql> INSTALL PLUGIN NULL_AUDIT SONAME 'adt_null.so';

For additional information about plugin loading, see [ Section 5.1.8.1, “Installing and Uninstalling Plugins”][Section_5.1.8.1_ _Installing and Uninstalling Plugins].

To verify plugin installation, examine the [ `INFORMATION_SCHEMA.PLUGINS`][_INFORMATION_SCHEMA.PLUGINS] table or use the [ `SHOW PLUGINS`][_SHOW PLUGINS] statement.

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         | 1385  |
    | Audit_null_general_error  | 1     |
    | Audit_null_general_log    | 741   |
    | Audit_null_general_result | 643   |
    +---------------------------+-------+

`Audit_null_called` counts all events, and the other variables count instances of event subclasses. For example, the preceding [ `SHOW STATUS`][_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.

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

mysql> UNINSTALL PLUGIN NULL_AUDIT;

**注：近期参加MySQL运维学习，老师推荐该文章作为学习和技术提高的扩展阅读，先记录到自己的博客中，随后慢慢消化、学习、提高。本文章与“字符集和权限安全”主题相关。**

[Section_24.2.4.2.1_ _Server Plugin Library and Plugin Descriptors]: http://docs.oracle.com/cd/E17952_01/refman-5.5-en/plugin-data-structures.html#server-plugin-descriptors
[_INSTALL PLUGIN]: http://docs.oracle.com/cd/E17952_01/refman-5.5-en/install-plugin.html
[_UNINSTALL PLUGIN]: http://docs.oracle.com/cd/E17952_01/refman-5.5-en/uninstall-plugin.html
[_INFORMATION_SCHEMA.PLUGINS]: http://docs.oracle.com/cd/E17952_01/refman-5.5-en/plugins-table.html
[_SHOW PLUGINS]: http://docs.oracle.com/cd/E17952_01/refman-5.5-en/show-plugins.html
[_SHOW STATUS]: http://docs.oracle.com/cd/E17952_01/refman-5.5-en/show-status.html
[_mysql_errno]: http://docs.oracle.com/cd/E17952_01/refman-5.5-en/mysql-errno.html
[_mysql_error]: http://docs.oracle.com/cd/E17952_01/refman-5.5-en/mysql-error.html
[_TIMESTAMP]: http://docs.oracle.com/cd/E17952_01/refman-5.5-en/datetime.html
[_DELETE]: http://docs.oracle.com/cd/E17952_01/refman-5.5-en/delete.html
[_SELECT]: http://docs.oracle.com/cd/E17952_01/refman-5.5-en/select.html
[Section_24.2.4.3_ _Compiling and Installing Plugin Libraries]: http://docs.oracle.com/cd/E17952_01/refman-5.5-en/compiling-plugin-libraries.html
[_plugin_dir]: http://docs.oracle.com/cd/E17952_01/refman-5.5-en/server-system-variables.html#sysvar_plugin_dir
[Section_5.1.8.1_ _Installing and Uninstalling Plugins]: http://docs.oracle.com/cd/E17952_01/refman-5.5-en/server-plugin-loading.html