MySQL Syslog Audit Plugin
- by jonathonc
This post shows the construction process of the Syslog Audit plugin that was presented at MySQL Connect 2012. It is based on an environment that has the appropriate development tools enabled including gcc,g++ and cmake. It also assumes you have downloaded the MySQL source code (5.5.16 or higher) and have compiled and installed the system into the /usr/local/mysql directory ready for use.
The information provided below is designed to show the different components that make up a plugin, and specifically an audit type plugin, and how it comes together to be used within the MySQL service. The MySQL Reference Manual contains information regarding the plugin API and how it can be used, so please refer there for more detailed information. The code in this post is designed to give the simplest information necessary, so handling every return code, managing race conditions etc is not part of this example code.
Let's start by looking at the most basic implementation of our plugin code as seen below:
/*
Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved.
Author: Jonathon Coombes
Licence: GPL
Description: An auditing plugin that logs to syslog and
can adjust the loglevel via the system variables.
*/
#include <stdio.h>
#include <string.h>
#include <mysql/plugin_audit.h>
#include <syslog.h>
There is a commented header detailing copyright/licencing and meta-data information and then the include headers. The two important include statements for our plugin are the syslog.h plugin, which gives us the structures for syslog, and the plugin_audit.h include which has details regarding the audit specific plugin api. Note that we do not need to include the general plugin header plugin.h, as this is done within the plugin_audit.h file already.
To implement our plugin within the current implementation we need to add it into our source code and compile.
> cd /usr/local/src/mysql-5.5.28/plugin
> mkdir audit_syslog
> cd audit_syslog
A simple CMakeLists.txt file is created to manage the plugin compilation:
MYSQL_ADD_PLUGIN(audit_syslog audit_syslog.cc
MODULE_ONLY)
Run the cmake command at the top level of the source and then you can compile the plugin using the 'make' command. This results in a compiled audit_syslog.so library, but currently it is not much use to MySQL as there is no level of api defined to communicate with the MySQL service.
Now we need to define the general plugin structure that enables MySQL to recognise the library as a plugin and be able to install/uninstall it and have it show up in the system. The structure is defined in the plugin.h file in the MySQL source code.
/*
Plugin library descriptor
*/
mysql_declare_plugin(audit_syslog)
{
MYSQL_AUDIT_PLUGIN, /* plugin type */
&audit_syslog_descriptor, /* descriptor handle */
"audit_syslog", /* plugin name */
"Author Name", /* author */
"Simple Syslog Audit", /* description */
PLUGIN_LICENSE_GPL, /* licence */
audit_syslog_init, /* init function */
audit_syslog_deinit, /* deinit function */
0x0001, /* plugin version */
NULL, /* status variables */
NULL, /* system variables */
NULL, /* no reserves */
0, /* no flags */
}
mysql_declare_plugin_end;
The general plugin descriptor above is standard for all plugin types in MySQL.
The plugin type is defined along with the init/deinit functions and interface methods into the system for sharing information, and various other metadata information. The descriptors have an internally recognised version number so that plugins can be matched against the api on the running server.
The other details are usually related to the type-specific methods and structures to implement the plugin. Each plugin has a type-specific descriptor as well which details how the plugin is implemented for the specific purpose of that plugin type.
/*
Plugin type-specific descriptor
*/
static struct st_mysql_audit audit_syslog_descriptor=
{
MYSQL_AUDIT_INTERFACE_VERSION, /* interface version */
NULL, /* release_thd function */
audit_syslog_notify, /* notify function */
{ (unsigned long) MYSQL_AUDIT_GENERAL_CLASSMASK |
MYSQL_AUDIT_CONNECTION_CLASSMASK } /* class mask */
};
In this particular case, the release_thd function has not been defined as it is not required. The important method for auditing is the notify function which is activated when an event occurs on the system. The notify function is designed to activate on an event and the implementation will determine how it is handled. For the audit_syslog plugin, the use of the syslog feature sends all events to the syslog for recording. The class mask allows us to determine what type of events are being seen by the notify function. There are currently two major types of event:
1. General Events: This includes general logging, errors, status and result type events. This is the main one for tracking the queries and operations on the database.
2. Connection Events: This group is based around user logins. It monitors connections and disconnections, but also if somebody changes user while connected.
With most audit plugins, the principle behind the plugin is to track changes to the system over time and counters can be an important part of this process. The next step is to define and initialise the counters that are used to track the events in the service. There are 3 counters defined in total for our plugin - the # of general events, the # of connection events and the total number of events.
static volatile int total_number_of_calls;
/* Count MYSQL_AUDIT_GENERAL_CLASS event instances */
static volatile int number_of_calls_general;
/* Count MYSQL_AUDIT_CONNECTION_CLASS event instances */
static volatile int number_of_calls_connection;
The init and deinit functions for the plugin are there to be called when the plugin is activated and when it is terminated. These offer the best option to initialise the counters for our plugin:
/*
Initialize the plugin at server start or plugin installation.
*/
static int audit_syslog_init(void *arg __attribute__((unused)))
{
openlog("mysql_audit:",LOG_PID|LOG_PERROR|LOG_CONS,LOG_USER);
total_number_of_calls= 0;
number_of_calls_general= 0;
number_of_calls_connection= 0;
return(0);
}
The init function does a call to openlog to initialise the syslog functionality. The parameters are the service to log under ("mysql_audit" in this case), the syslog flags and the facility for the logging. Then each of the counters are initialised to zero and a success is returned. If the init function is not defined, it will return success by default.
/*
Terminate the plugin at server shutdown or plugin deinstallation.
*/
static int audit_syslog_deinit(void *arg __attribute__((unused)))
{
closelog();
return(0);
}
The deinit function will simply close our syslog connection and return success. Note that the syslog functionality is part of the glibc libraries and does not require any external factors.
The function names are what we define in the general plugin structure, so these have to match otherwise there will be errors.
The next step is to implement the event notifier function that was defined in the type specific descriptor (audit_syslog_descriptor) which is audit_syslog_notify.
/*
Event notifier function
*/
static void audit_syslog_notify(MYSQL_THD thd __attribute__((unused)),
unsigned int event_class,
const void *event)
{
total_number_of_calls++;
if (event_class == MYSQL_AUDIT_GENERAL_CLASS)
{
const struct mysql_event_general *event_general=
(const struct mysql_event_general *) event;
number_of_calls_general++;
syslog(audit_loglevel,"%lu: User: %s Command: %s Query: %s\n",
event_general->general_thread_id, event_general->general_user,
event_general->general_command, event_general->general_query );
}
else if (event_class == MYSQL_AUDIT_CONNECTION_CLASS)
{
const struct mysql_event_connection *event_connection=
(const struct mysql_event_connection *) event;
number_of_calls_connection++;
syslog(audit_loglevel,"%lu: User: %s@%s[%s] Event: %d Status: %d\n",
event_connection->thread_id, event_connection->user, event_connection->host,
event_connection->ip, event_connection->event_subclass, event_connection->status );
}
}
In the case of an event, the notifier function is called. The first step is to increment the total number of events that have occurred in our database.The event argument is then cast into the appropriate event structure depending on the class type, of general event or connection event. The event type counters are incremented and details are sent via the syslog() function out to the system log. There are going to be different line formats and information returned since the general events have different data compared to the connection events, even though some of the details overlap, for example, user, thread id, host etc.
On compiling the code now, there should be no errors and the resulting audit_syslog.so can be loaded into the server and ready to use. Log into the server and type:
mysql> INSTALL PLUGIN audit_syslog SONAME 'audit_syslog.so';
This will install the plugin and will start updating the syslog immediately. Note that the audit plugin attaches to the immediate thread and cannot be uninstalled while that thread is active. This means that you cannot run the UNISTALL command until you log into a different connection (thread) on the server.
Once the plugin is loaded, the system log will show output such as the following:
Oct 8 15:33:21 machine mysql_audit:[8337]: 87: User: root[root] @ localhost [] Command: (null) Query: INSTALL PLUGIN audit_syslog SONAME 'audit_syslog.so'
Oct 8 15:33:21 machine mysql_audit:[8337]: 87: User: root[root] @ localhost [] Command: Query Query: INSTALL PLUGIN audit_syslog SONAME 'audit_syslog.so'
Oct 8 15:33:40 machine mysql_audit:[8337]: 87: User: root[root] @ localhost [] Command: (null) Query: show tables
Oct 8 15:33:40 machine mysql_audit:[8337]: 87: User: root[root] @ localhost [] Command: Query Query: show tables
Oct 8 15:33:43 machine mysql_audit:[8337]: 87: User: root[root] @ localhost [] Command: (null) Query: select * from t1
Oct 8 15:33:43 machine mysql_audit:[8337]: 87: User: root[root] @ localhost [] Command: Query Query: select * from t1
It appears that two of each event is being shown, but in actuality, these are two separate event types - the result event and the status event. This could be refined further by changing the audit_syslog_notify function to handle the different event sub-types in a different manner.
So far, it seems that the logging is working with events showing up in the syslog output. The issue now is that the counters created earlier to track the number of events by type are not accessible when the plugin is being run. Instead there needs to be a way to expose the plugin specific information to the service and vice versa. This could be done via the information_schema plugin api, but for something as simple as counters, the obvious choice is the system status variables. This is done using the standard structure and the declaration:
/*
Plugin status variables for SHOW STATUS
*/
static struct st_mysql_show_var audit_syslog_status[]=
{
{ "Audit_syslog_total_calls",
(char *) &total_number_of_calls,
SHOW_INT },
{ "Audit_syslog_general_events",
(char *) &number_of_calls_general,
SHOW_INT },
{ "Audit_syslog_connection_events",
(char *) &number_of_calls_connection,
SHOW_INT },
{ 0, 0, SHOW_INT }
};
The structure is simply the name that will be displaying in the mysql service, the address of the associated variables, and the data type being used for the counter. It is finished with a blank structure to show that there are no more variables. Remember that status variables may have the same name for variables from other plugin, so it is considered appropriate to add the plugin name at the start of the status variable name to avoid confusion. Looking at the status variables in the mysql client shows something like the following:
mysql> show global status like "audit%";
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| Audit_syslog_connection_events | 1 |
| Audit_syslog_general_events | 2 |
| Audit_syslog_total_calls | 3 |
+--------------------------------+-------+
3 rows in set (0.00 sec)
The final connectivity piece for the plugin is to allow the interactive change of the logging level between the plugin and the system. This requires the ability to send changes via the mysql service through to the plugin. This is done using the system variables interface and defining a single variable to keep track of the active logging level for the facility.
/*
Plugin system variables for SHOW VARIABLES
*/
static MYSQL_SYSVAR_STR(loglevel, audit_loglevel,
PLUGIN_VAR_RQCMDARG,
"User can specify the log level for auditing",
audit_loglevel_check, audit_loglevel_update, "LOG_NOTICE");
static struct st_mysql_sys_var* audit_syslog_sysvars[] = {
MYSQL_SYSVAR(loglevel),
NULL
};
So now the system variable 'loglevel' is defined for the plugin and associated to the global variable 'audit_loglevel'. The check or validation function is defined to make sure that no garbage values are attempted in the update of the variable. The update function is used to save the new value to the variable. Note that the audit_syslog_sysvars structure is defined in the general plugin descriptor to associate the link between the plugin and the system and how much they interact. Next comes the implementation of the validation function and the update function for the system variable. It is worth noting that if you have a simple numeric such as integers for the variable types, the validate function is often not required as MySQL will handle the automatic check and validation of simple types.
/* longest valid value */
#define MAX_LOGLEVEL_SIZE 100
/* hold the valid values */
static const char *possible_modes[]= {
"LOG_ERROR",
"LOG_WARNING",
"LOG_NOTICE",
NULL
};
static int audit_loglevel_check(
THD* thd, /*!< in: thread handle */
struct st_mysql_sys_var* var, /*!< in: pointer to system
variable */
void* save, /*!< out: immediate result
for update function */
struct st_mysql_value* value) /*!< in: incoming string */
{
char buff[MAX_LOGLEVEL_SIZE];
const char *str;
const char **found;
int length;
length= sizeof(buff);
if (!(str= value->val_str(value, buff, &length)))
return 1;
/*
We need to return a pointer to a locally allocated value in "save".
Here we pick to search for the supplied value in an global array of
constant strings and return a pointer to one of them.
The other possiblity is to use the thd_alloc() function to allocate
a thread local buffer instead of the global constants.
*/
for (found= possible_modes; *found; found++)
{
if (!strcmp(*found, str))
{
*(const char**)save= *found;
return 0;
}
}
return 1;
}
The validation function is simply to take the value being passed in via the SET GLOBAL VARIABLE command and check if it is one of the pre-defined values allowed in our possible_values array. If it is found to be valid, then the value is assigned to the save variable ready for passing through to the update function.
static void audit_loglevel_update(
THD* thd, /*!< in: thread handle */
struct st_mysql_sys_var* var, /*!< in: system variable
being altered */
void* var_ptr, /*!< out: pointer to
dynamic variable */
const void* save) /*!< in: pointer to
temporary storage */
{
/* assign the new value so that the server can read it */
*(char **) var_ptr= *(char **) save;
/* assign the new value to the internal variable */
audit_loglevel= *(char **) save;
}
Since all the validation has been done already, the update function is quite simple for this plugin. The first part is to update the system variable pointer so that the server can read the value. The second part is to update our own global plugin variable for tracking the value. Notice that the save variable is passed in as a void type to allow handling of various data types, so it must be cast to the appropriate data type when assigning it to the variables.
Looking at how the latest changes affect the usage of the plugin and the interaction within the server shows:
mysql> show global variables like "audit%";
+-----------------------+------------+
| Variable_name | Value |
+-----------------------+------------+
| audit_syslog_loglevel | LOG_NOTICE |
+-----------------------+------------+
1 row in set (0.00 sec)
mysql> set global audit_syslog_loglevel="LOG_ERROR";
Query OK, 0 rows affected (0.00 sec)
mysql> show global status like "audit%";
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| Audit_syslog_connection_events | 1 |
| Audit_syslog_general_events | 11 |
| Audit_syslog_total_calls | 12 |
+--------------------------------+-------+
3 rows in set (0.00 sec)
mysql> show global variables like "audit%";
+-----------------------+-----------+
| Variable_name | Value |
+-----------------------+-----------+
| audit_syslog_loglevel | LOG_ERROR |
+-----------------------+-----------+
1 row in set (0.00 sec)
So now we have a plugin that will audit the events on the system and log the details to the system log.
It allows for interaction to see the number of different events within the server details and provides a mechanism to change the logging
level interactively via the standard system methods of the SET command.
A more complex auditing plugin may have more detailed code, but each of the above areas is what will be involved and simply expanded on
to add more functionality. With the above skeleton code, it is now possible to create your own audit plugins to implement your own
auditing requirements. If, however, you are not of the coding persuasion, then you could always consider the option of the MySQL Enterprise Audit
plugin that is available to purchase.
