MySQL enables audit for common users (without super)

MySQL enables audit for common users (without super)

Recently, it was found that the bad guy deleted the data and could not find the user and host. It was found that mysql was able to enable the audit function for users without the super permission, and Oracle had implemented it for a long time, you only need to open the parameter.

1. Join in the my. cnf [mysqld] Group

Init-connect = 'insert into aduIt. accesslog (id, time, localname, matchname) values (connection_id (), now (), user (), current_user ());'

# Create database accesslog;

Create table aduit. accesslog ('id' int (11) primary keyauto_increment, 'time' timestamp, 'localname' varchar (30), 'matchname' varchar (30) engine = innodb;

2. the user to be audited must have the insert aduit. accesslog permission.

Grant insert on aduit. accesslog to hhl @ '192. 172.% 'identified by 'xxx ';

3. You can only audit the DDL and DML of normal users who do not have the super permission, and enable binlog analysis.

 

[Root @ wy ~] # Mysql-upxy-p-P3306-h 10.45.247.81

Enter password:

Welcome to the MySQL monitor. Commands end with; or \ g.

Mysql> insert into test. t10 values (10 );

Query OK, 1 row affected (0.00 sec)

# Above client connection Processid = 11

Mysql> select * from aduit. accesslog;

+ ---- + --------------------- + ----------- +

| Id | time | localname | matchname |

+ ---- + --------------------- + ----------- +

| 3 | 17:16:06 | admin@10.45.247.160 | admin @ % |

| 10 | 17:26:18 | pxy@10.45.247.160 | pxy @ % |

| 11 | 17:28:13 | pxy@10.45.247.160 | pxy @ % |

+ ---- + --------------------- + ----------- +

Processid = 11,

Mysql> show processlist;

+ ---- + ----------------- + ------------------- + ------ + --------- + ------ + -------------------------- + ------------------ +

| Id | User | Host | db | Command | Time | State | Info |

+ ---- + ----------------- + ------------------- + ------ + --------- + ------ + -------------------------- + ------------------ +

| 2 | root | localhost | NULL | Query | 0 | NULL | showprocesslist |

| 11 | pxy | 10.45.247.160: 53086 | NULL | Sleep | 26 | NULL |

+ ---- + ----------------- + ------------------- + ------ + --------- + ------ + -------------------------- + ------------------ +

The id = 11 is the id column of the aduit. accesslog table.

By parsing binlog

[Root @ localhost binlog] # mysqlbinlog -- base64-output = decode-rows-v mysql-bin.000030 | grep-nthread_id = 11

22: #140924 17:28:13 server id 114 end_log_pos 282 Query thread_id = 11 exec_time = 0 error_code = 0

24: SET @ session. pseudo do_thread_id = 11 /*! */;

42: #140924 17:32:21 server id 114 end_log_pos 491 Query thread_id = 11 exec_time = 0 error_code = 0

Locate the location of the fuzzy row, and then find the Processid = 11 operation in binlog.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: