免責事項
この記事はTony Darnell氏によるMySQL Server Blogの投稿「MySQL Enterprise Audit : Parsing Audit Information From Log Files, Inserting Into MySQL Table」(2015/7/8)をユーザが翻訳したものであり、Oracle公式の文書ではありません。
MySQL Enterprise Auditプラグインは、MySQL Enterprise Edition(有償ライセンス)の一部分である。基本的には、Enterprise AuditはMySQLサーバ上で起こっているすべてのことを追跡し、情報が誤って使われることを防ぎ、検知するのに利用できる。 これはまたHIPAA、Sarbanes-OxlayそしてPCI-DSSを含む有名なコンプライス基準を満たすことができる。
MySQL Enterprise Auditでは、公開されているMySQL Audit APIを利用しており、標準で利用できる特定MySQLサーバ上でのポリシーベースのモニタリングおよび、接続や実行されたクエリの記録を実現している。Oracleの監査仕様を満たす為に、MySQL Enterprise Auditは特有の使いやすいアプリケションの監査およびコンプライアンスのソリューションを提供し、これは内外両方の規約で管理される。
インストールされた場合は、監査プラグインはMySQLサーバがサーバの活動に関する監査レコードを含むログファイルを生成する機能を有効化する。 ログにはいつクライアントが接続し切断したか、接続中に何をしたのか、どのデータベースおよびテーブルにアクセスしたかなどが記録される。
(訳注: 原文はマニュアルの英語の引用だが翻訳した文章を記載した。マニュアル原文は、下記アドレスを参照のこと。)
(from https://dev.mysql.com/doc/refman/5.6/en/audit-log-plugin.html)
MySQL Enterprise Auditを有効化すれば、ログファイルがMySQLのデータディレクトリに生成される。 MySQL Workbench(Enterprise Edition)、mysqlauditgrep、またはOracle Audit Vaultの様なツールを使うと、ログのデータをインポートし、内容を確認したり、検索したり、レポートを作成したりできる。
監査のデータがテーブルに格納できるかどうか知りたがっているある顧客と話をする機会があった。
現在(MySQL 5.6.25)では、監査の情報は監査ログファイルにXML形式で保存される。
そこで私はPerlのスクリプトを即興で作成し、XMLのログファイルをパースしてMySQLデータベースに情報を挿入するようにすることを決めた。
my.cnfまたはmy.iniの設定ファイルにaudit_log_rotate_on_size変数を監査ログファイルのサイズとして指定する必要があり、またこのサイズはデータベースの利用状況やスクリプトがログファイルをどの程度うまくパースできるかを元に調整する必要がある。ログファイルが巨大な場合は、Perlのスクリプトが処理中に問題を起こす可能性があり、この場合ログファイルのサイズを減らしスクリプトをもっと高頻度に動作させたくなるだろう。
警告
Enterprise Auditを利用するにはMySQLのライセンスが必要となる。もしEnterpriseのサブスクリプションに興味を持たれたら下のコメント欄から私に連絡して欲しい。(訳注: 元記事のコメント欄に記載のこと)。Enterpriseのライセンスをお持ちの場合は、Enterprise Auditの設定を最初に行う必要がある。詳細については、Enterprise Auditのオンラインマニュアルを見るか、MySQL Supportに連絡して欲しい。
データフィールドについては、MySQL.comのAudit Log Fileのページの監査ログファイルフォーマットの情報を利用した。
私のMySQLサーバーはそんなに高負荷ではないので、データフィールドのサイズを各フィールドのサイズを各フィールドがとりうるサイズが確保されるよう設定してみた。
これらのフィールドを増やしたりデータ型を変えなければいけないこともありうるだろう。最大のフィールドはSQL_TEXTフィールドでSQL文を格納するものだ。
各テーブルは65,535バイトの最大列サイズを有している。従って、この例に対するSQL_TEXTフィールドのとりうる最大の値は、約63,200バイト(65,535バイト引く他のフィールドの合計サイズ、引く各varchar列に対する1バイトまたは2バイト長のプレフィックスサイズ)である。
この例ではSQL_TEXTフィールドは8,096バイトに設定されており、ご利用になる際はこの値を増やすか減らすかする必要があるだろう。
私はIDという名称の主キーフィールドを除いて、各フィールドにvarcharのデータ型を利用した。
データベースのスキーマにたくさん時間を使わなかったので、少し修正したいと思うかもしれない。
いくつかのフィールドはintegerであることは知っているが、各フィールドに対してとりうる値を確実に決めるだけの十分なデータがログファイルになかった。オンラインマニュアルを読み、CONNECTION_ID,SERVER_ID,STATUS,STATUS_CODEそしてVERSIONがunsigned integerであると記載されていたが、varcharのままとした。
注意
スクリプトは、対象がMySQL 5.6.20またはそれより新しいバージョンで利用できる新しいフォーマットの監査ログファイルである必要がある。
1つのデータベースを作成し、そこには2つのテーブルを作った。1つはログファイルの情報を保存し、もう1つはどのファイルがすでにパースされMySQLに挿入されたかを追跡する履歴テーブルで、ログファイルのエントリ数と同数のエントリがある。CREATE DATABASEとCREATE TABLEは次の通りである。
CREATE DATABASE `audit_information` /*!40100 DEFAULT CHARACTER SET latin1 */
CREATE TABLE `audit_parsed` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`COMMAND_CLASS` varchar(64) DEFAULT NULL,
`CONNECTIONID` varchar(32) DEFAULT NULL,
`DB_NAME` varchar(64) DEFAULT NULL,
`HOST_NAME` varchar(256) DEFAULT NULL,
`IP_ADDRESS` varchar(16) DEFAULT NULL,
`MYSQL_VERSION` varchar(64) DEFAULT NULL,
`COMMAND_NAME` varchar(64) DEFAULT NULL,
`OS_LOGIN` varchar(64) DEFAULT NULL,
`OS_VERSION` varchar(256) DEFAULT NULL,
`PRIV_USER` varchar(16) DEFAULT NULL,
`PROXY_USER` varchar(16) DEFAULT NULL,
`RECORD_ID` varchar(64) DEFAULT NULL,
`SERVER_ID` varchar(32) DEFAULT NULL,
`SQL_TEXT` varchar(8096) DEFAULT NULL,
`STARTUP_OPTIONS` varchar(1024) DEFAULT NULL,
`COMMAND_STATUS` varchar(64) DEFAULT NULL,
`STATUS_CODE` varchar(11) DEFAULT NULL,
`DATE_TIMESTAMP` varchar(24) DEFAULT NULL,
`USER_NAME` varchar(128) DEFAULT NULL,
`LOG_VERSION` varchar(11) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
CREATE TABLE `audit_history` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`AUDIT_LOG_NAME` varchar(64) DEFAULT NULL,
`PARSED_DATE_TIME` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`LOG_ENTRIES` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
Perlのスクリプトは非活性のログファイルを見つけ(.xmlで終わり、例えば audit.log.14357895017796690.xml といったもの)、データをパースし、INSERT文を書いたSQLファイルを生成し、MySQLのcli経由でデータをインポートし、それからログファイルとSQLファイルをあるディレクトリに移動する。履歴テーブルは、どのファイルを処理したかを記録するため、同じファイルを誤って2度処理してしまうことは発生しない。
Perlスクリプトの最初の部分は、システムに応じて変更すべき値がいくつかある。その値は"values needed"の段落にある。Perlスクリプト(audit.pl)の例をここに示す。
#!/usr/bin/perl
# audit.pl
use DBI;
use CGI;
use XML::Simple;
#----------------------------------------------------------
# values needed
$Database = "audit_information";
$MYSQL_DATA_DIR = "/usr/local/mysql/data";
$MySQL_Host_IP_Name = "192.168.1.2";
$mysql_user = "root";
$mysql_password = "password_needed";
# directory to store old audit files after parsing
$audit_directory = "$MYSQL_DATA_DIR/audit_files";
# make an audit_files directory if one does not exist
mkdir($audit_directory) unless(-d $audit_directory);
#----------------------------------------------------------
#----------------------------------------------------------
#for each file do this
@files = @files = ;;
foreach $file_name_to_parse (@files) {
#----------------------------------------------------------
# check to see if file has already been parsed
$dbh1 = ConnectToMySql($Database);
$query1 = "select AUDIT_LOG_NAME from audit_history where AUDIT_LOG_NAME = '$file_name_to_parse'";
$sth1 = $dbh1->prepare($query1);
$sth1->execute();
while (@data = $sth1->fetchrow_array()) {
$audit_log_name = $data[0];
}
# if length of audit_log_name is less than 1, process file
if (length($audit_log_name) $PARSED_FILE") or die print "Couldn't open log_file: $!";
$count = 0;
# XML::Simple variable - SuppressEmpty => 1 ignore empty values
$xml = XML::Simple->new(SuppressEmpty => 1);
$data = $xml->XMLin("$file_name_to_parse");
foreach $info (@{$data->{AUDIT_RECORD}})
{
# replace tick marks ' with \' in the SQL TEXT
$info->{"SQLTEXT"} =~ s/'/\\'/g;
print LOGOUT "INSERT INTO audit_information.AUDIT_PARSED (COMMAND_CLASS, CONNECTIONID, DB_NAME, HOST_NAME, IP_ADDRESS, MYSQL_VERSION, COMMAND_NAME, OS_LOGIN, OS_VERSION, PRIV_USER, PROXY_USER, RECORD_ID, SERVER_ID, SQL_TEXT, STARTUP_OPTIONS, COMMAND_STATUS, STATUS_CODE, DATE_TIMESTAMP, USER_NAME, LOG_VERSION) values ('" . $info->{"COMMAND_CLASS"} . "', '" . $info->{"CONNECTION_ID"} . "', '" . $info->{"DB"} . "', '" . $info->{"HOST"} . "', '" . $info->{"IP"} . "', '" . $info->{"MYSQL_VERSION"} . "', '" . $info->{"NAME"} . "', '" . $info->{"OS_LOGIN"} . "', '" . $info->{"OS_VERSION"} . "', '" . $info->{"PRIV_USER"} . "', '" . $info->{"PROXY_USER"} . "', '" . $info->{"RECORD_ID"} . "', '" . $info->{"SERVER_ID"} . "', '" . $info->{"SQLTEXT"} . "', '" . $info->{"STARTUP_OPTIONS"} . "', '" . $info->{"STATUS"} . "', '" . $info->{"STATUS_CODE"} . "', '" . $info->{"TIMESTAMP"} . "', '" . $info->{"USER"} . "', '" . $info->{"VERSION"} . "');\n";
$count++;
# end foreach $info (@{$data->{AUDIT_RECORD}})
}
# load parsed file into MySQL - hide warnings
system("mysql -u$mysql_user -p$mysql_password /dev/null 2>&1");
$dbh2 = ConnectToMySql($Database);
$query2 = "insert into audit_information.audit_history (AUDIT_LOG_NAME, LOG_ENTRIES) values ('$file_name_to_parse', '$count')";
# optional print output - uncomment if desired
# print "$query2\n";
$sth2 = $dbh2->prepare($query2);
$sth2->execute();
# close audit log file
close(INFILE);
# optional print output - uncomment if desired
# print "Moving audit log ($file_name_to_parse) and log file ($PARSED_FILE) to $audit_directory.\n";
# strip directories off $file_name_to_parse
@file_name_to_move_array = split("\/",$file_name_to_parse);
$directory_count = $#file_name_to_move_array;
$file_name_to_move = $file_name_to_move_array[$directory_count];
# optional print output - uncomment if desired
# print "mv $file_name_to_move $file_name_to_parse\n";
# print "mv $PARSED_FILE $audit_directory\n";
# move audit log files and parsed log files to $audit_directory
system("mv $file_name_to_parse $audit_directory");
system("mv $PARSED_FILE $audit_directory");
# end - if (length($audit_log_name) connect($connectionInfo,$mysql_user,$mysql_password);
return $l_dbh;
}
必要な値をスクリプトに正しく設定してさえいれば、audit.plはどこで実行しても良い。
このスクリプトを、MySQLのデータディレクトリにあり現在のログであるため、まだローテートされていないログに対して実行しようとした場合は、エラーとなる。
現在のログファイルはaudit.logという名前である。
# pwd
/usr/local/mysql/data
# ls -l audit.log
-rw-rw---- 1 mysql _mysql 9955118 Jul 2 15:25 audit.log
It should not matter where you execute audit.pl, as long as you have correctly entered the required values in the script. You might get errors if you try to run this script on a log file that has not been rotated, which is the current log file in your MySQL data directory. The current log file is named audit.log.
スクリプトは.xmlで終わるファイルにのみ使える。テストには小さく(そして同一の)監査ログファイルを使った。
# pwd
/usr/local/mysql/data
# ls -l *xml
-rw-rw---- 1 mysql wheel 15508 Jul 2 12:20 audit.log.14357895017796690.xml
-rw-r----- 1 mysql _mysql 15508 Jul 2 13:46 audit.log.14357895017796691.xml
-rw-r----- 1 mysql _mysql 15508 Jul 2 13:46 audit.log.14357895017796692.xml
-rw-r----- 1 mysql _mysql 15508 Jul 2 13:46 audit.log.14357895017796693.xml
Perlスクリプトのプリント文をコメントアウトしたが、コメントアウトを外せば、それぞれのログファイルに対してこの出力が得られる。
# perl audit.pl
Parsing - /usr/local/mysql/data/audit.log.14357895017796690.xml
insert into audit_information.audit_history (AUDIT_LOG_NAME, LOG_ENTRIES) values ('/usr/local/mysql/data/audit.log.14357895017796690.xml', '34')
Moving audit log (/usr/local/mysql/data/audit.log.14357895017796690.xml) and log file (/usr/local/mysql/data/audit.log.14357895017796690_parsed.sql) to /usr/local/mysql/data/audit_files.
mv audit.log.14357895017796690.xml /usr/local/mysql/data/audit.log.14357895017796690.xml
mv /usr/local/mysql/data/audit.log.14357895017796690_parsed.sql /usr/local/mysql/data/audit_files
....
テストスクリプトの実行後には、次のデータがaudit_historyテーブルに格納される。
mysql> use audit_information
Database changed
mysql> select * from audit_history;
+----+-------------------------------------------------------+---------------------+-------------+
| ID | AUDIT_LOG_NAME | PARSED_DATE_TIME | LOG_ENTRIES |
+----+-------------------------------------------------------+---------------------+-------------+
| 1 | /usr/local/mysql/data/audit.log.14357895017796690.xml | 2015-07-02 15:25:07 | 34 |
| 2 | /usr/local/mysql/data/audit.log.14357895017796691.xml | 2015-07-02 15:25:08 | 34 |
| 3 | /usr/local/mysql/data/audit.log.14357895017796692.xml | 2015-07-02 15:25:08 | 34 |
| 4 | /usr/local/mysql/data/audit.log.14357895017796693.xml | 2015-07-02 15:25:09 | 34 |
+----+-------------------------------------------------------+---------------------+-------------+
4 rows in set (0.00 sec)
audit_parsedテーブルの例(1行)は次のとおりだ。
mysql> select * from audit_parsed limit 1 \G
*************************** 1. row ***************************
ID: 1
COMMAND_CLASS: select
CONNECTIONID: 10093
DB_NAME:
HOST_NAME: localhost
IP_ADDRESS: 127.0.0.1
MYSQL_VERSION:
COMMAND_NAME: Query
OS_LOGIN:
OS_VERSION:
PRIV_USER:
PROXY_USER:
RECORD_ID: 1614933_2015-07-01T22:08:58
SERVER_ID:
SQL_TEXT: SELECT (UNIX_TIMESTAMP(now()) - CAST(variable_value AS SIGNED))*1000 as serverStartMillis
FROM information_schema.global_status
WHERE variable_name='uptime'
STARTUP_OPTIONS:
COMMAND_STATUS: 0
STATUS_CODE: 0
DATE_TIMESTAMP: 2015-07-01T22:08:58 UTC
USER_NAME: root[root] @ localhost [127.0.0.1]
LOG_VERSION:
1 row in set (0.00 sec)
ログファイルをパースしたら、監査データに対して自身で検索クエリを作成できる。 自動的にスクリプトが実行されファイルをパースするように、cronにこのスクリプトを入れても良いだろう。
しかし通常通り、プロダクション環境に適用する前にこのスクリプトをテストし気をつけて使って欲しい。保存する必要がないデータがフィルタリングされるようにPerlスクリプトを修正してもよいだろう。
このスクリプトを利用し、意見や、疑問がわいたらどのようなものでも下記のコメント欄にコメントを残していただきたい。(※訳注:元記事はこちら)
これが興味を持っていただき、MySQL Enterprise Auditのログを処理すると何ができるかという例として役立ってくれることを願う。 以上である、いつもの通り、MySQLをつかってくれてありがとう!
