MySQL Forums
Forum List  »  Install & Repo

Using the Audit Plug-in API in MySQL
Posted by: nick dipiazza
Date: October 09, 2012 09:33AM

Using the Audit Plug-in API in MySQL
MySQL provides only very simple audit logging out of the box. Enabling the general query log, you can use the contents of this file to give you a history of all SQL calls. Furthermore, the error log provides you a history of the errors that occurred.
But working with the general query log can be troublesome mostly because of its enormous size. You may instead need a custom log with only certain DDL operations logged.
To get this custom audit functionality, you need to create a MySQL Audit Plug-in. This only works with MySQL 5.1 and greater.
Plug-ins in MySQL are C or C++ .so modules that call on the MYSQL Audit API to declare a plugin.
Learn By Example – An Audit Plug-in that Counts Different Kinds of MySQL Events
The simplest example out there is the sample audit plug-in provided with the MySQL Source Distribution at mysql-VERSION/plugins/audit_null/audit_null.c. Here is a link: http://code.metager.de/source/xref/mysql-server/plugin/audit_null/audit_null.c
This plugin creates a count of each different MySQL event broken down by Event Class.
To install the plug in:
1) You will need to generate a Makefile in order to build this plug-in.
Create mysql-VERSION/plugins/audit_null/Makefile and edit it with the following:

MYSQL_SRC=../../mysql-repo/mysql-5.5
MYSQL_INST=/usr/local/mysql5.5/lib/mysql/plugin
CFLAGS=-g -DMYSQL_DYNAMIC_PLUGIN -fPIC
all: audit_null.so

audit_null.o:
gcc $(CFLAGS) -I$(MYSQL_SRC)/include -I$(MYSQL_SRC)/regex -I$(MYSQL_SRC)/sql -c audit_null.c

audit_null.so: my_audit_null.o
gcc -shared audit_null.o -o audit_null.so

Figure 1: Makefile for audit_null plugin
Note:
• You will have to set MYSQL_SRC to where you extracted the MySQL source tar ball.
• You also have to update MYSQL_INST to point to your MySQL plugin directory. Remember, this only works for MySQL 5.1 or later.

2) make && make install
This compiles the plugin and puts it in your MySQL plugins directory. But this still doesn’t actually tell MySQL the plugin is there yet.
3) Go to MySQL and run the following command:

install plugin audit_null soname 'audit_null.so';

To verify plugin installation
1) SELECT * FROM INFORMATION_SCHEMA.PLUGINS
Or
2) SHOW PLUGINS
How does it work?
Let’s open up audit_null.c and start explaining how this works:
• Imports the MySQL plugin API using
#include <mysql/plugin.h>
#include <mysql/plugin_audit.h>

• Declare the plugin using the Plugin Library Descriptor
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;

Everything bases on the declaration of this call to mysql_declare_plugin. Here is a brief description of some of the important values:
• Type: Declares a type for the plugin used by MySQL.
• Descriptor: Used to define the listener and what MySQL audit events to listen to. Here is the descriptor:
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 }
/* what classes to listen to */
};

So you see this is where the notify function gets set. In our case, it is a function called audit_null_notify:
static void audit_null_notify(MYSQL_THD thd __attribute__((unused)),
unsigned int event_class,
const void *event)

MySQL will call this listener function every time a new sql event happens. Each event has an event class and subclass. Here is a description:
• MYSQL_AUDIT_GENERAL_CLASS
 MYSQL_AUDIT_GENERAL_LOG
 MYSQL_AUDIT_GENERAL_ERROR
 MYSQL_AUDIT_GENERAL_RESULT
 MYSQL_AUDIT_GENERAL_STATUS
• MYSQL_AUDIT_CONNECTION_CLASS
 MYSQL_AUDIT_CONNECTION_CONNECT
 MYSQL_AUDIT_CONNECTION_DISCONNECT
 MYSQL_AUDIT_CONNECTION_CHANGE_USER
In this example, the event subclass is counted and that’s all the plug-in does. But the mysql-event-general struct contains many useful parameters:
struct mysql_event_general
{
unsigned int event_subclass;
int general_error_code;
unsigned long general_thread_id;
const char *general_user; /* user id */
unsigned int general_user_length;
const char *general_command; /* command */
unsigned int general_command_length;
const char *general_query; /* the sql query */
unsigned int general_query_length;
struct charset_info_st *general_charset;
unsigned long long general_time; /* timestamp */
unsigned long long general_rows; /* rows affected */

The next example uses some of these attributes to do interesting auditing.
• Init Function: Function called when the plug-in first loads.
• De-init function: Function called when the plug-in is uninstalled or server shuts down.
• Status variables: These are variables that are exposed to the MySQL clients as “Status Variables” prefixed by the audit plugin’s name. So in this example, you would be able to view the status variables like this:
SHOW STATUS LIKE 'Audit_null%';

• System variables: Same thing as status variables, but saves them to system variables.
A More Complex Example:
Here is another example: http://www.papablues.com/src/my_proclist.tar.gz
This plug-in does some advanced analytics on the MySQL queries. It provides a list of the 30 most commonly called functions in the Database. It normalizes the SQL so that the variables are replaced with question marks.
Let’s take a look at the files inside:
1) my_proclist.cc
Important differences than the previous example right off the bat:
o Doesn’t just use the Audit API, also utilizes the Information Schema Plug-in API
o Shows how to work with the query itself and how to parse it to a normal form
2) normalize.c and normalize.h
a. Code library pulled in that can normalize a SQL query
3) Makefile
a. Builds the plug-in (which includes building normalize.c/h dependency)

Creating our Own Plug-in - Generate a different log file for each different SQL type
Starting with the audit_null.c plugin, we can easily create a new plug-in that creates a different log file for each kind of sql statement. For example,
• Create table - goes to create-table.log
• Update – goes to update.log
• Delete – goes to delete.log
Steps to create the plugin
1) Let’s call our new plugin finelog. So copy audit_null.c to finelog.c.
2) Edit finelog.c
a. Replace all “audit_null” to “finelog”.
b. Create a new File descriptor at the global level for each log you want to keep:
static FILE *log_create;
static FILE *log_update;
static FILE *log_delete;
c. Open the files in the init method and close them in the deinit method:
static int finelog_init(void *arg __attribute__((unused)))
{
log_create = fopen("/tmp/create.log", "a");
log_update = fopen("/tmp/update.log", "a");
log_delete = fopen("/tmp/delete.log", "a");
return(0);
}

static int finelog_deinit(void *arg __attribute__((unused)))
{
fclose(log_create);
fclose(log_update);
fclose(log_delete);
return(0);
}


d. Now implement the notify function so that it will read the query and log the result to the respective file:
static void finelog_notify(MYSQL_THD thd, const struct mysql_event *event) {
mysql_event_general *pEvent;
char * pch;
if (event->event_class == MYSQL_AUDIT_GENERAL_CLASS) {
pEvent = (mysql_event_general *) event;
if (pEvent->general_query != NULL && *(pEvent->general_query) != '\0') {
if (strnicmp(pEvent->general_query, "create table", 12) == 0) {
fprintf(log_create, "%s;\n\n", pEvent->general_query);
fflush(log_create);
} else if(strnicmp(pEvent->general_query, "update", 6) == 0) {
fprintf(log_update, "%s;\n\n", pEvent->general_query);
fflush(log_update);
} else if(strnicmp(pEvent->general_query, "delete from", 11) == 0) {
fprintf(log_delete, "%s;\n\n", pEvent->general_query);
fflush(log_delete);
}
}
}
}


References:
http://dev.mysql.com/doc/refman/5.5/en/writing-audit-plugins.html
http://karlssonondatabases.blogspot.com/2010/03/mysql-audit-plugin-api.html
http://karlssonondatabases.blogspot.com/2010/03/more-fun-with-mysql-audit-plugin-api.html

Options: ReplyQuote


Subject
Written By
Posted
Using the Audit Plug-in API in MySQL
October 09, 2012 09:33AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.