IPTables log analyzer
mysql のタイプが TYPE=MyISAM、InnoDBエラーとなる?
本サーバーのmysql は?
phpmyadmin - データベース - infomation_schema 「種別」で、MyISAM
- データの読み書きを行う基盤のことを「ストレージエンジン」と呼ぶ
- MySQL5.5以降でのデフォルトのストレージエンジンはInnoDB
- MyISAMは対象のテーブル自体をロックする(テーブルロック)
|
インストール
[root@falcon21 ~]# yum -y install mysql-devel
[root@falcon21 ~]# wget http://jaist.dl.sourceforge.net/sourceforge/iptablelog/iptablelog-v0.9.tar.bz2
[root@falcon21 ~]# tar jxvf iptablelog-v0.9.tar.bz2
[root@falcon21 ~]# mv iptablelog/ /var/www
[root@falcon21 ~]# rm -f iptablelog-v0.9.tar.bz2
データベース作成
[root@falcon21 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 67
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database iptablelog;
Query OK, 1 row affected (0.00 sec)
mysql> grant all on iptablelog.* to root@localhost identified by '4eszaq2';
Query OK, 0 rows affected (0.04 sec)
mysql> exit
Bye
-------------------
iptablelog データベースにIPTable log analyzerの用意しているSQLバッチファイルを流し込み
[root@falcon21 ~]# cat /var/www/iptablelog/conf/iptables.mysql | mysql -u root -p iptablelog
Enter password:
確認
[root@falcon21 ~]# mysql -u root -p iptablelog
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 391
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show tables;
+----------------------+
| Tables_in_iptablelog |
+----------------------+
| host_cache |
| ports |
| protos |
| ulog |
+----------------------+
4 rows in set (0.00 sec)
mysql>select * from ports;
|
|
| nimhub | 48002 | udp | Nimbus Hub | N |
| nimgtw | 48003 | udp | Nimbus Gateway | N |
| com-bardac-dw | 48556 | udp | com-bardac-dw | N |
| csnet-ns | 105 | tcp | Mailbox Name Nameserver | N |
+-----------------+-------+-------+------------------------------------------------------+--------+
7568 rows in set (0.03 sec)
mysql> exit
Bye
|
------------------
[root@falcon21 ~]# rpm -q php-mysql
php-mysql-5.3.3-49.el6.i686
ulogdをインストール
[root@falcon21 ~]# wget ftp://ftp.netfilter.org/pub/ulogd/ulogd-1.24.tar.bz2
[root@falcon21 ~]# tar jxvf ulogd-1.24.tar.bz2
[root@falcon21 ulogd-1.24]# vi configure
1750 # MYSQL_FUNCTION_TEST=`strings ${MYSQLLIBS}/libmysqlclient.so | grep mysql_real_escape_string`
1751 MYSQL_FUNCTION_TEST=`strings /usr/lib/mysql/libmysqlclient.so | grep mysql_real_escape_string`
1731 # MYSQLLIBS=`$d/mysql_config --libs`
1732 MYSQLLIBS=`$d/mysql_config --libs|sed 's/-rdynamic//g'`
[root@falcon21 ulogd-1.24]# ./configure --prefix=/usr --sysconfdir=/etc --libdir=/usr/local/lib --with-mysql \ && make && make install
[root@falcon21 ulogd-1.24]# cp ulogd.init /etc/rc.d/init.d/ulogd
[root@falcon21 ulogd-1.24]# cd
[root@falcon21 ~]# rm -rf ulogd-1.24
[root@falcon21 ~]# rm -f ulogd-1.24.tar.bz2
----------------
ulogd設定
[root@falcon21 ~]# vi /etc/ulogd.conf
40 plugin="/usr/local/lib/ulogd/ulogd_BASE.so"
41 plugin="/usr/local/lib/ulogd/ulogd_LOCAL.so"
43 # output plugins.
44 #plugin="/usr/local/lib/ulogd/ulogd_LOGEMU.so"
45 #plugin="/usr/local/lib/ulogd/ulogd_OPRINT.so"
46 plugin="/usr/local/lib/ulogd/ulogd_MYSQL.so"
47 #plugin="/usr/local/lib/ulogd/ulogd_PGSQL.so"
48 #plugin="/usr/local/lib/ulogd/ulogd_SQLITE3.so"
49 #plugin="/usr/local/lib/ulogd/ulogd_PCAP.so"
52 [LOGEMU]
53 file="/var/log/ulogd.log"
57 file="/var/log/ulogd.pktlog" // /var/log/ulogd.pktlog ファイル作成
[root@falcon21 ~]# touch /var/log/ulogd.pktlog
[root@falcon21 ~]# chmod 666 /var/log/ulogd.pktlog
59 [MYSQL]
60 table="ulog"
61 pass="4eszaq2"
62 user="root"
63 db="iptablelog"
64 host="localhost"
-----------
[root@falcon21 ~]# touch /var/log/ulogd.pktlog
[root@falcon21 ~]# chmod 666 /var/log/ulogd.pktlog
[root@falcon21 ~]# cp /var/www/iptablelog/reports/conf/config.php.default /var/www/iptablelog/reports/conf/config.php
[root@falcon21 ~]# vi /var/www/iptablelog/reports/conf/config.php
# user report
$reports_user_localdomain = "falcon21.space";
ulogd起動
[root@falcon21 ~]# /etc/rc.d/init.d/ulogd start
Starting ulogd: [ OK ]
[root@falcon21 ~]# chkconfig --add ulogd
---------------
iptablesを設定
[root@falcon21 ~]# iptables -A INPUT -m limit --limit 1/s -j LOG --log-prefix '[IPTABLES INPUT] : '
iptables: Memory allocation problem.
[root@falcon21 ~]# iptables -A INPUT -m limit --limit 1/s -j ULOG --ulog-nlgroup 1 --ulog-prefix 'INPUT'
iptables: Memory allocation problem.
[root@falcon21 ~]# iptables --list
拒否リストに入れている国が多い
受け入れる国が多い
[root@falcon21 ~]# vi iptables.sh
201 echo "-A INPUT -m limit --limit 1/s -j LOG --log-prefix '[IPTABLES INPUT] : '
202 echo "-A INPUT -m limit --limit 1/s -j ULOG --ulog-nlgroup 1 --ulog-prefix 'INPUT'
---------------------------
log analyzer設定
[root@falcon21 ~]# cp /var/www/iptablelog/conf/config.php.default /var/www/iptablelog/conf/config.php
[root@falcon21 ~]# vi /var/www/iptablelog/conf/config.php
33 # Password of the MySQL database
34 $db_password="4eszaq2";
35
36 # Name of the database
37 $db_name="iptablelog";
38
47 $file_base="/var/www/iptablelog"; # i.e. "/var/www/html/iptablelog"
[root@falcon21 ~]# cp /var/www/iptablelog/conf/iptables_resolve.default /etc/cron.hourly/iptables_resolve
[root@falcon21 ~]# vi /etc/cron.hourly/iptables_resolve
4 $iptablelog_path = "/var/www/iptablelog"; # Change this
7 db_connect("localhost","iptablelog","root", "4eszaq2"); # Change these db settings
-------------
[root@falcon21 ~]# vi /var/www/iptablelog/conf/iptables.mysql
115 #INSERT INTO protos VALUES (0,'HOPOPT','IPv6 Hop-by-Hop Option [RFC1883]','N');
156 #INSERT INTO protos VALUES (41,'IPv6','Ipv6 [Deering]','N');
158 #INSERT INTO protos VALUES (43,'IPv6-Route','Routing Header for IPv6 [Deering]','N');
159 #INSERT INTO protos VALUES (44,'IPv6-Frag','Fragment Header for IPv6 [Deering]','N');
173 #INSERT INTO protos VALUES (58,'IPv6-ICMP','ICMP for IPv6 [RFC1883]','N');
174 #INSERT INTO protos VALUES (59,'IPv6-NoNxt','No Next Header for IPv6 [RFC1883]','N');
175 #INSERT INTO protos VALUES (60,'IPv6-Opts','Destination Options for IPv6 [RFC1883]','N');
----------------
IPTABLESの起動スクリプトをLOGからULOGに変更
[root@falcon21 ~]# vi /var/www/iptablelog/reports/conf/config.php
<?php
/*
* Report specific configuration details go here
*
* format of variable names is $reports_[report name]_[variable name]
*/
# user report
$reports_user_localdomain = "falcon21.space";
#!/bin/bash
# SYN Cookiesを有効にする
# ※TCP SYN Flood攻撃対策
sysctl -w net.ipv4.tcp_syncookies=1 > /dev/null
sed -i '/net.ipv4.tcp_syncookies/d' /etc/sysctl.conf
# /etc/sysctl.confからnet.ipv4.tcp_syncookiesが含まれる行を削除する。
echo "net.ipv4.tcp_syncookies=1" >> /etc/sysctl.conf
# /etc/sysctl.confにnet.ipv4.tcp_syncookies=1を追加する。
# ブロードキャストアドレス宛pingには応答しない
# ※Smurf攻撃対策
# デフォルトでnet.ipv4.icmp_echo_ignore_broadcasts=1 のため不要
# ICMP Redirectパケットは拒否
sed -i '/net.ipv4.conf.*.accept_redirects/d' /etc/sysctl.conf
for dev in `ls /proc/sys/net/ipv4/conf/`
do
sysctl -w net.ipv4.conf.$dev.accept_redirects=0 > /dev/null
echo "net.ipv4.conf.$dev.accept_redirects=0" >> /etc/sysctl.conf
done
# Source Routedパケットは拒否
sed -i '/net.ipv4.conf.*.accept_source_route/d' /etc/sysctl.conf
for dev in `ls /proc/sys/net/ipv4/conf/`
do
sysctl -w net.ipv4.conf.$dev.accept_source_route=0 > /dev/null
echo "net.ipv4.conf.$dev.accept_source_route=0" >> /etc/sysctl.conf
done
# インタフェース名定義
LAN=eth0
# 内部ネットワーク
LOCALNET="192.168.0.0/255.255.255.0"
# フィルタリングルールの削除
iptables -F INPUT || echo "1"
iptables -F OUTPUT || echo "2"
iptables -F FORWARD || echo "3"
iptables -F || echo "4"
iptables -X || echo "5"
# カウンターのリセット
iptables -Z || echo "6"
# デフォルトルール(以降のルールにマッチしなかった場合に適用するルール)設定
iptables -P INPUT DROP || echo "7"
iptables -P OUTPUT ACCEPT || echo "8"
iptables -P FORWARD DROP || echo "9"
# 自ホストからのアクセスをすべて許可
iptables -A INPUT -i lo -j ACCEPT || echo "10"
# 内部からのアクセスをすべて許可
iptables -A INPUT -s $LOCALNET -j ACCEPT || echo "11"
# 内部から行ったアクセスに対する外部からの返答アクセスを許可
iptables -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT || echo "12"
# フラグメント化されたパケットはログを記録して破棄
# -fオプションを付けると断片化されたパケットのみに規則を適用
# デフォルトではlog-level=warnにて出力
# /var/log/messages,syslog,kern.logに出力されるとサイズが大きくなるため
# kern.debugにしてファイルを分ける。
#iptables -A INPUT -f -j LOG --log-prefix '[IPTABLES FRAGMENT] : ' --log-level=debug || echo "13"
iptables -A INPUT -f -j ULOG --ulog-nlgroup 1 --ulog-prefix "[IPTABLES FRAGMENT] : " || echo "13"
iptables -A INPUT -f -j DROP || echo "14"
# 外部とのNetBIOS関連のアクセスはログを記録せずに破棄
# ※不要ログ記録防止
iptables -A INPUT -s ! $LOCALNET -p tcp -m multiport --dports 135,137,138,139,445 -j DROP || echo "15"
iptables -A INPUT -s ! $LOCALNET -p udp -m multiport --dports 135,137,138,139,445 -j DROP || echo "16"
iptables -A OUTPUT -d ! $LOCALNET -p tcp -m multiport --sports 135,137,138,139,445 -j DROP || echo "17"
iptables -A OUTPUT -d ! $LOCALNET -p udp -m multiport --sports 135,137,138,139,445 -j DROP || echo "18"
# 1秒間に4回を超えるpingはログを記録して破棄
# ※Ping of Death攻撃対策
# iptableが効いているかを試験する場合はLOCALNETからのINBOUND通信を許可する前に本設定を入れる。
iptables -N LOG_PINGDEATH
iptables -A LOG_PINGDEATH -m limit --limit 1/s --limit-burst 4 -j ACCEPT || echo "19"
#iptables -A LOG_PINGDEATH -j LOG --log-prefix '[IPTABLES PINGDEATH] : ' --log-level=debug || echo "20"
iptables -A LOG_PINGDEATH -j ULOG --ulog-nlgroup 1 --ulog-prefix "[IPTABLES PINGDEATH] :" || echo "20"
iptables -A LOG_PINGDEATH -j DROP || echo "21"
iptables -A INPUT -p icmp --icmp-type echo-request -j LOG_PINGDEATH || echo "22"
iptables -A INPUT -p icmp --icmp-type echo-reply -j LOG_PINGDEATH || echo "22"
iptables -A INPUT -p icmp --icmp-type destination-unreachable -j LOG_PINGDEATH || echo "22"
iptables -A INPUT -p icmp --icmp-type time-exceeded -j LOG_PINGDEATH || echo "22"
# 全ホスト(ブロードキャストアドレス、マルチキャストアドレス)宛パケットはログを記録せずに破棄
# ※不要ログ記録防止
iptables -A INPUT -d 255.255.255.255 -j DROP || echo "23"
iptables -A INPUT -d 224.0.0.1 -j DROP || echo "24"
# 113番ポート(IDENT)へのアクセスには拒否応答
# ※メールサーバ等のレスポンス低下防止
iptables -A INPUT -p tcp --dport 113 -j REJECT --reject-with tcp-reset || echo "25"
# それ以外のIPアドレスからは基本許可
# ただし1分間に20パケット以上特定のIPアドレスから来た場合は移行5パケットしか受け付けない(4分間通信が無ければ20パケット受け付けられる閾値まで回復する)
iptables -A INPUT -p tcp --dport 53 -j ULOG --ulog-nlgroup 1 --ulog-prefix "[IPTABLES_TCP_DNS_ACCESS] : "
iptables -A INPUT -p tcp --dport 53 -m hashlimit --hashlimit-name t_dns --hashlimit 5/minute --hashlimit-burst 20 --hashlimit-mode srcip --hashlimit-htable-expire 120000 -j ACCEPT
#iptables -A INPUT -p tcp --dport 53 -j LOG --log-prefix '[IPTABLES_TCP_DNS_DROP] : ' --log-level=debug
iptables -A INPUT -p tcp --dport 53 -j ULOG --ulog-nlgroup 1 --ulog-prefix "[IPTABLES_TCP_DNS_DROP] : "
iptables -A INPUT -p tcp --dport 53 -j DROP
iptables -A INPUT -p udp --dport 53 -j ULOG --ulog-nlgroup 1 --ulog-prefix "[IPTABLES_UDP_DNS_ACCESS] : "
iptables -A INPUT -p udp --dport 53 -m hashlimit --hashlimit-name t_dns --hashlimit 5/minute --hashlimit-burst 20 --hashlimit-mode srcip --hashlimit-htable-expire 120000 -j ACCEPT
#iptables -A INPUT -p udp --dport 53 -j LOG --log-prefix '[IPTABLES_UDP_DNS_DROP] : ' --log-level=debug
iptables -A INPUT -p udp --dport 53 -j ULOG --ulog-nlgroup 1 --ulog-prefix "[IPTABLES_UDP_DNS_DROP] : "
iptables -A INPUT -p udp --dport 53 -j DROP
# 外部からのTCP25番ポート(SMTP)へのアクセスを許可
#iptables -A INPUT -p tcp --dport 25 -j LOG --log-prefix '[IPTABLES_MAIL_ACCEPT] : ' --log-level=debug
iptables -A INPUT -p tcp --dport 25 -j ULOG --ulog-nlgroup 1 --ulog-prefix "[IPTABLES_MAIL_ACCESS] : "
iptables -A INPUT -p tcp --dport 25 -m hashlimit --hashlimit-name t_mail --hashlimit 3/minute --hashlimit-burst 10 --hashlimit-mode srcip --hashlimit-htable-expire 120000 -j ACCEPT
iptables -A INPUT -p tcp --dport 25 -j ULOG --ulog-nlgroup 1 --ulog-prefix "[IPTABLES_MAIL_DROP] : "
iptables -A INPUT -p tcp --dport 25 -j DROP
# 外部からのTCP80番ポート(HTTP)へのアクセスを許可
iptables -A INPUT -p tcp --dport 80 -j ULOG --ulog-nlgroup 1 --ulog-prefix "[IPTABLES_HTTP_ACCEPT] : "
iptables -A INPUT -p tcp --dport 80 -j ACCEPT
# 拒否IPアドレスからのアクセスはログを記録せずに破棄
# ※拒否IPアドレスは//etc/network/if-pre-up.d/deny_ipに1行ごとに記述しておくこと
# (/etc/network/if-pre-up.d/deny_ipがなければなにもしない)
# -Iなので、INPUTチェインの一番上に挿入される
if [ -s /etc/network/if-pre-up.d/deny_ip ]; then
for ip in `cat /etc/network/if-pre-up.d/deny_ip`
do
iptables -I INPUT -s $ip -j DROP
done
fi
# 上記のルールにマッチしなかったアクセスはログを記録して破棄
#iptables -A INPUT -m limit --limit 1/s -j LOG --log-prefix '[IPTABLES INPUT] : ' --log-level=debug || echo "26"
iptables -A INPUT -m limit --limit 1/s -j ULOG --ulog-nlgroup 1 --ulog-prefix "[IPTABLES INPUT] : "
iptables -A INPUT -j DROP || echo "27"
#iptables -A FORWARD -m limit --limit 1/s -j LOG --log-prefix '[IPTABLES FORWARD] : ' --log-level=debug || echo "28"
iptables -A FORWARD -m limit --limit 1/s -j ULOG --ulog-nlgroup 1 --ulog-prefix "[IPTABLES FORWARD] : "
iptables -A FORWARD -j DROP || echo "29"
[root@falcon21 ~]# vi /etc/cron.hourly/iptables_resolve
#!/usr/bin/php -q
<?php
[root@falcon21 ~]# vi /etc/php.ini
229 short_open_tag = On
[root@falcon21 ~]# vi /var/www/iptablelog/reports/index.php
26 //require_once("conf/config.php");
53 pageFooter();
54 ?> //削除
----------------
Apache設定
[root@falcon21 ~]# vi /etc/httpd/conf.d/iptables.conf
Alias /iptablelog /var/www/iptablelog
<Location /iptablelog>
Order deny,allow
Deny from all
Allow from 127.0.0.1
Allow from 192.168.2.0/24 192.168.0.0/24
</Location>
[root@falcon21 ~]# /etc/rc.d/init.d/httpd reload
httpd を再読み込み中:
[root@falcon21 ~]# /etc/init.d/mysqld restart
mysqld を停止中: [ OK ]
mysqld を起動中: [ OK ]
http://falcon21.space/iptablelog/ にアクセス
最終的なiptablesコマンド結果
[root@falcon21 ~]# iptables -nL -v
Chain INPUT (policy ACCEPT 19534 packets, 2249K bytes)
pkts bytes target prot opt in out source destination
125 6943 monitorix_IN_8 tcp -- * * 0.0.0.0/0 0.0.0.0/0 tcp spts:1024:65535 dpt:143 ctstate NEW,RELATED,ESTABLISHED
343 24637 monitorix_IN_7 udp -- * * 0.0.0.0/0 0.0.0.0/0 udp spts:1024:65535 dpt:53 ctstate NEW,RELATED,ESTABLISHED
16 672 monitorix_IN_6 tcp -- * * 0.0.0.0/0 0.0.0.0/0 tcp spts:1024:65535 dpt:3306 ctstate NEW,RELATED,ESTABLISHED
0 0 monitorix_IN_5 tcp -- * * 0.0.0.0/0 0.0.0.0/0 tcp spts:1024:65535 dpt:139 ctstate NEW,RELATED,ESTABLISHED
840 46499 monitorix_IN_4 tcp -- * * 0.0.0.0/0 0.0.0.0/0 tcp spts:1024:65535 dpt:110 ctstate NEW,RELATED,ESTABLISHED
13658 1367K monitorix_IN_3 tcp -- * * 0.0.0.0/0 0.0.0.0/0 tcp spts:1024:65535 dpt:22 ctstate NEW,RELATED,ESTABLISHED
1013 126K monitorix_IN_2 tcp -- * * 0.0.0.0/0 0.0.0.0/0 tcp spts:1024:65535 dpt:80 ctstate NEW,RELATED,ESTABLISHED
7 320 monitorix_IN_1 tcp -- * * 0.0.0.0/0 0.0.0.0/0 tcp spts:1024:65535 dpt:21 ctstate NEW,RELATED,ESTABLISHED
28 1443 monitorix_IN_0 tcp -- * * 0.0.0.0/0 0.0.0.0/0 tcp spts:1024:65535 dpt:25 ctstate NEW,RELATED,ESTABLISHED
Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
pkts bytes target prot opt in out source destination
Chain OUTPUT (policy ACCEPT 19170 packets, 5511K bytes)
pkts bytes target prot opt in out source destination
137 21131 monitorix_IN_8 tcp -- * * 0.0.0.0/0 0.0.0.0/0 tcp spt:143 dpts:1024:65535 ctstate RELATED,ESTABLISHED
343 46757 monitorix_IN_7 udp -- * * 0.0.0.0/0 0.0.0.0/0 udp spt:53 dpts:1024:65535 ctstate RELATED,ESTABLISHED
13 710 monitorix_IN_6 tcp -- * * 0.0.0.0/0 0.0.0.0/0 tcp spt:3306 dpts:1024:65535 ctstate RELATED,ESTABLISHED
0 0 monitorix_IN_5 tcp -- * * 0.0.0.0/0 0.0.0.0/0 tcp spt:139 dpts:1024:65535 ctstate RELATED,ESTABLISHED
959 60045 monitorix_IN_4 tcp -- * * 0.0.0.0/0 0.0.0.0/0 tcp spt:110 dpts:1024:65535 ctstate RELATED,ESTABLISHED
13125 4162K monitorix_IN_3 tcp -- * * 0.0.0.0/0 0.0.0.0/0 tcp spt:22 dpts:1024:65535 ctstate RELATED,ESTABLISHED
1170 716K monitorix_IN_2 tcp -- * * 0.0.0.0/0 0.0.0.0/0 tcp spt:80 dpts:1024:65535 ctstate RELATED,ESTABLISHED
9 456 monitorix_IN_1 tcp -- * * 0.0.0.0/0 0.0.0.0/0 tcp spt:21 dpts:1024:65535 ctstate RELATED,ESTABLISHED
22 1527 monitorix_IN_0 tcp -- * * 0.0.0.0/0 0.0.0.0/0 tcp spt:25 dpts:1024:65535 ctstate RELATED,ESTABLISHED
Chain monitorix_IN_0 (2 references)
pkts bytes target prot opt in out source destination
Chain monitorix_IN_1 (2 references)
pkts bytes target prot opt in out source destination
Chain monitorix_IN_2 (2 references)
pkts bytes target prot opt in out source destination
Chain monitorix_IN_3 (2 references)
pkts bytes target prot opt in out source destination
Chain monitorix_IN_4 (2 references)
pkts bytes target prot opt in out source destination
Chain monitorix_IN_5 (2 references)
pkts bytes target prot opt in out source destination
Chain monitorix_IN_6 (2 references)
pkts bytes target prot opt in out source destination
Chain monitorix_IN_7 (2 references)
pkts bytes target prot opt in out source destination
Chain monitorix_IN_8 (2 references)
pkts bytes target prot opt in out source destination
|