How to track user operation (add, delete and change) records in MySql

Sometimes, we want to track a database operation record, such as to find out who operated a table (such as who changed the field name).

Binary log records operation record, thread number and other information, but it does not record user information, so it needs to be combined with init connect to achieve tracking.

Init connect, in the initialization stage of each connection, records the user of the connection and the connection ﹣ ID information.

 

Experimental steps:

1:Create a table to monitor connection information
use dba;
create table accesslog(`thread_id` int primary key auto_increment, `time` timestamp, `localname` varchar(40), `machine_name` varchar(40));
 
 
2: set variable init_connect
 
mysql> show variables like 'init%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| init_connect  |       |
| init_file     |       |
| init_slave    |       |
+---------------+-------+
3 rows in set (0.00 sec)
 
mysql> set global init_connect='insert into dba.accesslog(thread_id,time,localname,machine_name) values(connection_id(),now(),user(),current_user());';
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like 'init%';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| init_connect  | insert into dba.accesslog(thread_id,time,localname,machine_name) values(connection_id(),now(),user(),current_user()); |
| init_file     |                                                                                                                       |
| init_slave    |                                                                                                                       |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
 
 
3: Assign user rights
mysql> grant select,insert,update on dba.accesslog to baidandan@'192.168.9.45' identified by 'baidandan';
Query OK, 0 rows affected (0.00 sec)
 
 --To do the experiment, give baidandan Assignment operation dba.t Table permissions
mysql> grant select,delete on dba.t to baidandan@'192.168.9.45';
Query OK, 0 rows affected (0.00 sec)
 
4: test
--Client connection for testing
C:\Users\dandan>mysql -u baidandan -p -h 192.168.6.51
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 371
Server version: 5.6.20-r5436-log Source distribution
 
 
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
 
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
mysql> use dba;
Database changed
mysql> delete from t;
Query OK, 1 row affected (0.10 sec)
 
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
 
mysql> select * from t;
Empty set (0.00 sec)
 
 
//If I want to see who deleted the data in the DBA.t table now.
//View log:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |     1640 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 
--If I knew this data was deleted after 9:00 on June 18:
[root@ser6-51 data]# mysqlbinlog mysql-bin.000007 --start-datetime='2015-06-18 09:00:00'
 
--Delete statement found:
#150618 16:55:30 server id 1  end_log_pos 1609 CRC32 0xa2296c53 Query thread_id=371 exec_time=0 error_code=0
use `dba`/*!*/;
SET TIMESTAMP=1434617730/*!*/;
delete from t
/*!*/;
 
--query accesslog surface
mysql> select * from dba.accesslog where thread_id=371;
+-----------+---------------------+------------------------+------------------------+
| thread_id | time                | localname              | machine_name           |
+-----------+---------------------+------------------------+------------------------+
|       371 | 2015-06-18 16:55:19 | baidandan@192.168.9.45 | baidandan@192.168.9.45 |
+-----------+---------------------+------------------------+------------------------+
1 row in set (0.00 sec)
 

Note: all ordinary level users must have read and write permission to the log table, otherwise, users without permission will not be able to use the database.

Init connect will not record the connection information of users with super administrator rights (reason: when init connect is set incorrectly, super administrator can modify it)

Therefore, for ordinary users, all privileges cannot be granted.

-- if you want to view all the added, deleted, modified and queried records, query in the general log. It records the connected users and IP information. Such as:

2016-10-08T12:09:58.476859Z   57 Connect baidandan@10.0.9.121 on dba using TCP/IP

--This article is from: http://blog.csdn.net/ljasdf123/article/details/14166793

Tags: MySQL Database Oracle mysqlbinlog

Posted on Wed, 01 Jan 2020 20:40:14 -0800 by lc21