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

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.