AWS Database Blog

Using DML auditing for Amazon Keyspaces (for Apache Cassandra)

Amazon Keyspaces (for Apache Cassandra) is a scalable, highly available, and managed Apache Cassandra-compatible database service. With Amazon Keyspaces, you can run your Cassandra workloads on AWS using the same Cassandra application code and developer tools that you use today. You don’t have to provision, patch, or manage servers, and you don’t have to install, maintain, or operate software.

Amazon Keyspaces recently added support for auditing DML events. This added support is an extension of the existing DDL auditing capabilities that were available before. For more information, see Amazon Keyspaces Data Definition Language (DDL) information in CloudTrail. With the addition of DML auditing, you can now log and audit read, update, insert, and delete operations with AWS CloudTrail.

This post discusses why DML auditing is important for some organizations, and walks you through setting it up for Amazon Keyspaces. Then, using an example, we show how native integration between Amazon Keyspaces and CloudTrail makes it straightforward to record and analyze audit trails (change events) from multiple tables in a keyspace without the use of additional tools.

Benefits of DML auditing

Some organizations that use Amazon Keyspaces may be operating either in highly regulated industry sectors or in geographies with strict laws governing data privacy and sovereignty that mandate organizations audit data access and usage. Data privacy laws and security requirements typically specify that an organization has to be able to demonstrate that all data access is logged and that all logs are retained and can be audited for a defined period of time. The following are some examples:

  • Privacy regulations – Privacy and data governance typically requires monitoring of authorized access to data and requires that an audit trail contain the following information:
    • Time when data was accessed
    • Who accessed the data (user, source IP)
    • The actions the user performed (read, update, insert, or delete)
  • Security requirements – Information security requirements can come from various sources, including but not limited to company policies, laws and rules for regulated industries, and data privacy laws. Security is mostly concerned with unauthorized access to data, generally from threat actors, including malicious insiders.

Database administrators managing regulated or sensitive data in Amazon Keyspaces can now log the required database events and use CloudTrail and Amazon CloudWatch integrations to implement automatic monitoring and alerting. CloudWatch logs allow you to monitor and receive alerts for specific events, for example data deletions, bulk updates, or reads captured by CloudTrail. In addition, the logs create an audit trail of events, which can be used to help assess and perform root cause analysis on security events.

The following sections show in more detail how to set up DML auditing in Amazon Keyspaces and how it can help you demonstrate compliance with the security and data governance requirements of your organization.

Solution overview

Amazon Keyspaces is integrated with CloudTrail, a service that provides a record of AWS API calls made by an AWS Identity and Access Management (IAM) identity, for example a user, role, or AWS service. CloudTrail captures the relevant API calls for Amazon Keyspaces as events. The log includes calls made as a result of interactions on the Amazon Keyspaces console and calls made from code using the Amazon Keyspaces APIs, covering both Cassandra Query Language (CQL) and AWS SDK actions. You can use the information collected by CloudTrail to review the requests that were made to Amazon Keyspaces, the IP addresses from which the requests originated, the IAM principals who made the requests, the request timestamps, and additional details.

Create a CloudTrail log with Cassandra table DML events

Auditing of DML events is not enabled by default in CloudTrail. You can enable logging of DML events using the CloudTrail console or AWS Command Line Interface (CLI). In this post, we use the console. To follow along using the AWS CLI, refer to Creating, updating, and managing trails with the AWS CLI.

Complete the following steps:

  1. On the CloudTrail console, choose Trails in the navigation pane.
  2. Choose Create trail.
  3. Enter a trail name.
  4. Choose Create new S3 bucket.
  5. Confirm that log file SSE-KMS encryption is enabled.
  6. For Log file SSE-KMS encryption, choose a new customer managed encryption key. Enter a name for the AWS Key Management Service (AWS KMS) alias. The new key will be automatically created for you.
  7. Confirm that Log file validation is enabled.
  8. Skip all the optional sections for now and choose Next
  9. Deselect Management events and select Data events. This makes it less difficult to find relevant events, because data events are separated into their own log file.
  10. For Data events, under Data event type, choose Cassandra table.
  11. For Log selector template, choose Log all events.
  12. Choose Next to review your configuration.
  13. Choose Create trail.

You have now created a data trail for the following DML data events for Amazon Keyspaces.

CloudTrail eventName CQL Action AWS SDK Action
Select SELECT GetKeyspace, GetTable, ListKeyspaces, ListTables ListTagsForResource
Insert INSERT No AWS SDK actions available
Update UPDATE No AWS SDK actions available
Delete DELETE No AWS SDK actions available

Review a CloudTrail log file

Within about 5 minutes after creating the Amazon Keyspaces DML trail, CloudTrail delivers the first set of log files to an Amazon Simple Storage Service (Amazon S3) bucket. Let’s run some CQL statements on an Amazon Keyspaces table and look at a log file to learn about the information it contains. If you don’t already have a sample table, refer to Getting started with Amazon Keyspaces (for Apache Cassandra) to create a sample keyspace and table and upload some sample data from a CSV file.

  1. On the Amazon Keyspaces console, choose CQL editor in the navigation pane.
  2. Use a test table in the CQL editor to delete a row. The following CQL statement is an example: 
    DELETE book_title FROM catalog.book_awards WHERE year=2020 AND award='Richard Roe' AND category='Fiction' AND rank=1;
  3. On the CloudTrail console, choose Trails in the navigation pane.
  4. Open the trail you just created.
  5. Under trail details, choose Trail log location.
    The Amazon S3 console opens and shows that bucket, at the top level for log files.
  6. Navigate through the bucket folder structure to today’s date to review the log of activity to find the delete event.

You should see a file that begins with your AWS account ID and ends with the extension .gz. To view a .gz file, you first have to download the file and then unzip or extract the content. You can then view the extracted file in a plain text editor or a JSON file viewer.

Let’s look at the following log file entry to review our delete event:

{
         "eventVersion":"1.09",
         "userIdentity":{
            "type":"AssumedRole",
            "principalId":"EXAMPLEIDQQPPZZYYY22:test-user",
            "arn":"arn:aws:sts::111222333444:assumed-role/Admin/test-user",
            "accountId":"111222333444"
         },
         "eventTime":"2024-02-09T15:18:02Z",
         "eventSource":"cassandra.amazonaws.com",
         "eventName":"Delete",
         "awsRegion":"us-east-1",
         "sourceIPAddress":"AWS Internal",
         "userAgent":"Cassandra Client/ProtocolV4",
         "requestParameters":{
            "keyspaceName":"catalog",
            "tableName":"book_awards",
            "primaryKeys":{
               "award":"'W**(Redacted)f'",
               "year":"2**(Redacted)0",
               "category":"'N**(Redacted)n'",
               "rank":"**(Redacted)"
            },
            "conditions":[
               
            ],
            "deleteColumnNames":[
               
            ],
            "updateParameters":{
               
            }
         },
         "responseElements":null,
         "requestID":"f56c6125-8ee4-478e-853f-a8b0ad94dc5a",
         "eventID":"3959d476-1da2-4741-96b3-59ea726750b8",
         "readOnly":false,
         "resources":[
            {
               "accountId":"111222333444",
               "type":"AWS::Cassandra::Table",
               "ARN":"arn:aws:cassandra:us-east-1:111222333444:/keyspace/catalog/table/book_awards"
            }
         ],
         "eventType":"AwsApiCall",
         "apiVersion":"3.4.4",
         "managementEvent":false,
         "recipientAccountId":"111222333444",
         "eventCategory":"Data",
         "tlsDetails":{
            "tlsVersion":"TLSv1.3",
            "cipherSuite":"TLS_AES_128_GCM_SHA256",
            "clientProvidedHostHeader":"cassandra.us-east-1.amazonaws.com"
         }
      },

From this log file entry, you can see that an IAM user with the name test-user in the AWS account 111222333444 has deleted one row from a table with the name book_awards in the keyspace catalog located in the AWS Region US East (N. Virginia). You can see the conditions of the row that were deleted, but the actual data has been redacted to prevent sensitive data from leaking into log files. You can also see that the user connected with an IP address AWS Internal (which is consistent with the API call being made by the Amazon Keyspaces console) and deleted the row at the following timestamp: 2024-02-09T15:18:02Z.

Next, let’s set up a CloudWatch log for all DML events, a metric filter, and a CloudWatch alarm.

Create a log group in CloudTrail to send logs to CloudWatch

Complete the following steps to set up a log group:

  1. On the CloudTrail console, choose Trails in the navigation pane.
  2. Choose the trail you created earlier.
  3. In the CloudWatch Logs section, choose Edit.
  4. For CloudWatch Logs, choose Enabled.
  5. For Log group name, choose New and let CloudTrail specify a name.
  6. For IAM Role, choose New to create a new IAM role for permissions to send logs to CloudWatch Logs. For Role name, enter a name for the new role.
    The policy statement for the new role is displayed when you expand Policy document.
  7. Choose Save changes.

Create a metric filter in CloudWatch

Because finding the events you’re looking for from a full set of logs can be an involved process, we set up a metric filter to have CloudWatch find and report the events you’re interested in, automatically. In this example, we create a metric filter for delete events. Complete the following steps:

  1. On the CloudWatch console, in the navigation pane, under Logs, choose Log groups.
  2. In the list of log groups, choose the log group that you created earlier for your trail.
  3. On the Metric filters or Actions menu, choose Create metric filter.
  4. On the Define pattern page, provide the following details:
    1. For Create filter pattern, enter the following for Filter pattern: { $.eventName = "Delete" }.
    2. For Test pattern, leave the defaults.
    3. Choose Next.
  5. On the Assign metric page, provide the following information:
    1. For Filter name, enter DeleteEvents.
    2. In Metric details, turn on Create new, then enter CloudTrailMetrics for Metric namespace.
    3. For Metric name, enter DeleteEventCount.
    4. For Metric value, enter 1.
    5. Leave Default value blank.
    6. Leave Unit value blank.
    7. Choose Next.
  6. On the Review and create page, review your configuration and choose Create metric filter.

Create a CloudWatch alarm for automatic notification

After you create the metric filter, the details page for your CloudWatch trail log group opens. Complete the following steps to create an alarm:

  1. On the Metric filters tab, select the metric filter you created in the last step.
  2. For Metric filters, choose Create alarm.
  3. For Specify metric and conditions, enter the following:
    1. For Graph, the line is set at 1 based on other settings you make when you create your alarm.
    2. For Metric name, keep the current metric name, DeleteEventCount.
    3. For Statistic, keep the default, Sum.
    4. For Period, keep the default, 5 minutes.
    5. Under Conditions, for Threshold type, choose Static.
    6. For Whenever DeleteEventCount is, choose Greater/Equal.
    7. For the threshold value, enter 1.
    8. In Additional configuration, leave defaults.
    9. Choose Next.
  4. On the Configure actions page, provide the following information:
    1. Choose Notification, then choose In alarm, which indicates that the action is taken when the threshold of one change event in 5 minutes is crossed, and DeleteEventCount is in an alarm state.
    2. For Send a notification to the following SNS topic, choose Create new topic.
    3. Enter DeleteChanges_CloudWatch_Alarms_Topic as the name for the new Amazon Simple Notification Service (Amazon SNS) topic.
    4. For Email endpoints that will receive the notification, enter your email address to receive a notification if this alarm is raised. You will receive an email asking you to confirm your subscription. You’ll need to accept this in your email client before you can receive the notifications.
    5. Choose Create topic.
    6. For this example, skip the other action types.
    7. Choose Next.
  5. On the Add name and description page, provide the following information:
    1. Enter a name for the alarm (for example, Delete events in Keyspaces table) and a description (for example, Raises alarm if data is deleted from table).
    2. Choose Next.
  6. On the Preview and create page, review your configuration and choose Create alarm to create the alarm.

After you create the alarm, CloudWatch opens the Alarms page. The alarm’s Actions column shows Pending confirmation until you have confirmed your subscription to SNS notifications.

You can now go back to your Amazon Keyspaces table and delete a row of data. Within 5 minutes, you’ll see a notification in your inbox that will look similar to the following example.

Clean up

To avoid incurring further costs, delete the resources you created while following this tutorial:

  1. Delete the test table you created for this tutorial. For more information, refer to Tutorial Step 3: Delete a table and a keyspace in Amazon Keyspaces. The following statement is an example of the CQL statement: 
    DROP TABLE  catalog.book_awards ;
  2. Delete the CloudTrail trail that logs data events. For more information, see Deleting a trail with the CloudTrail console.
  3. Empty and delete the S3 bucket containing existing logs. For more information, see Emptying a bucket and Deleting a bucket.
  4. Delete the metric filter and the log group that is used by CloudWatch. For more information, see Deleting a metric filter and Deleting the AWS CloudWatch logs.
  5. Disable the customer managed key stored in AWS KMS and schedule it for deletion. For more information, see Scheduling and canceling key deletion.

Additional considerations

Before you start logging DML events, consider the pricing implications of this feature. Amazon Keyspaces imposes no additional charge to enable DML auditing, but CloudTrail only logs management events by default. For more information on CloudTrail pricing for logging DML events, see AWS CloudTrail pricing.

Finally, we want to share some current configuration recommendations that you might find useful. To get through the setup for this tutorial quickly, we mostly used default options and chose the simplest configurations. For production environments, there are additional options available that enhance the security of your log data and help make your Amazon Keyspaces monitoring solution successful. The following are additional steps that you can take to keep your data more secure:

  • Lock down your S3 log file bucket – By default, the S3 bucket you created when creating a trail has a security policy that allows CloudTrail to write log files to that bucket. Make sure this bucket is not accessible to other users in your AWS account who don’t need access. Review the security policy for your bucket and restrict access on a need-to-know basis. For more information, see Amazon S3 security and Controlling access to a bucket with user policies.
  • Use a customer managed KMS key for log file encryption – The log files delivered by CloudTrail to your bucket are encrypted by Amazon server-side encryption with Amazon S3 managed encryption keys (SSE-S3). To provide a security layer that you manage, you can instead use server-side encryption with AWS KMS managed keys (SSE-KMS) for your CloudTrail log files. To use SSE-KMS with CloudTrail, you create and manage a KMS key. For more information, see Encrypting CloudTrail log files with AWS KMS keys (SSE-KMS).
  • Apply trails to all Regions – To obtain a complete record of events taken by an IAM identity or service in Amazon Keyspaces, configure your trail to log events in all Regions. When you create a trail that applies to all Regions, CloudTrail records events in each Region and delivers the CloudTrail event log files to an S3 bucket that you specify. If a Region is added after you create a trail that applies to all Regions, that new Region is automatically included, and events in that Region are logged. Consider the Region you choose to place your CloudTrail bucket in carefully, if you choose to configure a multi-Region trail, because you may need to take per-jurisdiction data export restrictions into account for all the Regions in which you have AWS environments built.
  • Enable CloudTrail log file integrity – Validated log files are especially valuable in security and forensic investigations. For example, a validated log file enables you to assert first that the log file itself has not been altered, and then that a particular IAM identity performed a specific API activity. The CloudTrail log file integrity validation process specifies if a log file has been deleted or changed. CloudTrail log file integrity validation uses industry standard algorithms: SHA-256 for hashing and SHA-256 with RSA for digital signing. This makes it computationally impractical to modify, delete, or forge CloudTrail log files without detection. For more information, see Enabling validation and validating files.
  • Monitor your trails – Consider implementing ongoing detective controls to help make sure all trails are sending events to CloudWatch logs for monitoring by using the cloud-trail-cloud-watch-logs-enabled managed rule in AWS Config.

Conclusion

In this post, we discussed the importance of DML auditing and demonstrated how to set it up for Amazon Keyspaces. Additionally, the integration between Amazon Keyspaces and CloudTrail makes it straightforward to record and analyze audit trails from multiple tables in a keyspace without the use of additional tools.

Try the solution out for yourself, and leave any feedback or questions in the comments.

About the author

Alice Kupcik is a Senior Programmer Writer for Amazon Keyspaces. She writes the Amazon Keyspaces Developer Guide and API Reference. Her passion is database security and data privacy.