Share an example of mysql using init-connect to add access audit function

The following editor will bring you an article on the implementation of mysql using init-connect to increase the access audit function. The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor and take a look.

Mysql connections 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, user name and user address during init-connect.

Implementation steps

1. Create a database table for auditing.

In order not to conflict with the business library, create your own library separately:

#建库表代码
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, and be sure to add it 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 permission to the accesslog table

This point is very important

This parameter only takes effect for ordinary users, not those with super permissions.

If you are an ordinary user, after adding this function, you must authorize:

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

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

The accesslog table does not have insert permission for users:

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 the audit function

#A user deleted a table in the test library. Let’s see if we can track it with the binlog log. Which user:

Check the binlog:

You can see which user performed the operation to complete the audit.

The above is the detailed content of Share an example of mysql using init-connect to add access audit function. For more information, please follow other related articles on the PHP Chinese website!