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"}}}} |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
【監査ログ】
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>
これまでより、断然監査がし易くなっているので是非検証してみて下さい。
トライアル(試用版)ダウンロード： https://www-jp.mysql.com/trials/
Enterprise Monitorも合わせて検証するとAudit Logの状況がリアルタイムで可視化出来ます。（閾値を超えたら、メールかSNMPで管理者に連絡）
参考)
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/
![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
