MySQL uses init-connect to increase the access audit function implementation

The MySQL connection is first initialized via Init-connect and then connected to the instance.

We take advantage of this by recording the user's thread_id, user name and user address in Init-connect to achieve DB access auditing.

Implementation steps

1. Create a library table for auditing purposes.

In order to not conflict with the business's library, create your own library individually:

#建库表代码 create database db_monitor ; use db_monitor ; CREATE TABLE accesslog ( thread_id int(11) DEFAULT NULL,  #进程id  log_time datetime default null,  #登录时间  localname varchar(50) DEFAULT NULL, #登录名称，带详细ip  matchname varchar(50) DEFAULT NULL, #登录用户  key idx_log_time(log_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Configure Init-connect Parameters

This parameter can be dynamically adjusted, but also note to add to the configuration file my.cnf, or the next restart after the failure;

mysql> show variables like ‘init_connect%‘ ; + ---------------+-------+ | Variable_name | Value | + ---------------+-------+ | init_connect |    | + ---------------+-------+ 1 row in set (0.00 sec  

mysql>set global init_connect=‘insert into db_monitor.accesslog(thread_id, log_time,localname,matchname) values(connection_id(),now(),user(),current_user());‘; 

Add in my.conf

init_connect= ' INSERT into Db_monitor.accesslog (thread_id, Log_time,localname,matchname) VALUES (connection_id (), now (), User (), current_user ()); '

3, grant the ordinary user insert permission to the Accesslog table

This point is important.

This parameter is only valid for ordinary users, and the Super privilege does not work.

If you are an ordinary user, you will need to authorize this feature if you add it:

Grant insert on Db_monitor.accesslog to [email protected] '% ';

The consequence of an unauthorized connection is that the database will fail:

Accesslog table does not have insert permissions for users:

4, verify the audit function

A user has deleted a table from the test library to see if we can track the Binlog log to which user:

650) this.width=650; "src=" http://files.jb51.net/file_images/article/201703/20170331133321.jpg "/>

View Binlog:

650) this.width=650; "style=" Width:650px;height:auto; "src=" http://files.jb51.net/file_images/article/201703/ 20170331133337.jpg "/>

It is possible to see which user is doing the work, thus completing the audit.

Home of the original script

This article is from the "Knife" blog, please be sure to keep this source http://quyong.blog.51cto.com/2434342/1945674

MySQL uses init-connect to increase the access audit function implementation