AWS AmazonS3 medium security documentation change
Summary
Restructured documentation to separate journal and inventory table examples, added new KMS-related queries, updated placeholder names, and expanded encryption monitoring examples
Security assessment
Added multiple queries related to KMS key usage monitoring (e.g., 'Listing objects not encrypted with SSE-KMS', 'Listing AWS KMS encryption keys used for PUT operations') which directly help identify security configuration gaps. These changes provide concrete tools for auditing encryption practices, making them security-related.
Diff
diff --git a/AmazonS3/latest/userguide/metadata-tables-example-queries.md b/AmazonS3/latest/userguide/metadata-tables-example-queries.md index f02a0b82d..5944e99b0 100644 --- a//AmazonS3/latest/userguide/metadata-tables-example-queries.md +++ b//AmazonS3/latest/userguide/metadata-tables-example-queries.md @@ -5 +5 @@ -Finding objects by file extensionListing object deletionsListing AWS KMS encryption keys used by your objectsListing objects that don't use KMS keysViewing metadata provided by Amazon BedrockUnderstanding the current state of your objects +Journal table example queriesInventory table example queries @@ -17 +17 @@ Remember when using these examples: - * Replace ``amzn-s3-demo-bucket`` with the name of the S3 table bucket that's storing your metadata table. + * Replace `b_`general-purpose-bucket-name`` with the name of your namespace. @@ -19 +19 @@ Remember when using these examples: - * Replace ``my_metadata_table`` with the name of the metadata table that you're querying. + * For a full list of supported columns, see the [S3 Metadata journal tables schema](./metadata-tables-schema.html) and [S3 Metadata live inventory tables schema](./metadata-tables-inventory-schema.html). @@ -21 +20,0 @@ Remember when using these examples: - * For a full list of supported columns, see the [S3 Metadata tables schema](./metadata-tables-schema.html). @@ -24,0 +24 @@ Remember when using these examples: +###### Contents @@ -26 +26 @@ Remember when using these examples: -## Finding objects by file extension + * [Journal table example queries](./metadata-tables-example-queries.html#metadata-tables-example-queries-journal-tables) @@ -28 +28 @@ Remember when using these examples: -The following query returns objects with a specific file extension (`.jpg` in this case). + * [Finding objects by file extension](./metadata-tables-example-queries.html#metadata-tables-example-query-object-pattern) @@ -29,0 +30 @@ The following query returns objects with a specific file extension (`.jpg` in th + * [Listing object deletions](./metadata-tables-example-queries.html#metadata-tables-example-query-delete-events) @@ -31 +32,39 @@ The following query returns objects with a specific file extension (`.jpg` in th - SELECT key FROM "s3tablescatalog/amzn-s3-demo-bucket"."aws_s3_metadata"."my_metadata_table" + * [Listing AWS KMS encryption keys used by your objects](./metadata-tables-example-queries.html#metadata-tables-example-query-objects-using-kms-key) + + * [Listing objects that don't use KMS keys](./metadata-tables-example-queries.html#metadata-tables-example-query-objects-not-using-kms-key) + + * [Listing AWS KMS encryption keys used for PUT operations in the last 7 days](./metadata-tables-example-queries.html#metadata-tables-example-query-objects-using-kms-key-puts) + + * [Listing objects deleted in the last 24 hours by S3 Lifecycle](./metadata-tables-example-queries.html#metadata-tables-example-query-objects-deleted-lifecycle) + + * [Viewing metadata provided by Amazon Bedrock](./metadata-tables-example-queries.html#metadata-tables-example-query-bedrock) + + * [Understanding the current state of your objects](./metadata-tables-example-queries.html#metadata-tables-example-query-current-state) + + * [Inventory table example queries](./metadata-tables-example-queries.html#metadata-tables-example-queries-inventory-tables) + + * [Discovering datasets that use specific tags](./metadata-tables-example-queries.html#metadata-tables-example-query-datasets-specific-tags) + + * [Listing objects not encrypted with SSE-KMS](./metadata-tables-example-queries.html#metadata-tables-example-query-objects-not-kms-encrypted) + + * [Listing objects generated by Amazon Bedrock](./metadata-tables-example-queries.html#metadata-tables-example-query-objects-generated-bedrock) + + * [Generating the latest inventory table](./metadata-tables-example-queries.html#metadata-tables-example-query-generate-latest-inventory) + + * [Joining custom metadata with S3 metadata tables](./metadata-tables-join-custom-metadata.html) + + * [Visualizing metadata table data with Amazon QuickSight](./metadata-tables-quicksight-dashboards.html) + + + + +## Journal table example queries + +You can use the following example queries to query your journal tables. + +### Finding objects by file extension + +The following query returns objects with a specific file extension (`.jpg` in this case): + + + SELECT key FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."journal" @@ -35 +74 @@ The following query returns objects with a specific file extension (`.jpg` in th -## Listing object deletions +### Listing object deletions @@ -37 +76 @@ The following query returns objects with a specific file extension (`.jpg` in th -The following query returns object deletion events, including the AWS account ID or AWS service principal that made the request. +The following query returns object deletion events, including the AWS account ID or AWS service principal that made the request: @@ -41 +80 @@ The following query returns object deletion events, including the AWS account ID - FROM "s3tablescatalog/amzn-s3-demo-bucket"."aws_s3_metadata"."my_metadata_table" + FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."journal" @@ -44 +83 @@ The following query returns object deletion events, including the AWS account ID -## Listing AWS KMS encryption keys used by your objects +### Listing AWS KMS encryption keys used by your objects @@ -46 +85 @@ The following query returns object deletion events, including the AWS account ID -The following query returns the ARNs of the AWS Key Management Service (AWS KMS) keys encrypting your objects. +The following query returns the ARNs of the AWS Key Management Service (AWS KMS) keys encrypting your objects: @@ -50 +89 @@ The following query returns the ARNs of the AWS Key Management Service (AWS KMS) - FROM "s3tablescatalog/amzn-s3-demo-bucket"."aws_s3_metadata"."my_metadata_table"; + FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."journal"; @@ -52 +91 @@ The following query returns the ARNs of the AWS Key Management Service (AWS KMS) -## Listing objects that don't use KMS keys +### Listing objects that don't use KMS keys @@ -54 +93 @@ The following query returns the ARNs of the AWS Key Management Service (AWS KMS) -The following query returns objects that aren't encrypted with AWS KMS keys. +The following query returns objects that aren't encrypted with AWS KMS keys: @@ -58 +97 @@ The following query returns objects that aren't encrypted with AWS KMS keys. - FROM "s3tablescatalog/amzn-s3-demo-bucket"."aws_s3_metadata"."my_metadata_table" + FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."journal" @@ -62 +101,13 @@ The following query returns objects that aren't encrypted with AWS KMS keys. -## Viewing metadata provided by Amazon Bedrock +### Listing AWS KMS encryption keys used for `PUT` operations in the last 7 days + +The following query returns the ARNs of the AWS Key Management Service (AWS KMS) keys encrypting your objects: + + + SELECT DISTINCT kms_key_arn + FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."journal" + WHERE record_timestamp > (current_date - interval '7' day) + AND kms_key_arn is NOT NULL; + +### Listing objects deleted in the last 24 hours by S3 Lifecycle + +The following query returns lists the objects expired in the last day by S3 Lifecycle: @@ -64 +115,10 @@ The following query returns objects that aren't encrypted with AWS KMS keys. -Some AWS services (such as [Amazon Bedrock](https://docs.aws.amazon.com/bedrock/latest/APIReference/welcome.html)), upload objects to Amazon S3. You can query the object metadata provided by these services. For example, the following query includes the `user_metadata` column to determine if there are objects uploaded by Amazon Bedrock to a general purpose bucket. + + SELECT bucket, key, version_id, last_modified_date, record_timestamp, requester + FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."journal" + WHERE requester = 's3.amazonaws.com' + AND record_type = 'DELETE' + AND record_timestamp > (current_date - interval '1' day) + +### Viewing metadata provided by Amazon Bedrock + +Some AWS services (such as [Amazon Bedrock](https://docs.aws.amazon.com/bedrock/latest/APIReference/welcome.html)), upload objects to Amazon S3. You can query the object metadata provided by these services. For example, the following query includes the `user_metadata` column to determine if there are objects uploaded by Amazon Bedrock to a general purpose bucket: @@ -68 +128 @@ Some AWS services (such as [Amazon Bedrock](https://docs.aws.amazon.com/bedrock/ - FROM "s3tablescatalog/amzn-s3-demo-bucket"."aws_s3_metadata"."my_metadata_table" + FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."journal" @@ -78 +138 @@ If Amazon Bedrock uploaded an object to your bucket, the `user_metadata` column -## Understanding the current state of your objects +### Understanding the current state of your objects @@ -85 +145 @@ The following query can help you determine the current state of your objects. Th - SELECT * from "s3tablescatalog/amzn-s3-demo-bucket"."aws_s3_metadata"."my_metadata_table" + SELECT * from "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."journal" @@ -126,0 +187,163 @@ The following query can help you determine the current state of your objects. Th +## Inventory table example queries + +You can use the following example queries to query your inventory tables. + +### Discovering datasets that use specific tags + +The following query returns the dataset that uses the specified tags: + + + SELECT * + FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."inventory" + WHERE object_tags['key1'] = 'value1' + AND object_tags['key2'] = 'value2'; + +### Listing objects not encrypted with SSE-KMS + +The following query returns objects that aren't encrypted with SSE-KMS: + + + SELECT key, encryption_status + FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."inventory" + WHERE encryption_status != 'SSE-KMS'; + +### Listing objects generated by Amazon Bedrock + +The following query lists objects that were generated by Amazon Bedrock: + + + SELECT DISTINCT bucket, key, sequence_number, user_metadata + FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."inventory" + WHERE user_metadata['content-source'] = 'AmazonBedrock'; + +### Generating the latest inventory table + +The following query generates the most recent inventory table. This query works when your metadata configuration is in **Active** status. This query requires both the journal and inventory tables to be in **Active** status. If your inventory table isn't up to date because of permission or other issues, this query might not work. + +We recommend using this query for general purpose buckets with less than a billion objects. + +This query reconciles the contents of the inventory table with the recent events of the journal table. When your journal becomes up to date with all the changes that occurred in your bucket, the query result will match that of the contents of the bucket. + +This example restricts the output to only those keys ending with `'%.txt'`. To query a different subset, you can adjust the common table expression named `"working_set_of_interest"`. + + + WITH inventory_time_cte AS ( + -- Reveal the extent of the journal table that has not yet been reflected in the inventory table. + SELECT COALESCE(inventory_time_from_property, inventory_time_default) AS inventory_time FROM + ( + SELECT * FROM + -- The fallback default includes the entirety of the journal table. + (VALUES (TIMESTAMP '2024-12-01 00:00')) AS T (inventory_time_default) + LEFT OUTER JOIN + -- This side queries the Iceberg table property and loads it up in + -- a column. If the property doesn't exist, then you get 0 rows. + ( + SELECT from_unixtime(CAST(value AS BIGINT)) AS inventory_time_from_property FROM "journal$properties" + WHERE key = 'aws.s3metadata.oldest-uncoalesced-record-timestamp' LIMIT 1 + ) + -- Force an unequivocal join. + ON TRUE + ) + ), + + -- Select only those journal table events not yet reflected in the inventory table. + my_new_events AS ( + SELECT journal.* FROM + ( + journal + JOIN + inventory_time_cte + -- Include only those rows that have yet to be merged with the inventory table.