S3 access auditing with CloudTrail and Athena isn’t about logging what happened, but about reconstructing who did what to which object, and why it was permissible.
Let’s see it in action. Imagine a bucket my-secure-data-bucket and we want to know who’s been reading sensitive files.
First, ensure CloudTrail is logging S3 data events. This is the crucial first step. In the AWS console, navigate to CloudTrail, create a trail (or edit an existing one). Under "Data events," select "S3" and then choose "Read" and "Write" for the relevant buckets. If you want to audit all S3 buckets, select "All current and future S3 buckets."
CloudTrail will then start sending events to an S3 bucket you designate. Let’s say this bucket is my-cloudtrail-logs-bucket. These events are JSON files, often partitioned by date.
Now, for Athena. Athena is a serverless query service that lets you query data directly in S3 using SQL. To use it with CloudTrail logs, you need to define a table schema that matches the CloudTrail event structure.
Here’s a typical Athena CREATE EXTERNAL TABLE statement for S3 data events:
CREATE EXTERNAL TABLE `cloudtrail_s3_data_events`(
`eventversion` string,
`useridentity` struct<type:string, principalid:string, arn:string, accountid:string,
invokedby:string, identityprovider:string, accesskeyid:string,
sessioncontext:struct<attributes:struct<mfaauthenticated:string, creationdate:string>>>,
`eventtime` timestamp,
`eventsource` string,
`eventname` string,
`awsregion` string,
`sourceipaddress` string,
`useragent` string,
`errorcode` string,
`errormessage` string,
`requestparameters` map<string,string>,
`responseelements` map<string,string>,
`escalate_event` boolean,
`share_event` boolean,
`access_denied_reason` string,
`object_key` string,
`bucket_name` string,
`bucket_owner_identity` string,
`sequencenumber` bigint,
`tags` array<struct<key:string, value:string>>,
`x_amz_request_id` string,
`x_amz_id_2` string,
`versionids` string,
`storageclass` string,
`requester_id` string,
`request_tier` string,
`delete_marker` boolean,
`intelligent_tiering_access_tier` string,
`intelligent_tiering_is_truncated` boolean,
`intelligent_tiering_archive_access_tier` string,
`intelligent_tiering_archive_is_truncated` boolean,
`restore_request_status` string,
`multipartupload` map<string,string>,
`abort_incomplete_multipart_upload` map<string,string>,
`object_lock_mode` string,
`object_lock_retain_until_date` timestamp,
`object_lock_legal_hold` string,
`object_lock_governance_mode` string,
`object_lock_governance_retention_days` int,
`object_lock_governance_retention_mode` string,
`object_lock_governance_retention_until_date` timestamp,
`s3_bucket_version_id` string,
`s3_object_version_id` string,
`s3_request_charged` string,
`s3_request_id` string,
`s3_version_id` string,
`s3_x_amz_id_2` string,
`s3_x_amz_server_side_encryption` string,
`s3_x_amz_server_side_encryption_aws_kms_key_id` string,
`s3_x_amz_server_side_encryption_bucket_key_enabled` string,
`s3_x_amz_storage_class` string,
`s3_x_amz_website_redirect_location` string,
`s3_x_amz_version_id` string,
`s3_x_amz_acl` string,
`s3_x_amz_copy_source_version_id` string,
`s3_x_amz_copy_source_sse_kms_key_id` string,
`s3_x_amz_copy_source_sse_kms_key_id_alias` string,
`s3_x_amz_copy_source_sse_kms_key_id_arn` string,
`s3_x_amz_copy_source_sse_kms_key_id_type` string,
`s3_x_amz_copy_source_sse_kms_key_id_region` string,
`s3_x_amz_metadata_directive` string,
`s3_x_amz_copy_source_storage_class` string,
`s3_x_amz_restore` string,
`s3_x_amz_tagging` string,
`s3_x_amz_tagging_directive` string,
`s3_x_amz_version_id_2` string,
`s3_x_amz_server_side_encryption_customer_algorithm` string,
`s3_x_amz_server_side_encryption_customer_key` string,
`s3_x_amz_server_side_encryption_customer_key_MD5` string,
`s3_x_amz_server_side_encryption_kms_key_id_2` string,
`s3_x_amz_server_side_encryption_kms_key_id_arn_2` string,
`s3_x_amz_server_side_encryption_kms_key_id_alias_2` string,
`s3_x_amz_server_side_encryption_kms_key_id_type_2` string,
`s3_x_amz_server_side_encryption_kms_key_id_region_2` string,
`s3_x_amz_server_side_encryption_bucket_key_enabled_2` string,
`s3_x_amz_server_side_encryption_2` string,
`s3_x_amz_storage_class_2` string,
`s3_x_amz_website_redirect_location_2` string,
`s3_x_amz_version_id_3` string,
`s3_x_amz_acl_2` string,
`s3_x_amz_copy_source_version_id_2` string,
`s3_x_amz_copy_source_sse_kms_key_id_2` string,
`s3_x_amz_copy_source_sse_kms_key_id_alias_2` string,
`s3_x_amz_copy_source_sse_kms_key_id_arn_2` string,
`s3_x_amz_copy_source_sse_kms_key_id_type_2` string,
`s3_x_amz_copy_source_sse_kms_key_id_region_2` string,
`s3_x_amz_metadata_directive_2` string,
`s3_x_amz_copy_source_storage_class_2` string,
`s3_x_amz_restore_2` string,
`s3_x_amz_tagging_2` string,
`s3_x_amz_tagging_directive_2` string,
`s3_x_amz_version_id_4` string,
`s3_x_amz_server_side_encryption_customer_algorithm_2` string,
`s3_x_amz_server_side_encryption_customer_key_2` string,
`s3_x_amz_server_side_encryption_customer_key_MD5_2` string,
`s3_x_amz_server_side_encryption_kms_key_id_3` string,
`s3_x_amz_server_side_encryption_kms_key_id_arn_3` string,
`s3_x_amz_server_side_encryption_kms_key_id_alias_3` string,
`s3_x_amz_server_side_encryption_kms_key_id_type_3` string,
`s3_x_amz_server_side_encryption_kms_key_id_region_3` string,
`s3_x_amz_server_side_encryption_bucket_key_enabled_3` string,
`s3_x_amz_server_side_encryption_3` string,
`s3_x_amz_storage_class_3` string,
`s3_x_amz_website_redirect_location_3` string,
`s3_x_amz_version_id_5` string,
`s3_x_amz_acl_3` string,
`s3_x_amz_copy_source_version_id_3` string,
`s3_x_amz_copy_source_sse_kms_key_id_3` string,
`s3_x_amz_copy_source_sse_kms_key_id_alias_3` string,
`s3_x_amz_copy_source_sse_kms_key_id_arn_3` string,
`s3_x_amz_copy_source_sse_kms_key_id_type_3` string,
`s3_x_amz_copy_source_sse_kms_key_id_region_3` string,
`s3_x_amz_metadata_directive_3` string,
`s3_x_amz_copy_source_storage_class_3` string,
`s3_x_amz_restore_3` string,
`s3_x_amz_tagging_3` string,
`s3_x_amz_tagging_directive_3` string,
`s3_x_amz_version_id_6` string,
`s3_x_amz_server_side_encryption_customer_algorithm_3` string,
`s3_x_amz_server_side_encryption_customer_key_3` string,
`s3_x_amz_server_side_encryption_customer_key_MD5_3` string,
`s3_x_amz_server_side_encryption_kms_key_id_4` string,
`s3_x_amz_server_side_encryption_kms_key_id_arn_4` string,
`s3_x_amz_server_side_encryption_kms_key_id_alias_4` string,
`s3_x_amz_server_side_encryption_kms_key_id_type_4` string,
`s3_x_amz_server_side_encryption_kms_key_id_region_4` string,
`s3_x_amz_server_side_encryption_bucket_key_enabled_4` string,
`s3_x_amz_server_side_encryption_4` string,
`s3_x_amz_storage_class_4` string,
`s3_x_amz_website_redirect_location_4` string,
`s3_x_amz_version_id_7` string,
`s3_x_amz_acl_4` string,
`s3_x_amz_copy_source_version_id_4` string,
`s3_x_amz_copy_source_sse_kms_key_id_4` string,
`s3_x_amz_copy_source_sse_kms_key_id_alias_4` string,
`s3_x_amz_copy_source_sse_kms_key_id_arn_4` string,
`s3_x_amz_copy_source_sse_kms_key_id_type_4` string,
`s3_x_amz_copy_source_sse_kms_key_id_region_4` string,
`s3_x_amz_metadata_directive_4` string,
`s3_x_amz_copy_source_storage_class_4` string,
`s3_x_amz_restore_4` string,
`s3_x_amz_tagging_4` string,
`s3_x_amz_tagging_directive_4` string,
`s3_x_amz_version_id_8` string,
`s3_x_amz_server_side_encryption_customer_algorithm_4` string,
`s3_x_amz_server_side_encryption_customer_key_4` string,
`s3_x_amz_server_side_encryption_customer_key_MD5_4` string,
`s3_x_amz_server_side_encryption_kms_key_id_5` string,
`s3_x_amz_server_side_encryption_kms_key_id_arn_5` string,
`s3_x_amz_server_side_encryption_kms_key_id_alias_5` string,
`s3_x_amz_server_side_encryption_kms_key_id_type_5` string,
`s3_x_amz_server_side_encryption_kms_key_id_region_5` string,
`s3_x_amz_server_side_encryption_bucket_key_enabled_5` string,
`s3_x_amz_server_side_encryption_5` string,
`s3_x_amz_storage_class_5` string,
`s3_x_amz_website_redirect_location_5` string,
`s3_x_amz_version_id_9` string,
`s3_x_amz_acl_5` string,
`s3_x_amz_copy_source_version_id_5` string,
`s3_x_amz_copy_source_sse_kms_key_id_5` string,
`s3_x_amz_copy_source_sse_kms_key_id_alias_5` string,
`s3_x_amz_copy_source_sse_kms_key_id_arn_5` string,
`s3_x_amz_copy_source_sse_kms_key_id_type_5` string,
`s3_x_amz_copy_source_sse_kms_key_id_region_5` string,
`s3_x_amz_metadata_directive_5` string,
`s3_x_amz_copy_source_storage_class_5` string,
`s3_x_amz_restore_5` string,
`s3_x_amz_tagging_5` string,
`s3_x_amz_tagging_directive_5` string,
`s3_x_amz_version_id_10` string,
`s3_x_amz_server_side_encryption_customer_algorithm_5` string,
`s3_x_amz_server_side_encryption_customer_key_5` string,
`s3_x_amz_server_side_encryption_customer_key_MD5_5` string,
`s3_x_amz_server_side_encryption_kms_key_id_6` string,
`s3_x_amz_server_side_encryption_kms_key_id_arn_6` string,
`s3_x_amz_server_side_encryption_kms_key_id_alias_6` string,
`s3_x_amz_server_side_encryption_kms_key_id_type_6` string,
`s3_x_amz_server_side_encryption_kms_key_id_region_6` string,
`s3_x_amz_server_side_encryption_bucket_key_enabled_6` string,
`s3_x_amz_server_side_encryption_6` string,
`s3_x_amz_storage_class_6` string,
`s3_x_amz_website_redirect_location_6` string,
`s3_x_amz_version_id_11` string,
`s3_x_amz_acl_6` string,
`s3_x_amz_copy_source_version_id_6` string,
`s3_x_amz_copy_source_sse_kms_key_id_6` string,
`s3_x_amz_copy_source_sse_kms_key_id_alias_6` string,
`s3_x_amz_copy_source_sse_kms_key_id_arn_6` string,
`s3_x_amz_copy_source_sse_kms_key_id_type_6` string,
`s3_x_amz_copy_source_sse_kms_key_id_region_6` string,
`s3_x_amz_metadata_directive_6` string,
`s3_x_amz_copy_source_storage_class_6` string,
`s3_x_amz_restore_6` string,
`s3_x_amz_tagging_6` string,
`s3_x_amz_tagging_directive_6` string,
`s3_x_amz_version_id_12` string,
`s3_x_amz_server_side_encryption_customer_algorithm_6` string,
`s3_x_amz_server_side_encryption_customer_key_6` string,
`s3_x_amz_server_side_encryption_customer_key_MD5_6` string,
`s3_x_amz_server_side_encryption_kms_key_id_7` string,
`s3_x_amz_server_side_encryption_kms_key_id_arn_7` string,
`s3_x_amz_server_side_encryption_kms_key_id_alias_7` string,
`s3_x_amz_server_side_encryption_kms_key_id_type_7` string,
`s3_x_amz_server_side_encryption_kms_key_id_region_7` string,
`s3_x_amz_server_side_encryption_bucket_key_enabled_7` string,
`s3_x_amz_server_side_encryption_7` string,
`s3_x_amz_storage_class_7` string,
`s3_x_amz_website_redirect_location_7` string,
`s3_x_amz_version_id_13` string,
`s3_x_amz_acl_7` string,
`s3_x_amz_copy_source_version_id_7` string,
`s3_x_amz_copy_source_sse_kms_key_id_7` string,
`s3_x_amz_copy_source_sse_kms_key_id_alias_7` string,
`s3_x_amz_copy_source_sse_kms_key_id_arn_7` string,
`s3_x_amz_copy_source_sse_kms_key_id_type_7` string,
`s3_x_amz_copy_source_sse_kms_key_id_region_7` string,
`s3_x_amz_metadata_directive_7` string,
`s3_x_amz_copy_source_storage_class_7` string,
`s3_x_amz_restore_7` string,
`s3_x_amz_tagging_7` string,
`s3_x_amz_tagging_directive_7` string,
`s3_x_amz_version_id_14` string,
`s3_x_amz_server_side_encryption_customer_algorithm_7` string,
`s3_x_amz_server_side_encryption_customer_key_7` string,
`s3_x_amz_server_side_encryption_customer_key_MD5_7` string,
`s3_x_amz_server_side_encryption_kms_key_id_8` string,
`s3_x_amz_server_side_encryption_kms_key_id_arn_8` string,
`s3_x_amz_server_side_encryption_kms_key_id_alias_8` string,
`s3_x_amz_server_side_encryption_kms_key_id_type_8` string,
`s3_x_amz_server_side_encryption_kms_key_id_region_8` string,
`s3_x_amz_server_side_encryption_bucket_key_enabled_8` string,
`s3_x_amz_server_side_encryption_8` string,
`s3_x_amz_storage_class_8` string,
`s3_x_amz_website_redirect_location_8` string,
`s3_x_amz_version_id_15` string,
`s3_x_amz_acl_8` string,
`s3_x_amz_copy_source_version_id_8` string,
`s3_x_amz_copy_source_sse_kms_key_id_8` string,
`s3_x_amz_copy_source_sse_kms_key_id_alias_8` string,
`s3_x_amz_copy_source_sse_kms_key_id_arn_8` string,
`s3_x_amz_copy_source_sse_kms_key_id_type_8` string,
`s3_x_amz_copy_source_sse_kms_key_id_region_8` string,
`s3_x_amz_metadata_directive_8` string,
`s3_x_amz_copy_source_storage_class_8` string,
`s3_x_amz_restore_8` string,
`s3_x_amz_tagging_8` string,
`s3_x_amz_tagging_directive_8` string,
`s3_x_amz_version_id_16` string,
`s3_x_amz_server_side_encryption_customer_algorithm_8` string,
`s3_x_amz_server_side_encryption_customer_key_8` string,
`s3_x_amz_server_side_encryption_customer_key_MD5_8` string,
`s3_x_amz_server_side_encryption_kms_key_id_9` string,
`s3_x_amz_server_side_encryption_kms_key_id_arn_9` string,
`s3_x_amz_server_side_encryption_kms_key_id_alias_9` string,
`s3_x_amz_server_side_encryption_kms_key_id_type_9` string,
`s3_x_amz_server_side_encryption_kms_key_id_region_9` string,
`s3_x_amz_server_side_encryption_bucket_key_enabled_9` string,
`s3_x_amz_server_side_encryption_9` string,
`s3_x_amz_storage_class_9` string,
`s3_x_amz_website_redirect_location_9` string,
`s3_x_amz_version_id_17` string,
`s3_x_amz_acl_9` string,
`s3_x_amz_copy_source_version_id_9` string,
`s3_x_amz_copy_source_sse_kms_key_id_9` string,
`s3_x_amz_copy_source_sse_kms_key_id_alias_9` string,
`s3_x_amz_copy_source_sse_kms_key_id_arn_9` string,
`s3_x_amz_copy_source_sse_kms_key_id_type_9` string,
`s3_x_amz_copy_source_sse_kms_key_id_region_9` string,
`s3_x_amz_metadata_directive_9` string,
`s3_x_amz_copy_source_storage_class_9` string,
`s3_x_amz_restore_9` string,
`s3_x_amz_tagging_9` string,
`s3_x_amz_tagging_directive_9` string,
`s3_x_amz_version_id_18` string,
`s3_x_amz_server_side_encryption_customer_algorithm_9` string,
`s3_x_amz_server_side_encryption_customer_key_9` string,
`s3_x_amz_server_side_encryption_customer_key_MD5_9` string,
`s3_x_amz_server_side_encryption_kms_key_id_10` string,
`s3_x_amz_server_side_encryption_kms_key_id_arn_10` string,
`s3_x_amz_server_side_encryption_kms_key_id_alias_10` string,
`s3_x_amz_server_side_encryption_kms_key_id_type_10` string,
`s3_x_amz_server_side_encryption_kms_key_id_region_10` string,
`s3_x_amz_server_side_encryption_bucket_key_enabled_10` string,
`s3_x_amz_server_side_encryption_10` string,
`s3_x_amz_storage_class_10` string,
`s3_x_amz_website_redirect_location_10` string,
`s3_x_amz_version_id_19` string,
`s3_x_amz_acl_10` string,
`s3_x_amz_copy_source_version_id_10` string,
`s3_x_amz_copy_source_sse_kms_key_id_10` string,
`s3_x_amz_copy_source_sse_kms_key_id_alias_10` string,
`s3_x_amz_copy_source_sse_kms_key_id_arn_10` string,
`s3_x_amz_copy_source_sse_kms_key_id_type_10` string,
`s3_x_amz_copy_source_sse_kms_key_id_region_10` string,
`s3_x_amz_metadata_directive_10` string,
`s3_x_amz_copy_source_storage_class_10` string,
`s3_x_amz_restore_10` string,
`s3_x_amz_tagging_10` string,
`s3_x_amz_tagging_directive_10` string,
`s3_x_amz_version_id_20` string,
`s3_x_amz_server_side_encryption_customer_algorithm_10` string,
`s3_x_amz_server_side_encryption_customer_key_10` string,
`s3_x_amz_server_side_encryption_customer_key_MD5_10` string,
`s3_x_amz_server_side_encryption_kms_key_id_11` string,
`s3_x_amz_server_side_encryption_kms_key_id_arn_11` string,
`s3_x_amz_server_side_encryption_kms_key_id_alias_11` string,
`s3_x_amz_server_side_encryption_kms_key_id_type_11` string,
`s3_x_amz_server_side_encryption_kms_key_id_region_11` string,
`s3_x_amz_server_side_encryption_bucket_key_enabled_11` string,
`s3_x_amz_server_side_encryption_11` string,
`s3_x_amz_storage_class_11` string,
`s3_x_amz_website_redirect_location_11` string,
`s3_x_amz_version_id_21` string,
`s3_x_amz_acl_11` string,
`s3_x_amz_copy_source_version_id_11` string,
`s3_x_amz_copy_source_sse_kms_key_id_11` string,
`s3_x_amz_copy_source_sse_kms_key_id_alias_11` string,
`s3_x_amz_copy_source_sse_kms_key_id_arn_11` string,
`s3_x_amz_copy_source_sse_kms_key_id_type_11` string,
`s3_x_amz_copy_source_sse_kms_key_id_region_11` string,
`s3_x_amz_metadata_directive_11` string,
`s3_x_amz_copy_source_storage_class_11` string,
`s3_x_amz_restore_11` string,
`s3_x_amz_tagging_11` string,
`s3_x_amz_tagging_directive_11` string,
`s3_x_amz_version_id_22` string,
`s3_x_amz_server_side_encryption_customer_algorithm_11` string,
`s3_x_amz_server_side_encryption_customer_key_11` string,
`s3_x_amz_server_side_encryption_customer_key_MD5_11` string,
`s3_x_amz_server_side_encryption_kms_key_id_12` string,
`s3_x_amz_server_side_encryption_kms_key_id_arn_12` string,
`s3_x_amz_server_side_encryption_kms_key_id_alias_12` string,
`s3_x_amz_server_side_encryption_kms_key_id_type_12` string,
`s3_x_amz_server_side_encryption_kms_key_id_region_12` string,
`s3_x_amz_server_side_encryption_bucket_key_enabled_12` string,
`s3_x_amz_server_side_encryption_12` string,
`s3_x_amz_storage_class_12` string,
`s3_x_amz_website_redirect_location_12` string,
`s3_x_amz_version_id_23` string,
`s3_x_amz_acl_12` string,
`s3_x_amz_copy_source_version_id_12` string,
`s3_x_amz_copy_source_sse_kms_key_id_12` string,
`s3_x_amz_copy_source_sse_kms_key_id_alias_12` string,
`s3_x_amz_copy_source_sse_kms_key_id_arn_12` string,
`s3_x_amz_copy_source_sse_kms_key_id_type_12` string,
`s3_x_amz_copy_source_sse_kms_key_id_region_12` string,
`s3_x_amz_metadata_directive_12` string,
`s3_x_amz_copy_source