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

The mysql connection must first be initialized through init-connect and then connected to the instance.

We take advantage of this and implement the db access audit function by recording the user's thread_id, username and user address during init-connect.

Implementation steps

1. Create a library table for auditing.

In order to avoid conflicts with business libraries, create your own library separately:

#Create database table code create database db_monitor;
use db_monitor ;
CREATE TABLE accesslog
(thread_id int(11) DEFAULT NULL, #process id
 log_time datetime default null, #login time localname varchar(50) DEFAULT NULL, #login name with detailed IP
 matchname varchar(50) DEFAULT NULL, #Login user key idx_log_time(log_time)
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 

2. Configure init-connect parameters

This parameter can be adjusted dynamically. Please also note that it must be added to the configuration file my.cnf, otherwise it will become invalid after the next restart.

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());'; 

3. Grant ordinary users insert permissions to the accesslog table

This point is important

This parameter is only effective for ordinary users and will not work for users with super permissions.

If you are a normal user, you must authorize after adding this function:

grant insert on db_monitor.accesslog to user@'xx.xx.xx.%';

The consequence of not authorizing is that connecting to the database will fail:

Users who do not have insert permission on the accesslog table:

mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 7
Current database: *** NONE ***

ERROR 1184 (08S01): Aborted connection 7 to db: 'unconnected' user: 'user2' host: 'localhost' (init_connect command failed) 

4. Verify audit function

A user deleted a table in the test database. Let's see if we can track the user with the help of binlog log:

View binlog:

It can be seen which user performed the operation, thus completing the audit.

The above article about how to use init-connect to increase the access audit function in MySQL is all I have to share with you. I hope it can give you a reference, and I also hope that you will support 123WORDPRESS.COM.