Mysql uses init-connect to add the implementation of the access audit function, mysqlinit-connect

Mysql uses init-connect to add the implementation of the access audit function, mysqlinit-connect

Mysql is first initialized through init-connect and then connected to the instance.

We use this to record the user's thread_id, user name, and user address during init-connect to implement the Database Access Audit function.

Steps

1. Create a database table for audit.

To avoid conflicts with the Business Database, create your own database separately:

# Create database db_monitor; use db_monitor; create table accesslog (thread_id int (11) default null, # process id log_time datetime default null, # Logon 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 the init-connect Parameter

This parameter can be dynamically adjusted and 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 secmysql> set global init_connect='insert into db_monitor.accesslog(thread_id,log_time,localname,matchname) values(connection_id(),now(),user(),current_user());'; 

3. Grant normal users the insert permission on the accesslog table.

This is important.

This parameter takes effect only for common users.

If you are a common user, you must grant permissions after adding this function:

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

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

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

mysql> show databases;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id:  7Current 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 deletes a table from the test database to see if the user can be tracked using binlog:

View binlog:

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

The above implementation of the Access Audit function added by mysql using init-connect is all the content that I have shared with you. I hope to give you a reference and support for the customer's house.