MySQL5.5的audit plugin

在MySQL5.5的版本中，增加了一个新的插件：audit plugin，用于对mysql的操作进行审计

相关代码:

sql/sql_audit.cc

plugin/audit_null文件夹下，有1个样板程序文件audit_null.c，主要包括四个函数：

1).audit_null_plugin_init:加载插件

2).audit_null_plugin_deinit:卸载插件

3).audit_null_notify: 实际执行具体功能的函数，参数包括thd以及event

4).mysql_declare_plugin：声明插件，用于描述该插件，如下

140 /* 141 Plugin library descriptor 142 */ 143 144 mysql_declare_plugin(audit_null) 145 { 146 MYSQL_AUDIT_PLUGIN, /* type */ 147 &audit_null_descriptor, /* descriptor */ 148 "NULL_AUDIT", /* name */ 149 "Oracle Corp", /* author */ 150 "Simple NULL Audit", /* description */ 151 PLUGIN_LICENSE_GPL, 152 audit_null_plugin_init, /* init function (when loaded) */ 153 audit_null_plugin_deinit, /* deinit function (when unloaded) */ 154 0x0002, /* version */ 155 simple_status, /* status variables */ 156 NULL, /* system variables */ 157 NULL 158 } 159 mysql_declare_plugin_end;

除了上述函数外，还定义了插件的类型描述符：

112 /* 113 Plugin type-specific descriptor 114 */ 115 116 static struct st_mysql_audit audit_null_descriptor= 117 { 118 MYSQL_AUDIT_INTERFACE_VERSION, /* interface version */ 119 NULL, /* release_thd function */ 120 audit_null_notify, /* notify function */ 121 { (unsigned long) MYSQL_AUDIT_GENERAL_CLASSMASK } /* class mask */ 122 };

以及show status时所显示的内容：

124 /* 125 Plugin status variables for SHOW STATUS 126 */ 127 128 static struct st_mysql_show_var simple_status[]= 129 { 130 { "Audit_null_called", (char *) &number_of_calls, SHOW_INT }, 131 { "Audit_null_general_log", (char *) &number_of_calls_general_log, SHOW_INT }, 132 { "Audit_null_general_error", (char *) &number_of_calls_general_error, 133 SHOW_INT }, 134 { "Audit_null_general_result", (char *) &number_of_calls_general_result, 135 SHOW_INT }, 136 { 0, 0, 0} 137 };

这些函数会在sql/sql_audit.cc文件中以函数指针的形式被调用。下面是我参考网上的一个样例写的程序

这个程序实现了将mysql_event_general结构体的general_query字段写到一个日志文件里的功能

17 #include <stdio.h> 18 #include <mysql/plugin.h> 19 #include <mysql/plugin_audit.h> 20 21 #if !defined(__attribute__) && (defined(__cplusplus) || !defined(__GNUC__) || __GNUC__ == 2 && __GNUC_MINOR__ < 8) 22 #define __attribute__(A) 23 #endif 24 25 static FILE *log_fp; 26 static volatile int number_of_calls; /* for SHOW STATUS, see below */ 27 static volatile int number_of_calls_general_log; 28 static volatile int number_of_calls_general_error; 29 static volatile int number_of_calls_general_result; 30 31 32 /* 33 Initialize the plugin at server start or plugin installation. 34 35 SYNOPSIS 36 audit_null_plugin_init() 37 38 DESCRIPTION 39 Does nothing. 40 41 RETURN VALUE 42 0 success 43 1 failure (cannot happen) 44 */ 45 46 static int audit_null_plugin_init(void *arg __attribute__((unused))) 47 { 48 number_of_calls= 0; 49 number_of_calls_general_log= 0; 50 number_of_calls_general_error= 0; 51 number_of_calls_general_result= 0; 52 53 log_fp = NULL; 54 55 return(0); 56 } 57 58 59 /* 60 Terminate the plugin at server shutdown or plugin deinstallation. 61 62 SYNOPSIS 63 audit_null_plugin_deinit() 64 Does nothing. 65 66 RETURN VALUE 67 0 success 68 1 failure (cannot happen) 69 70 */ 71 72 static int audit_null_plugin_deinit(void *arg __attribute__((unused))) 73 { 74 fclose(log_fp); 75 return(0); 76 } 77 78 79 /* 80 Foo 81 82 SYNOPSIS 83 audit_null_notify() 84 thd connection context 85 86 DESCRIPTION 87 */ 88 89 static void audit_null_notify(MYSQL_THD thd __attribute__((unused)), 90 unsigned int event_class, 91 const void *event) 92 { 93 /* prone to races, oh well */ 94 const struct mysql_event_general *pEvent; 95 96 if(log_fp == NULL) 97 log_fp = fopen("/tmp/null_audit.log", "a"); 98 number_of_calls++; 99 100 101 if (event_class == MYSQL_AUDIT_GENERAL_CLASS && log_fp != NULL){ 102 pEvent = (const struct mysql_event_general *) event; 103 if (pEvent->general_query != NULL && *(pEvent->general_query) != '\0') { 104 fprintf(log_fp, "%s;\n\n", pEvent->general_query); 105 fflush(log_fp); 106 } 107 } 108 } 109 110 111 /* 112 Plugin type-specific descriptor 113 */ 114 115 static struct st_mysql_audit audit_null_descriptor= 116 { 117 MYSQL_AUDIT_INTERFACE_VERSION, /* interface version */ 118 NULL, /* release_thd function */ 119 audit_null_notify, /* notify function */ 120 { (unsigned long) -1 } /* class mask */ 121 }; 122 123 /* 124 Plugin status variables for SHOW STATUS 125 */ 126 127 static struct st_mysql_show_var audit_null_status[]= 128 { 129 { "Audit_null_called", (char *) &number_of_calls, SHOW_INT }, 130 { "Audit_null_general_log", (char *) &number_of_calls_general_log, SHOW_INT }, 131 { "Audit_null_general_error", (char *) &number_of_calls_general_error, 132 SHOW_INT }, 133 { "Audit_null_general_result", (char *) &number_of_calls_general_result, 134 SHOW_INT }, 135 { 0, 0, 0} 136 }; 137 138 139 /* 140 Plugin library descriptor 141 */ 142 143 mysql_declare_plugin(audit_null) 144 { 145 MYSQL_AUDIT_PLUGIN, /* type */ 146 &audit_null_descriptor, /* descriptor */ 147 "NULL_AUDIT", /* 注意，这个名字就是要install的插件名，开始没弄清楚，一直输入audit_null，结果老是报错：Can't find symbol */ 148 "Oracle Corp", /* author */ 149 "Simple NULL Audit", /* description */ 150 PLUGIN_LICENSE_GPL, 151 audit_null_plugin_init, /* init function (when loaded) */ 152 audit_null_plugin_deinit, /* deinit function (when unloaded) */ 153 0x0002, /* version */ 154 audit_null_status, /* status variables */ 155 NULL, /* system variables */ 156 NULL 157 } 158 mysql_declare_plugin_end; 159

-----------------------------------------------------------------------------------------------------------------------------------------------

然后直接make && make install，生成的adt_null.so文件会被复制到安装目录/lib/plugin下

进入mysql，执行：

mysql> install plugin NULL_AUDIT soname 'adt_null.so';
Query OK, 0 rows affected (0.00 sec)

然后执行一系列的操作！我们会发现在tmp文件夹下创建了一个audit_null.log文件夹，记录了所有的操作命令

mysql> SHOW STATUS LIKE 'Audit_null%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| Audit_null_called | 44 |
| Audit_null_general_error | 0 |
| Audit_null_general_log | 0 |
| Audit_null_general_result | 0 |
+---------------------------+-------+
4 rows in set (0.00 sec)

最后：

在定义notify 函数的时候，可以看到参数为THD，由于THD结构体中记录了非常丰富的信息，因此我们可以实现一些非常有趣的功能，比较记录执行命令的用户名和域名，进行审计！

下一步，将分析MySQL源代码如何实现audit功能的，以对其进行修改，适应业务需求