mysql 用init-connect+binlog实现用户操作追踪做access 的ip的log 记录

最新推荐文章于 2021-12-29 14:52:48 发布

weixin_33785972

最新推荐文章于 2021-12-29 14:52:48 发布

阅读量253

点赞数

文章标签：数据库操作系统

原文链接：http://blog.51cto.com/yingmeng/1561535

版权

在MYSQL中，每个连接都会先执行init-connect，进行连接的初始化。我们可以在这里获取用户的登录名称和thread的ID值。然后配合binlog，就可以追踪到每个操作语句的操作时间，操作人等。实现审计。

实验过程：
1：创建登录日志库，登录日志表

CREATE DATABASE `accesslog`;
USE
`accesslog`;
CREATE
TABLE
`accesslog`
(
`id`
int(11)
NOT
NULL
AUTO_INCREMENT,
`thread_id`
int(11)
DEFAULT
NULL,
#线程ID，这个值很重要
`log_time`
timestamp
NOT
NULL
DEF AULT CURRENT_TIMESTAMP ON
UPDATE
CURRENT_TIMESTAMP,
#登录时间
`localname` varchar(30) DEFAULT NULL, #登录名称带IP
`matchname` varchar(30) DEFAULT NULL, #登录用户，user的全称
PRIMARY KEY
(`id`)
)
ENGINE=InnoDB
AUTO_INCREMENT=1
DEFAULT
CHARSET=utf8;

2：在配置文件中配置init-connect参数。登录时插入日志表。如果这个参数是个错误的SQL语句，登录就会失败。
Linux 下的配置文件为 my.cnf,windows下位my.ini

init-connect='insert into accesslog.accesslog values(null,connection_id(),now(),user(),current_user());'
log-bin

重启service mysqld 以使其配置文件生效

3：创建普通用户，不能有super权限。init-connect对具有super权限的用户不起作用。同时此用户必须要有INSERT权限，如果没有，登录后的任何操作都会导致MYSQL登录失败。

grant insert,select,update on *.* to 'user1'@'localhost'; #带INSERT权限
grant
select,update
on
*.*
to
'user2'@'localhost';
#不带INSERT权限

4：SESSION1登录，并查看日志

D:\mysql6\bin>mysql -uuser1 -p
Enter
password:
Welcome
to
the MySQL monitor.
Commands
end
with
;
or
\g.
Your MySQL connection id
is
65
Server
version:
5.1.45-community-log
MySQL Community Server
(GPL)
Type
'help;'
or
'\h'
for
help.
Type
'\c'
to
clear the current input statement.
mysql>
select
*
FROM
accesslog.accesslog;
+----+-----------+---------------------+-----------------+-----------------+
|
id
|
thread_id
|
log_time
|
localname
|
matchname
|
+----+-----------+---------------------+-----------------+-----------------+
|
1
|
65
|
2011-03-11 19:18:25
|
user1@localhost
|
user1@localhost
|
+----+-----------+---------------------+-----------------+-----------------+
1 row
in
set
(0.00 sec)
mysql> show processlist;# 当前运行的threadId
+----+-------+----------------+------+---------+------+-------+------------------+
|
Id
|
User
|
Host
|
db
|
Command
|
Time
|
State
|
Info
|
+----+-------+----------------+------+---------+------+-------+------------------+
|
65
|
user1
|
localhost:1339
|
NULL
|
Query
|
0
|
NULL
|
show
processlist
|
+----+-------+----------------+------+---------+------+-------+------------------+
1 row
in
set
(0.00 sec)
mysql>

5：再用user2登录

D:\mysql6\bin>mysql -uuser2 -p
Enter
password:
Welcome
to
the MySQL monitor.
Commands
end
with
;
or
\g.
Your MySQL connection id
is
76
Server
version:
5.1.45-community-log
Type
'help;'
or
'\h'
for
help.
Type
'\c'
to
clear the current input statement.
mysql>
select
*
FROM
accesslog.accesslog;
ERROR 2006
(HY000):
MySQL server has gone away
No connection.
Trying
to
reconnect...
Connection id: 77
Current
database:
***
NONE
***
ERROR 2013
(HY000):
Lost connection
to
MySQL server during query
mysql>
select
*
FROM
accesslog.accesslog;
ERROR 2006
(HY000):
MySQL server has gone away
No connection.
Trying
to
reconnect...
Connection id: 78
Current
database:
***
NONE
***

看下错误日志
如果没有对log-bin指定log文件，默认在 /var/lib/mysql目录下以mysqld-bin.00000X等作为名称。而 mysqld-bin.index则记录了所有的log的文件名称
使用时则使用mysqlbinlog /var/lib/mysql|grep "*****"等来追踪database的操作。

110311 19:23:47 [Warning] Aborted connection 77 to db: 'unconnected' user: 'user2' host: 'localhost' (init_connect command failed)
110311 19:23:47
[Warning]
INSERT
command denied
to
user
'user2'@'localhost'
for
table
'accesslog'
110311 19:23:53
[Warning]
Aborted connection 78
to
db:
'unconnected'
user:
'user2'
host:
'localhost'
(init_connect command failed)
110311 19:23:53
[Warning]
INSERT
command denied
to
user
'user2'@'localhost'
for
table
'accesslog'

6：下面以USER1登录，并做一个INSERT操作，查看日志文件。

mysql> insert into t3 values(10,10,'2011-10-10 00:00:00');
Query OK,
1 row affected
(0.00 sec)
mysql>
show
processlist;
+----+-------+----------------+-----------+---------+------+-------+------------------+
|
Id
|
User
|
Host
|
db
|
Command
|
Time
|
State
|
Info
|
+----+-------+----------------+-----------+---------+------+-------+------------------+
|
69
|
user1
|
localhost:1439
|
accesslog
|
Query
|
0
|
NULL
|
show
processlist
|
+----+-------+----------------+-----------+---------+------+-------+------------------+
1 row
in
set
(0.00 sec)
mysql>
select
*
from
accesslog.accesslog;
+----+-----------+---------------------+-----------------+-----------------+
|
id
|
thread_id
|
log_time
|
localname
|
matchname
|
+----+-----------+---------------------+-----------------+-----------------+
|
1
|
65
|
2011-03-11 19:18:25
|
user1@localhost
|
user1@localhost
|
|
2
|
91
|
2011-03-11 19:28:33
|
user1@localhost
|
user1@localhost
|
|
3
|
2
|
2011-03-11 19:31:49
|
user1@localhost
|
user1@localhost
|
|
4
|
2
|
2000-10-10 10:10:10
|
user1@localhost
|
user1@localhost
|
|
5
|
21
|
2000-10-10 11:11:11
|
root@localhost
|
root@%
|
|
6
|
69
|
2011-03-12 21:35:43
|
user1@localhost
|
user1@localhost
|
+----+-----------+---------------------+-----------------+-----------------+
6 rows
in
set
(0.01 sec)

查看日志文件的内容

# at 340
#110312 21:36:01 server id 1 end_log_pos 453 Query thread_id=69 exec_time=0 error_code=0
use
text;
SET
TIMESTAMP=1299936961;
insert
into
t3
values(10,10,'2011-10-10 00:00:00')
;
# at 453

thread_id=69

在日志表里记录的和日志文件里面记录的相同。可以通过这个thread_id来追踪到是谁，什么时间，做了什么操作。

转载于:https://blog.51cto.com/yingmeng/1561535

weixin_33785972

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql 用init-connect+binlog实现用户操作追踪做access 的ip的log 记录

在MYSQL中，每个连接都会先执行init-connect，进行连接的初始化。我们可以在这里获取用户的登录名称和thread的ID值。然后配合binlog，就可以追踪到每个操作语句的操作时间，操作人等。实现审计。实验过程：1：创建登录日志库，登录日志表CREATEDATABASE`accesslog`;USE`accesslog`;CREATETABLE`acces...
复制链接

扫一扫