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

背景：

假設(shè)這么一個情況，你是某公司mysql-DBA，某日突然公司數(shù)據(jù)庫中的所有被人為刪了。

盡管有數(shù)據(jù)備份，但是因服務(wù)停止而造成的損失上千萬，現(xiàn)在公司需要查出那個做刪除操作的人。

但是擁有數(shù)據(jù)庫操作權(quán)限的人很多，如何排查，證據(jù)又在哪？

是不是覺得為力？

mysql本身并沒有操作審計的功能，那是不是意味著遇到這種情況只能自認(rèn)倒霉呢？

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

概述：

其實(shí)mysql本身已經(jīng)提供了詳細(xì)的sql執(zhí)行記錄–general log（詳見上篇blog） ，但是開啟它有以下幾個缺點(diǎn)

無論sql有無語法錯誤，只要執(zhí)行了就會記錄，導(dǎo)致記錄大量無用信息，后期的篩選有難度。

sql并發(fā)量很大時，log的記錄會對io造成一定的印象，是數(shù)據(jù)庫效率降低。

日志文件很容易快速膨脹，不妥善處理會對磁盤空間造成一定影響。

本文觀點(diǎn)：

使用init-connect + binlog的方法進(jìn)行mysql的操作審計。

由于mysql binlog記錄了所有對數(shù)據(jù)庫長生實(shí)際修改的sql語句，及其執(zhí)行時間，和connection_id但是卻沒有記錄connection_id對應(yīng)的詳細(xì)用戶信息。

因此本文將通過init-connect，在每次連接的初始化階段，記錄下這個連接的用戶，和connection_id信息。

在后期審計進(jìn)行行為追蹤時，根據(jù)binlog記錄的行為及對應(yīng)的connection-id 結(jié)合 之前連接日志記錄 進(jìn)行分析，得出最后的結(jié)論。

正文：

1. 設(shè)置init-connect

1.1 創(chuàng)建用于存放連接日志的數(shù)據(jù)庫和表

create database accesslog;

CREATE TABLE accesslog.accesslog (`id` int(11) primary key auto_increment, `time` timestamp, `localname` varchar(30), `matchname` varchar(30))

1.2 創(chuàng)建用戶權(quán)限

可用現(xiàn)成的root用戶用于信息的讀取

grant read on accesslog.* to root@localhost identified by ‘password’;

如果存在具有to *.* 權(quán)限的用戶需要進(jìn)行。

當(dāng)前登錄用戶需要對accesslog庫至少具有insert權(quán)限

1.3 設(shè)置init-connect

在[mysqld]下添加以下設(shè)置：

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

Eg.提示信息

No connection. Trying to reconnect...

Connection id: 220

Current database: *** NONE ***

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

log-bin--------如果原來的配置文件已經(jīng)啟用了日志，這里省略

1.4 重啟數(shù)據(jù)庫生效

shell> service mysqld restart

2. 記錄追蹤

2.1 thread_id確認(rèn)

假設(shè)想知道在2009年11月25日，上午9點(diǎn)多的時候，是誰吧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

會得到如下結(jié)果(可見thread_id為5)：

# at 300777

#091124 16::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 用戶確認(rèn)

thread_id 確認(rèn)以后，找到元兇就只是一條sql語句的問題了。

select * from accesslog.accesslog where conn_id=5 ;

就能發(fā)現(xiàn)是testuser2@localhost干的了。

+——+——————————-+——————————-+—————————–+

| id | time | localname | matchname |

+——+——————————-+——————————-+—————————–+

| 5 | 2009-11-25 10:57:39 | testuser2@localhost | testuser2@% |

+——+——————————-+——————————-+—————————–+

3. Q&A

Q：使用init-connect會影響服務(wù)器性能嗎？

A：理論上，只會在用戶每次連接時往數(shù)據(jù)庫里插入一條記錄，不會對數(shù)據(jù)庫產(chǎn)生很大影響。除非連接頻率非常高（當(dāng)然，這個時候需要注意的就是如何進(jìn)行連接復(fù)用和控制，而非是不是要用這種方法的問題了）

Q：access-log表如何維護(hù)?

A: 由于是一個log系統(tǒng)，推薦使用archive存儲引擎，有利于數(shù)據(jù)厄壓縮存放。如果數(shù)據(jù)庫連接數(shù)量很大的話，建議一定時間做一次數(shù)據(jù)導(dǎo)出，然后清表。

Q：表有其他用途么？

A：有！access-log表當(dāng)然不只用于審計，當(dāng)然也可以用于對于數(shù)據(jù)庫連接的情況進(jìn)行數(shù)據(jù)分析，例如每日連接數(shù)分布圖等等，只有想不到?jīng)]有做不到。

Q：會有遺漏的記錄嗎？

A：會的，init-connect 是不會在super用戶登錄時執(zhí)行的。所以access-log里不會有數(shù)據(jù)庫超級用戶的記錄，這也是為什么我們不主張多個超級用戶，并且多人使用的原因

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com