![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
どのようなシステムにも完全が無いように、
Webサイトのセキュリティに気を付けてコーディングしたり、セキュリティ診断しながら運用しても、
どこかしら運用している中でセキュリティ対策漏れが出て来てしまう事が往々にしてあります。
また、それらのセキュリティ問題は、社内システムに関しても同様です。
個人情報を管理しているデータベースや企業秘密情報を管理するデータベースに関しては、
IPSやFW機能などのアプライアンスなどでカバーしてくれる製品もありますが、
非常に高価なものが多いです。本日、検証し共有させて頂くMySQL Enterprise Firewall機能に関しては、
White List方法（2015年4月現在）を用いたデータベース側で不正アクセスをブロックする
MySQLデータベースのセキュリティ対策追加モジュールです。
もし、ご興味を持たれたら、是非 Oracle Software Delivery Cloud から、
30日間無償で検証出来るトライアルを使って検証してみて頂ければと思います。
動作確認MySQL Version
admin@192.168.56.201 [(none)]> select @@version; +-------------------------------------------+ | @@version | +-------------------------------------------+ | 5.6.24-enterprise-commercial-advanced-log | +-------------------------------------------+ 1 row in set (0.00 sec)
インストールとPluginの状況確認
[admin@GA01 ~]$ mysql -u root -p mysql < /usr/local/mysql/share/linux_install_firewall.sql
Enter password:
[admin@GA01 ~]$ cat /usr/local/mysql/share/linux_install_firewall.sql
# Copyright (c) 2015 Oracle and/or its affiliates. All rights reserved.
# Install firewall tables
USE mysql;
CREATE TABLE IF NOT EXISTS mysql.firewall_whitelist( USERHOST VARCHAR(80) NOT NULL, RULE text Nne= MyISAM;
CREATE TABLE IF NOT EXISTS mysql.firewall_users( USERHOST VARCHAR(80) PRIMARY KEY, MODE ENUM ('ING', 'PROTECTING', 'RESET') DEFAULT 'OFF') engine= MyISAM;
INSTALL PLUGIN mysql_firewall SONAME 'firewall.so';
INSTALL PLUGIN mysql_firewall_whitelist SONAME 'firewall.so';
INSTALL PLUGIN mysql_firewall_users SONAME 'firewall.so';
CREATE FUNCTION set_firewall_mode RETURNS STRING SONAME 'firewall.so';
CREATE FUNCTION normalize_statement RETURNS STRING SONAME 'firewall.so';
CREATE AGGREGATE FUNCTION read_firewall_whitelist RETURNS STRING SONAME 'firewall.so';
CREATE AGGREGATE FUNCTION read_firewall_users RETURNS STRING SONAME 'firewall.so';
delimiter //
CREATE PROCEDURE sp_set_firewall_mode (IN arg_userhost VARCHAR(80), IN arg_mode varchar(12))
BEGIN
IF arg_mode = "RECORDING" THEN
SELECT read_firewall_whitelist(arg_userhost,FW.rule) FROM mysql.firewall_whitelist FW WHERE Fg_userhost;
END IF;
SELECT set_firewall_mode(arg_userhost, arg_mode);
if arg_mode = "RESET" THEN
SET arg_mode = "OFF";
END IF;
INSERT IGNORE INTO mysql.firewall_users VALUES (arg_userhost, arg_mode);
UPDATE mysql.firewall_users SET mode=arg_mode WHERE userhost = arg_userhost;
IF arg_mode = "PROTECTING" OR arg_mode = "OFF" THEN
DELETE FROM mysql.firewall_whitelist WHERE USERHOST = arg_userhost;
INSERT INTO mysql.firewall_whitelist SELECT USERHOST,RULE FROM INFORMATION_SCHEMA.mysql_firew WHERE USERHOST=arg_userhost;
END IF;
END //
delimiter ;
[admin@GA01 ~]$
root@GA01 [(none)]> select PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_LIBRARY,PLUGIN_LICENSE,LOAD_OPTION
-> from information_schema.plugins where PLUGIN_NAME like '%FIREWALL%';
+--------------------------+---------------+----------------+----------------+-------------+
| PLUGIN_NAME | PLUGIN_STATUS | PLUGIN_LIBRARY | PLUGIN_LICENSE | LOAD_OPTION |
+--------------------------+---------------+----------------+----------------+-------------+
| MYSQL_FIREWALL | ACTIVE | firewall.so | PROPRIETARY | ON |
| MYSQL_FIREWALL_WHITELIST | ACTIVE | firewall.so | PROPRIETARY | ON |
| MYSQL_FIREWALL_USERS | ACTIVE | firewall.so | PROPRIETARY | ON |
+--------------------------+---------------+----------------+----------------+-------------+
3 rows in set (0.00 sec)
root@GA01 [(none)]>
Install
Plugins
検証用ユーザー作成
root@GA01 [(none)]> GRANT ALL PRIVILEGES ON test.* TO fw_user@localhost IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.00 sec) root@GA01 [(none)]> root@GA01 [mysql]> select user,host,password from user; +---------+-----------+-------------------------------------------+ | user | host | password | +---------+-----------+-------------------------------------------+ | root | localhost | *B51ECFBE1191DDE4713F2B6F5A6CD5D0D0D5DC35 | | root | 127.0.0.1 | *B51ECFBE1191DDE4713F2B6F5A6CD5D0D0D5DC35 | | admin | % | *B51ECFBE1191DDE4713F2B6F5A6CD5D0D0D5DC35 | | fw_user | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | +---------+-----------+-------------------------------------------+ 4 rows in set (0.00 sec) root@GA01 [mysql]>
Firewall基本設定オプション
詳細： 5.14.4.2 MySQL Enterprise Firewall Procedures and Functions
自動学習機能を有効にして、ステートメントをWhite Listに記録します
--- First register this account with the Firewall.
--- You do this by calling the stored proceedure we created earlier:
root@GA01 [mysql]> CALL sp_set_firewall_mode('fw_user@localhost','RECORDING');
+-----------------------------------------------+
| read_firewall_whitelist(arg_userhost,FW.rule) |
+-----------------------------------------------+
| Imported users: 0 Imported rules: 0 |
+-----------------------------------------------+
1 row in set (0.01 sec)
+-------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode) |
+-------------------------------------------+
| OK |
+-------------------------------------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
root@GA01 [mysql]>
※ CALL sp_set_firewall_mode(‘ユーザー名’,’RECORDING’)は、運用開始後にWhite Listに値を追加する時にも使用します。
記録対象ユーザー(検証用ユーザー)でアクセスし、SQLステートメントを実際に登録してみます。
[admin@GA01 ~]$ mysql -u fw_user -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.24-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2015, 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.
fw_user@localhost [(none)]> use test
Database changed
fw_user@localhost [test]> CREATE TABLE FW_DEMO
-> (
-> ID INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(100)
-> ) Engine=InnoDB CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.00 sec)
fw_user@localhost [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| FW_DEMO |
+----------------+
1 row in set (0.00 sec)
fw_user@localhost [test]>
fw_user@localhost [test]> insert into FW_DEMO(title) values(concat('test firewall',@@version));
Query OK, 1 row affected (0.00 sec)
fw_user@localhost [test]> insert into FW_DEMO(title) values(concat('test firewall2',@@version));
Query OK, 1 row affected (0.00 sec)
fw_user@localhost [test]> insert into FW_DEMO(title) values(concat('test firewall3',@@version));
Query OK, 1 row affected (0.00 sec)
fw_user@localhost [test]> select * from FW_DEMO where id = 1;
+----+--------------------------------------------------------+
| ID | title |
+----+--------------------------------------------------------+
| 1 | test firewall5.6.24-enterprise-commercial-advanced-log |
+----+--------------------------------------------------------+
1 row in set (0.00 sec)
fw_user@localhost [test]> select * from FW_DEMO where id = 2;
+----+---------------------------------------------------------+
| ID | title |
+----+---------------------------------------------------------+
| 2 | test firewall25.6.24-enterprise-commercial-advanced-log |
+----+---------------------------------------------------------+
1 row in set (0.00 sec)
fw_user@localhost [test]>
学習が終了したので、White Listを有効にして、リストにあるステートメントのみを許可します。
root@GA01 [mysql]> CALL sp_set_firewall_mode('fw_user@localhost','PROTECTING');
+-------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode) |
+-------------------------------------------+
| OK |
+-------------------------------------------+
1 row in set (0.00 sec)
Query OK, 10 rows affected (0.00 sec)
root@GA01 [mysql]>
登録された、White Listの確認。
root@GA01 [mysql]> SELECT userhost, substr(rule,1,80) FROM mysql.firewall_whitelist WHERE userhost= 'fw_user@localhost'; +-------------------+----------------------------------------------------------------------------------+ | userhost | substr(rule,1,80) | +-------------------+----------------------------------------------------------------------------------+ | fw_user@localhost | SELECT SCHEMA ( ) | | fw_user@localhost | SHOW TABLES | | fw_user@localhost | INSERT INTO `FW_DEMO` ( `title` ) VALUES ( `concat` ( ? , @@version ) ) | | fw_user@localhost | DESC `FW_DEMO` | | fw_user@localhost | SELECT * FROM `FW_DEMO` WHERE `id` = ? | | fw_user@localhost | CREATE TABLE `FW_DEMO` ( `ID` INTEGER UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY K | | fw_user@localhost | SHOW SCHEMAS | | fw_user@localhost | SELECT SYSTEM_USER ( ) | | fw_user@localhost | SELECT @@version_comment LIMIT ? | | fw_user@localhost | SELECT * FROM SYSTEM_USER | +-------------------+----------------------------------------------------------------------------------+ 10 rows in set (0.00 sec) root@GA01 [mysql]> SELECT * FROM information_schema.mysql_firewall_users; +-------------------+------------+ | USERHOST | MODE | +-------------------+------------+ | fw_user@localhost | PROTECTING | +-------------------+------------+ 1 row in set (0.00 sec) root@GA01 [mysql]>
実際に対象ユーザー(fw_user@localhost)でアクセスしてみて、データベースが防御されているか確認。
fw_user@localhost [test]> select * from FW_DEMO where id = 1; +----+--------------------------------------------------------+ | ID | title | +----+--------------------------------------------------------+ | 1 | test firewall5.6.24-enterprise-commercial-advanced-log | +----+--------------------------------------------------------+ 1 row in set (0.00 sec) fw_user@localhost [test]> select * from FW_DEMO where id = 2; +----+---------------------------------------------------------+ | ID | title | +----+---------------------------------------------------------+ | 2 | test firewall25.6.24-enterprise-commercial-advanced-log | +----+---------------------------------------------------------+ 1 row in set (0.00 sec) fw_user@localhost [test]> select * from FW_DEMO where id = 3; +----+---------------------------------------------------------+ | ID | title | +----+---------------------------------------------------------+ | 3 | test firewall35.6.24-enterprise-commercial-advanced-log | +----+---------------------------------------------------------+ 1 row in set (0.00 sec) fw_user@localhost [test]> select * from FW_DEMO; ERROR 1045 (28000): Statement was blocked by Firewall fw_user@localhost [test]> root@GA01 [mysql]> SHOW STATUS LIKE 'Firewall%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Firewall_access_denied | 10 | | Firewall_access_granted | 6 | | Firewall_cached_entries | 10 | +-------------------------+-------+ 3 rows in set (0.00 sec) root@GA01 [mysql]>
※ 上記を確認頂くと、テーブル全体のデータを持っていこうとする処理はブロックされています。
SQLのエラーログにも記録されている事が確認出来ます。
[root@GA01 data]# tail -n 2 error.log 2015-04-13 22:40:21 3321 [Note] Plugin MYSQL_FIREWALL reported: 'ACCESS DENIED for fw_user@localhost. Reason: No match in whitelist. Statement: SELECT * FROM `FW_DEMO` ' 2015-04-13 22:41:25 3321 [Note] Plugin MYSQL_FIREWALL reported: 'ACCESS DENIED for fw_user@localhost. Reason: No match in whitelist. Statement: SELECT * FROM `FW_DEMO` ' [root@GA01 data]#
White Listの設定をOFFにしたい場合は、以下のコマンドでリセットする事が可能です。
root@GA01 [mysql]> CALL sp_set_firewall_mode('fw_user@localhost','RESET');
+-------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode) |
+-------------------------------------------+
| OK |
+-------------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
root@GA01 [mysql]> SELECT * FROM information_schema.mysql_firewall_users;
+-------------------+------+
| USERHOST | MODE |
+-------------------+------+
| fw_user@localhost | OFF |
+-------------------+------+
1 row in set (0.00 sec)
root@GA01 [mysql]>
リセット結果は、全部のデータをSELECTしてもブロックされて無い事で確認出来ます。
補足：
CALL sp_set_firewall_mode('fw_user@localhost','OFF');
でもWhite ListをOFFにしてSQLステートメントをブロックされないようにする事が可能です。
関連テーブルとProcedure
基本的には、以下のテーブルを利用してモジュールをコントロールしています。
今後、更にMySQL5.7との連携や機能拡張が行われて行く事になるかと思います。
root@GA01 [information_schema]> select TABLE_SCHEMA,TABLE_NAME from information_schema.tables
-> where TABLE_NAME like '%fire%';
+--------------------+--------------------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------------+--------------------------+
| information_schema | MYSQL_FIREWALL_WHITELIST |
| information_schema | MYSQL_FIREWALL_USERS |
| mysql | firewall_users |
| mysql | firewall_whitelist |
+--------------------+--------------------------+
4 rows in set (0.01 sec)
root@GA01 [information_schema]>
root@GA01 [mysql]> show create procedure sp_set_firewall_mode\G
*************************** 1. row ***************************
Procedure: sp_set_firewall_mode
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_set_firewall_mode`(IN arg_userhost VARCHAR(80), IN arg_mode varchar(12))
BEGIN
IF arg_mode = "RECORDING" THEN
SELECT read_firewall_whitelist(arg_userhost,FW.rule) FROM mysql.firewall_whitelist FW WHERE FW.userhost=arg_userhost;
END IF;
SELECT set_firewall_mode(arg_userhost, arg_mode);
if arg_mode = "RESET" THEN
SET arg_mode = "OFF";
END IF;
INSERT IGNORE INTO mysql.firewall_users VALUES (arg_userhost, arg_mode);
UPDATE mysql.firewall_users SET mode=arg_mode WHERE userhost = arg_userhost;
IF arg_mode = "PROTECTING" OR arg_mode = "OFF" THEN
DELETE FROM mysql.firewall_whitelist WHERE USERHOST = arg_userhost;
INSERT INTO mysql.firewall_whitelist SELECT USERHOST,RULE FROM INFORMATION_SCHEMA.mysql_firewall_whitelist WHERE USERHOST=arg_userhost;
END IF;
END
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
root@GA01 [mysql]>
Case Sensitve
大切なデータを守る為には、コーディングルールを決める必要がありますね。
fw_user@localhost [test]> select * from FW_DEMO where ID = 1; +----+--------------------------------------------------------+ | ID | title | +----+--------------------------------------------------------+ | 1 | test firewall5.6.24-enterprise-commercial-advanced-log | +----+--------------------------------------------------------+ 1 row in set (0.00 sec) fw_user@localhost [test]> select * from FW_DEMO where id = 1; ERROR 1045 (28000): Statement was blocked by Firewall fw_user@localhost [test]>
OverHead (抜粋)
Firewall takes those digests and compares them against an in-memory hash.
Matching a query against a whitelist of course adds a little extra processing
and our preliminary testing has shown under concurrent stress level loads
only a 2-3% performance impact added by running the firewall.
参照：
New MySQL Enterprise Firewall – Prevent SQL Injection Attacks
5.14.3 Using MySQL Enterprise Firewall
5.14.4.2 MySQL Enterprise Firewall Procedures and Functions
5.14.4.3 MySQL Enterprise Firewall System Variables