Use the audit function of mysql to record user operation information

MysqlIf you want to record user operation information in the database, you can useAuditAudit function. This function is automatically triggered. You can see more detailed definitions in the plugin_audit.h file. In the audit plug-in, controllable variables include THD and events.

There are two types of event structures, which can be forced conversion:

First:

 
 

  
  
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;   
  
  
 
  
  
}; 
 
 

Trigger condition:

# Define MYSQL_AUDIT_GENERAL_LOG 0: triggered before being submitted to the general query log.

# Define MYSQL_AUDIT_GENERAL_ERROR 1: triggered before an error is sent to the user.

# Define MYSQL_AUDIT_GENERAL_RESULT 2: triggered when the result set is sent to the user.

# Define MYSQL_AUDIT_GENERAL_STATUS 3: triggered when a result set is sent or an error occurs.

Second:

 
 

  
  
struct mysql_event_connection   
  
  
 
  
  
{  
  
  
 
  
  
unsigned int event_subclass;   
  
  
 
  
  
int status;   
  
  
 
  
  
unsigned long thread_id;   
  
  
 
  
  
const char *user;   
  
  
 
  
  
unsigned int user_length;   
  
  
 
  
  
const char *priv_user;   
  
  
 
  
  
unsigned int priv_user_length;   
  
  
 
  
  
const char *external_user;   
  
  
 
  
  
unsigned int external_user_length;   
  
  
 
  
  
const char *proxy_user;   
  
  
 
  
  
unsigned int proxy_user_length;   
  
  
 
  
  
const char *host;   
  
  
 
  
  
unsigned int host_length;   
  
  
 
  
  
const char *ip;   
  
  
 
  
  
unsigned int ip_length;   
  
  
 
  
  
const char *database;   
  
  
 
  
  
unsigned int database_length;   
  
  
 
  
  
}; 
 
 

Trigger condition:

# Define MYSQL_AUDIT_CONNECTION_CONNECT 0: triggered after authentication is completed.

# Define MYSQL_AUDIT_CONNECTION_DISCONNECT 1: triggered when the connection is interrupted.

# Define MYSQL_AUDIT_CONNECTION_CHANGE_USER 2: triggered after the COM_CHANGE_USER command is executed.

From the above analysis, we can see that a wealth of information is stored in the event, and the notify function is modified as follows:

 
 

  
  
static void audit_null_notify(MYSQL_THD thd __attribute__((unused)),   
  
  
 
  
  
unsigned int event_class,  
  
  
 
  
  
const void *event)  
  
  
 
  
  
{   
  
  
 
  
  
const struct mysql_event_general *pEvent;  
  
  
 
  
  
if (log_fp == NULL)  
  
  
 
  
  
log_fp = fopen("/tmp/rec.log", "a");  
  
  
 
  
  
number_of_calls++;  
  
  
 
  
  
if (event_class == MYSQL_AUDIT_GENERAL_CLASS && log_fp != NULL){  
  
  
 
  
  
pEvent = (const struct mysql_event_general *) event;  
  
  
 
  
  
if ( pEvent->event_subclass == MYSQL_AUDIT_GENERAL_RESULT &&  
  
  
 
  
  
pEvent->general_query != NULL  
  
  
 
  
  
&& *(pEvent->general_query) != '\0') {   
  
  
 
  
  
// fprintf(log_fp, "user:%s,host:%s,command:%s\n",&thd->security_ctx->priv_user[0],   
  
  
 
  
  
// (char *) thd->security_ctx->host_or_ip ,  
  
  
 
  
  
// pEvent->general_query);  
  
  
 
  
  
time_t cur = time(NULL);  
  
  
 
  
  
fprintf(log_fp, "%s %s\n%s\n", ctime(&cur) , pEvent->general_user , pEvent->general_query);  
  
  
 
  
  
fflush(log_fp);  
  
  
 
  
  
}  
  
  
 
  
  
}  
  
  
 
  
  
} 
 
 

In this way, we can record user name, user host information, SQL operations, and other related information.