Amazon Redshift Spectrum を使用して監査ログを分析するにはどうすればよいですか?

簡単な説明

Redshift Spectrum の使用を開始する前に、次のタスクを完了してください。

1.    監査ログを有効にします。

注意: 監査ログが Amazon Simple Storage Service (Amazon S3) バケットに表示されるまでに時間がかかる場合があります。

2.    AWS Identity and Access Management (IAM) ロールを作成します。

3.    IAM ロールを Amazon Redshift クラスターに関連付けます。

Redshift Spectrum で監査ログをクエリするには、外部テーブルを作成し、(ファイルで使用される) 共通のフォルダをポイントするように設定します。次に、非表示の $path 列と regex 関数を使用して、分析用の行を生成するビューを作成します。

解決方法

Redshift Spectrum で監査ログをクエリするには、次の手順を実行します。

1.    外部スキーマを作成します。

create external schema s_audit_logs 
from data catalog 
database 'audit_logs' 
iam_role 'arn:aws:iam::your_account_number:role/role_name' create external database if not exists

実際のアカウント番号と一致するように、your_account_number を置き換えます。role_name には、Amazon Redshift クラスターにアタッチされている IAM ロールを指定します。

2.    外部テーブルを作成します。

create external table s_audit_logs.user_activity_log(
    logrecord varchar(max)
)
 ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY '|' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://bucket_name/logs/AWSLogs/your_account_id/redshift/region'

この例では、ユーザーアクティビティログテーブルを作成します。bucket_name、your_account_id、および region を実際のバケット名、アカウント ID、リージョンと一致するように置き換えます。

3.    接続ログテーブルを作成します。

CREATE EXTERNAL TABLE s_audit_logs.connections_log(
  event varchar(60),  recordtime varchar(60), 
  remotehost varchar(60),  remoteport varchar(60), 
  pid int,  dbname varchar(60), 
  username varchar(60),  authmethod varchar(60), 
  duration int,  sslversion varchar(60), 
  sslcipher varchar(150),  mtu int, 
  sslcompression varchar(70),  sslexpansion varchar(70), 
  iamauthguid varchar(50),  application_name varchar(300))
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY '|' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://bucket_name/logs/AWSLogs/your_account_id/redshift/region';

bucket_name、your_account_id、および region をバケット名、アカウント ID、リージョンと一致するように置き換えます。

4.    監査ログを表示するローカルスキーマを作成します。

create schema audit_logs_views

5.    外部テーブルにアクセスするには、(WITH NO SCHEMA BINDING オプションを使用して) データベース内にビューを作成します。

CREATE VIEW audit_logs_views.v_connections_log AS
select *
FROM s_audit_logs.connections_log
WHERE "$path" like '%connectionlog%'
with no schema binding;

返されるファイルは、非表示の $path 列によって connectionlog のエントリと一致するように制限されています。

次の例では、v_connections_log について返されるファイルを制限するために、非表示の $path 列と regex 関数が使用されています。

CREATE or REPLACE VIEW audit_logs_views.v_useractivitylog AS
SELECT    logrecord,
          substring(logrecord,2,24) as recordtime,
          replace(regexp_substr(logrecord,'db=[^" "]*'),'db=','') as db,
          replace(regexp_substr(logrecord,'user=[^" "]*'),'user=','') AS user,
          replace(regexp_substr(logrecord, 'pid=[^" "]*'),'pid=','') AS pid,
          replace(regexp_substr(logrecord, 'userid=[^" "]*'),'userid=','') AS userid,
          replace(regexp_substr(logrecord, 'xid=[^" "]*'),'xid=','') AS xid,
          replace(regexp_substr(logrecord, '][^*]*'),']','') AS query
   FROM s_audit_logs.user_activity_log
   WHERE "$path" like '%useractivitylog%'
   with no schema binding;

返されるファイルは、useractivitylog のエントリと一致します。

注意: ユーザーアクティビティログの複数行のクエリには制限があります。したがって、列のログレコードを直接クエリすることがベストプラクティスです。

---

関連情報

Amazon Redshift Spectrumによるセキュリティとコンプライアンスのためのデータベース監査ログの分析

STL_CONNECTION_LOG