Yakstは、海外の役立つブログ記事などを人力で翻訳して公開するプロジェクトです。
4年以上前投稿 修正あり

MySQL Enterprise Auditの監査ログのデータをパースしてテーブルに挿入する(MySQL Server Blogより)

MySQLのEnterprise EditionのEnterprise Auditでは監査ログを出力する。本記事ではこれをパースしてデータベースに挿入するスクリプトを紹介する。これを利用すれば監査ログに対してSQLクエリで処理ができるようになり大きく利便性が向上する。

原文
MySQL Enterprise Audit : Parsing Audit Information From Log Files, Inserting Into MySQL Table | MySQL Server Blog (English)
翻訳依頼者
B5aa4f809000b9147289650532e83932
翻訳者
B5aa4f809000b9147289650532e83932 taka-h
原著者への翻訳報告
未報告


免責事項

この記事は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サーバ上で起こっているすべてのことを追跡し、情報が誤って使われることを防ぎ、検知するのに利用できる。 これはまたHIPAASarbanes-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.comAudit 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そしてVERSIONunsigned integerであると記載されていたが、varcharのままとした。


注意

スクリプトは、対象がMySQL 5.6.20またはそれより新しいバージョンで利用できる新しいフォーマットの監査ログファイルである必要がある。

1つのデータベースを作成し、そこには2つのテーブルを作った。1つはログファイルの情報を保存し、もう1つはどのファイルがすでにパースされMySQLに挿入されたかを追跡する履歴テーブルで、ログファイルのエントリ数と同数のエントリがある。CREATE DATABASECREATE 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をつかってくれてありがとう!

次の記事
MySQLのinformation_schemaの便利なクエリ(MySQL Step-by-Step Blogより)
前の記事
【挑戦者求む!】The MySQL 5.7のオプティマイザ チャレンジ

Feed small 記事フィード

新着記事Twitterアカウント

新着翻訳リクエストTwitterアカウント