Mysql對使用者操作加審計功能——進階版

標籤：style   blog   color   io   os   ar   for   檔案   div   

在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, #登入名稱稱  `matchname` varchar(30) DEFAULT NULL, #登入使用者  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2：在設定檔中配置init-connect參數。登入時插入日誌表。如果這個參數是個錯誤的SQL語句，登入就會失敗。

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

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 -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 65Server 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 | [email protected] | [email protected] |+----+-----------+---------------------+-----------------+-----------------+1 row in set (0.00 sec)mysql> show processlist;+----+-------+----------------+------+---------+------+-------+------------------+| 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 -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 76Server version: 5.1.45-community-logType ‘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 awayNo connection. Trying to reconnect...Connection id: 77Current database: *** NONE ***ERROR 2013 (HY000): Lost connection to MySQL server during querymysql> select * FROM accesslog.accesslog;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 78Current database: *** NONE ***

看下錯誤記錄檔 

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 | [email protected] | [email protected] || 2 | 91 | 2011-03-11 19:28:33 | [email protected] | [email protected] || 3 | 2 | 2011-03-11 19:31:49 | [email protected] | [email protected] || 4 | 2 | 2000-10-10 10:10:10 | [email protected] | [email protected] || 5 | 21 | 2000-10-10 11:11:11 | [email protected] | [email protected]% || 6 | 69 | 2011-03-12 21:35:43 | [email protected] | [email protected] |+----+-----------+---------------------+-----------------+-----------------+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=0use text/*!*/;SET TIMESTAMP=1299936961/*!*/;insert into t3 values(10,10,‘2011-10-10 00:00:00‘)/*!*/;# at 453

 thread_id=69
在日誌表裡記錄的和記錄檔裡面記錄的相同。可以通過這個thread_id來追蹤到是誰，什麼時間，做了什麼操作。

Mysql對使用者操作加審計功能——進階版