mysql開啟用戶(登錄)審計功能

 

背景:

假設這麼一個情況,你是某公司mysql-DBA,某日突然公司數據庫中的所有被人為刪瞭。

盡管有數據備份,但是因服務停止而造成的損失上千萬,現在公司需要查出那個做刪除操作的人。

但是擁有數據庫操作權限的人很多,如何排查,證據又在哪?

是不是覺得無能為力?

mysql本身並沒有操作審計的功能,那是不是意味著遇到這種情況隻能自認倒黴呢?

本文就將討論一種簡單易行的,用於mysql訪問審計的思路。

 

概述:

其實mysql本身已經提供瞭詳細的sql執行記錄–general log(詳見上篇blog) ,但是開啟它有以下幾個缺點

無論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 *.* 權限的用戶需要進行限制。

當前登錄用戶需要對accesslog庫至少具有insert權限

1.3 設置init-connect

在[mysqld]下添加以下設置:

init-connect=’insert into accesslog.accesslog values(connection_id(),now(),user(),current_user());’—註意insert句子的語法、引號正確,如果錯誤的話,登錄到mysql之後,操作db會提示你與服務器連接丟失。

Eg.提示信息

  No connection. Trying to reconnect…

Connection id:    220

Current database: *** NONE ***

 

 ERROR 2013 (HY000): Lost connection to MySQL server during query

 

 

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裡不會有數據庫超級用戶的記錄,這也是為什麼我們不主張多個超級用戶,並且多人使用的原因

You May Also Like