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 insert on accesslog.* to  testuser@。。。。。。                               ------其他使用者需要對改庫擁有insert 的許可權，不然init初始化時插不進去

1.3 設定init-connect

在[mysqld]下新增以下設定：

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

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

方法二：可透過mysql使用mariadb審計外掛（蠻好的方法）