mysqlç审计åè½
mysqlæå¡å¨èªèº«æ²¡ææä¾å®¡è®¡åè½,ä½æ¯æ们å¯ä»¥ä½¿ç¨init-connect + binlogçæ¹æ³è¿è¡mysqlçæä½å®¡è®¡ãç±äºmysql binlogè®°å½äºææ对æ°æ®åºé¿çå®é
ä¿®æ¹çsqlè¯å¥ï¼åå
¶æ§è¡æ¶é´ï¼åconnection_idä½æ¯å´æ²¡æè®°å½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 select on accesslog.* to root;
å¦æåå¨å
·æto *.* æéçç¨æ·éè¦è¿è¡éå¶ã
è¿éè¿éè¦æ³¨æç¨æ·å¿
须对accesslog表å
·æinsertæé
grant select on accesslog.* to user@â%â;
1.3 设置init-connect
å¨[mysqld]ä¸æ·»å 以ä¸è®¾ç½®ï¼
init-connect=âinsertinto accesslog.accesslog(id, time, localname, matchname)
values(connection_id(),now(),user(),current_user());â
------注æuser()åcurrent_user()çåºå«
log-bin=xxx
è¿éå¿
é¡»å¼å¯binlog
1.4 éå¯æ°æ®åºçæ
shell> /etc/init.d/mysql restart
2. è®°å½è¿½è¸ª
2.1 thread_id确认
å¯ä»¥ç¨ä»¥ä¸è¯å¥å®ä½è¯å¥æ§è¡äºº
Tencent:~ # mysqlbinlog --start-datetime='2011-01-26 16:00:00'
--stop-datetime='2011-01-26 17:00:00' /var/lib/mysql/mysql-bin.000010
| grep -B 5 'wsj'
COMMIT/*!*/;
# at 767
#110126 16:16:43 server id 1 end_log_pos 872 Query thread_id=19 exec_time=0 error_code=0
use test/*!*/;
SET TIMESTAMP=1296029803/*!*/;
create table wsj(id int unsigned not null)
--
BEGIN
/*!*/;
# at 940
#110126 16:16:57 server id 1 end_log_pos 1033 Query thread_id=19 exec_time=0 error_code=0
SET TIMESTAMP=1296029817/*!*/;
insert into wsj(id) values (1)
--
BEGIN
/*!*/;
# at 1128
#110126 16:16:58 server id 1 end_log_pos 1221 Query thread_id=19 exec_time=0 error_code=0
SET TIMESTAMP=1296029818/*!*/;
insert into wsj(id) values (2)
2.2 ç¨æ·ç¡®è®¤
thread_id 确认以åï¼æ¾å°å
å¶å°±åªæ¯ä¸æ¡sqlè¯å¥çé®é¢äºã
mysql> select * from accesslog where id=19;
+----+---------------------+---------------------+-----------+
| id | time | localname | matchname |
+----+---------------------+---------------------+-----------+
| 19 | 2011-01-26 16:15:54 |
[email protected] | test@% |
+----+---------------------+---------------------+-----------+
1 row in set (0.00 sec)
3. Q
Qï¼ä½¿ç¨init-connectä¼å½±åæå¡å¨æ§è½åï¼
Aï¼ç论ä¸ï¼åªä¼å¨ç¨æ·æ¯æ¬¡è¿æ¥æ¶å¾æ°æ®åºéæå
¥ä¸æ¡è®°å½ï¼ä¸ä¼å¯¹æ°æ®åºäº§çå¾å¤§å½±åãé¤éè¿æ¥é¢çé常é«ï¼å½ç¶ï¼è¿ä¸ªæ¶åéè¦æ³¨æçå°±æ¯å¦ä½è¿è¡è¿æ¥å¤ç¨åæ§å¶ï¼èéæ¯ä¸æ¯è¦ç¨è¿ç§æ¹æ³çé®é¢äºï¼---å¦æéç¨é¿è¿æ¥å¹¶ä¸ç¼åçè¯,å¯ä»¥æé«æ§è½
Qï¼access-log表å¦ä½ç»´æ¤?
A: ç±äºæ¯ä¸ä¸ªlogç³»ç»ï¼æ¨è使ç¨archiveåå¨å¼æï¼æå©äºæ°æ®åå缩åæ¾ãå¦ææ°æ®åºè¿æ¥æ°éå¾å¤§çè¯ï¼å»ºè®®ä¸å®æ¶é´åä¸æ¬¡æ°æ®å¯¼åºï¼ç¶åæ¸
表ã
Qï¼è¡¨æå
¶ä»ç¨éä¹ï¼
Aï¼æï¼access-log表å½ç¶ä¸åªç¨äºå®¡è®¡ï¼å½ç¶ä¹å¯ä»¥ç¨äºå¯¹äºæ°æ®åºè¿æ¥çæ
åµè¿è¡æ°æ®åæï¼ä¾å¦æ¯æ¥è¿æ¥æ°åå¸å¾ççï¼åªææ³ä¸å°æ²¡æåä¸å°ã---å¯ä»¥ç¨æ¥æµè¯è¯»åå离,éªè¯è´è½½åè¡¡ç
Qï¼ä¼æéæ¼çè®°å½åï¼
Aï¼ä¼çï¼init-connect æ¯ä¸ä¼å¨superç¨æ·ç»å½æ¶æ§è¡çãæ以access-logéä¸ä¼ææ°æ®åºè¶
级ç¨æ·çè®°å½ï¼è¿ä¹æ¯ä¸ºä»ä¹æ们ä¸ä¸»å¼ å¤ä¸ªè¶
级ç¨æ·ï¼å¹¶ä¸å¤äººä½¿ç¨çåå ã--è¿ç§å®¡è®¡ä¸ä¼è®°å½rootçå
·æsuperæéçè´¦å·å¯¹æ°æ®åºç访é®