#SQLの監査
RDBMSはたくさんの機密情報が保存されると思います。なので、誰が、いつ機密情報にアクセスしたのか監査証跡を残しておくことが一般的です。
特にAzure Synapse Analytics SQLプールはデータウェアハウスシステムのデータベースとして利用されることが多く、このデータウェアハウスシステムはいろいろなシステムからデータを連携されることが多いので、各システムの機密情報が長期間にわたって保存されることになり、ことさら、監査は重要な課題となることが多いです。
#Azure Synapse Analyticsでの監査設定
Azure Synapse Analytics SQLプールではSQLを監査する機能があります。
この機能を使う事で、データベースで発生したイベントは以下の何れかの保存先に監査ログとして格納されます。
・Azure ストレージアカウント
・Log Analyticsワークスペース
・Event Hubs
今回はAzure ストレージアカウントへの監査ログの出力を試してみました。
#監査設定例(Azure ストレージアカウント)
Azure Portalから対象のAzure Synapse Analyticsを選択し、メニューから__「監査」__を選択します。
 |
|---|
__「Azure SQL 監査を有効にする」__で監査設定を有効にします。
 |
|---|
監査ログの保存先で、AzureストレージかLog AnalyticsかEvent HUBを選択できるので、今回は__「ストレージ」__を選択し、監査ログの保存先として、Azureストレージアカウントを選択します。
 |
|---|
保存先のAzureストレージアカウントの情報を入力します。
 |
|---|
__「保存」__をして設定完了です。
 |
|---|
監査ログはこの画面の別タブからも確認可能です。__「監査ログの表示」__を押すと、データベース監査の中に監査ログが表示されるようになります。
 |
|---|
監査ログの詳細も以下のようにAzure Potalから確認できます。
 |
|---|
Azureストレージアカウント上の監査ログの保存先ですが、
指定したストレージアカウント内にsqldbauditlogsと言うコンテナが作成され、その配下に以下のような形で保存されます。
sqldbauditlogs/<サーバー名>/<データベース名>/SqlDbAuditing_Audit/<YYYY-MM-DD>/<ファイル名>.xel
#監査ログの保存期間
監査ログの保存期間はAzure Portalより、Azure Synapse Analyticsを選択し、メニューから__「監査」を選択、「詳細プロパティ」__で変更可能です。
 |
|---|
#監査ログの確認方法
Azure Portalからも確認可能ですが、以下の方法でも確認可能です。
##SQLで確認
sys.fn_get_audit_fileを利用することで確認する事が出来ます。
上記ステートメントの実行例は以下の通りです。
select * from sys.fn_get_audit_file('https://<ストレージアカウント名>.blob.core.windows.net/sqldbauditlogs/<サーバー名>/<データベース名>/SqlDbAuditing_Audit',default,default);
また上記は対象のデータベースの監査ログすべてを確認する時に使用するもので、日付のファルダや、ファイルパスまで指定すれば読み込むファイルを少なくできます。
※以下は特定のファイル名まで指定した例です。
select * from sys.fn_get_audit_file('https://<ストレージアカウント名>.blob.core.windows.net/sqldbauditlogs/<サーバー名>/<データベース名>/SqlDbAuditing_Audit/<YYYY-MM-DD>/<ファイル名>.xel',default,default);
出力結果
 |
|---|
##SSMSを使って確認
監査ログ（.xel）をSSMSへ取り込んで確認する事も可能です。
__「ファイル」⇒「開く」⇒「監査Fileの統合」__から取り込むことが可能です。
 |
|---|
__「追加」⇒「接続」__にてAzure ストレージへの接続情報を入力すればOKです。
 |
|---|
このように監査ログを確認する事が出来ます。
 |
|---|
#監査ログに分類や情報の種類の指定をする
監査ログは基本全てのアクションで取得されてしまいますので、何か起こった場合に、問題の監査ログを探しにくいです。また、監査レポートを作成する際なども重要なデータへのアクセスがあったのかなどの情報が監査ログ内にあった方がスムーズに作成できます。
Azure Synapse Analyticsでは、監査ログを分類し、ラベル付けを行う機能が実装されています。
##設定方法
Azure Portalより、Azure Synapse Analyticsを選択し、メニューから__「データの検出と分類」__を選択します。
 |
|---|
__「分類」を選択します。Synapse Analyticsから分類設定に対して推奨事項が見つかれば、ここに出力されます。この推奨事項は、「情報の種類」と言うものでカラム名などのパターンを入れておくことで、この推奨事項に表示されるようになります。（例えば、個人情報であるPhoneやNameと言ったような情報がカラム名に指定されていれば推奨事項として表示されます。「情報の種類」や「分類」についてはカスタマイズ可能ですが今回の説明では割愛しています。）
今回はこの推奨事項を使わず、個別に設定を行うので「分類の追加」__を選択します。
 |
|---|
dbo.CUSTOMERというテーブルのC_PHONE（電話番号）列を分類対象とします。情報の種類は「Contact Info」、機密レベルを「Confidential」に設定しました。
 |
|---|
__「保存」__を押せば、設定完了です。
 |
|---|
##監査ログにおける分類の確認
dbo.CUSTOMERテーブルのC_PHONE（電話番号）列に分類を設定したので、以下の２つのSQLを実行します。
１つはC_PHONE列を含んだSELECT文、もう１つはC_PHONE列を含まないSELECT文です。
--C_PHONE列を含んだselect
select C_CUSTKEY,C_NAME,C_PHONE from CUSTOMER where C_CUSTKEY=12944433;
--C_PHONE列を含まないselect
select C_CUSTKEY,C_NAME,C_ADDRESS from CUSTOMER where C_CUSTKEY=12944433;
Azure Potalから監査ログを確認します。C_PHONEを含んだSELECT文の監査ログには分類分けしたラベルがついている事が分かります。
 |
|---|
sys.fn_get_audit_fileステートメントで確認すると分類分けされたクエリにはdata_sensitivity_information列に情報が入ります。
 |
|---|