mysql audit-訪問日志記錄

背景:
假設這麼一個情況,你是某公司mysql-DBA,某日突然公司數據庫中的所有被人為刪瞭。
盡管有數據備份,但是因服務停止而造成的損失上千萬,現在公司需要查出那個做刪除操作的人。
但是擁有數據庫操作權限的人很多,如何排查,證據又在哪?
是不是覺得無能為力?
mysql本身並沒有操作審計的功能,那是不是意味著遇到這種情況隻能自認倒黴呢?
本文就將討論一種簡單易行的,用於mysql訪問審計的思路。
 
概述:
其實mysql本身已經提供瞭詳細的sql執行記錄–general log ,但是開啟它有以下幾個缺點
無論sql有無語法錯誤,隻要執行瞭就會記錄,導致記錄大量無用信息,後期的篩選有難度。
sql並發量很大時,log的記錄會對io造成一定的印象,是數據庫效率降低。
日志文件很容易快速膨脹,不妥善處理會對磁盤空間造成一定影響。
本文觀點:
使用init-connect + binlog的方法進行mysql的操作審計。
由於mysql binlog記錄瞭所有對數據庫長生實際修改的sql語句,及其執行時間,和connection_id但是卻沒有記錄connection_id對應的詳細用戶信息。
因此本文將通過init-connect,在每次連接的初始化階段,記錄下這個連接的用戶,和connection_id信息。
在後期審計進行行為追蹤時,根據binlog記錄的行為及對應的connection-id 結合 之前連接日志記錄 進行分析,得出最後的結論。
 
 
正文:
1. 設置init-connect
1.1 創建用於存放連接日志的數據庫和表
create database accesslog;
CREATE TABLE accesslog.accesslog (`id` int(11) primary key auto_increment, `time` timestamp, `localname` varchar(30), `matchname` varchar(30))
1.2 創建用戶權限
可用現成的root用戶用於信息的讀取
grant read on accesslog.* to root@localhost identified by ‘password’;
如果存在具有to *.* 權限的用戶需要進行限制。
1.3 設置init-connect
在[mysqld]下添加以下設置:
init-connect=’insert into accesslog.accesslog values(connection_id(),user(),current_user(),now());’
log-bin
1.4 重啟數據庫生效
shell> service mysqld restart
 
2. 記錄追蹤
2.1 thread_id確認
假設想知道在2009年11月25日,上午9點多的時候,是誰吧test.dummy這個表給刪瞭。可以用以下語句定位
mysqlbinlog –start-datetime=’2009-11-25 09:00:00′ –stop-datetime=’2009-11-25 09:00:00′  binlog.xxxx | grep ‘dummy’ -B 5
會得到如下結果(可見thread_id為5):
# at 300777
#091124 16:54:00 server id 10  end_log_pos 301396       Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1259052840;
drop table test.dummy;
2.2 用戶確認
thread_id 確認以後,找到元兇就隻是一條sql語句的問題瞭。
select * from accesslog.accesslog where conn_id=5 ;
就能發現是testuser2@localhost幹的瞭。
 
+——+——————————-+——————————-+—————————–+
| id   | time                        | localname              | matchname          |
+——+——————————-+——————————-+—————————–+
|   5  | 2009-11-25 10:57:39 | testuser2@localhost | testuser2@%        |
+——+——————————-+——————————-+—————————–+
 
3. Q&A
Q:使用init-connect會影響服務器性能嗎?
A:理論上,隻會在用戶每次連接時往數據庫裡插入一條記錄,不會對數據庫產生很大影響。除非連接頻率非常高(當然,這個時候需要註意的就是如何進行連接復用和控制,而非是不是要用這種方法的問題瞭)
Q:access-log表如何維護?
A: 由於是一個log系統,推薦使用archive存儲引擎,有利於數據厄壓縮存放。如果數據庫連接數量很大的話,建議一定時間做一次數據導出,然後清表。
Q:表有其他用途麼?
A:有!access-log表當然不隻用於審計,當然也可以用於對於數據庫連接的情況進行數據分析,例如每日連接數分佈圖等等,隻有想不到沒有做不到。
 
Q:會有遺漏的記錄嗎?
A:會的,init-connect 是不會在super用戶登錄時執行的。所以access-log裡不會有數據庫超級用戶的記錄,這也是為什麼我們不主張多個超級用戶,並且多人使用的原因。

作者 jerryswxs

You May Also Like