Visualize Amazon Redshift audit logs using Amazon Athena and Amazon QuickSight - AWS Prescriptive Guidance
SummaryPrerequisites and limitationsArchitectureToolsEpicsRelated resourcesAttachments

Visualize Amazon Redshift audit logs using Amazon Athena and Amazon QuickSight

Created by Sanket Sirsikar (AWS) and Gopal Krishna Bhatia (AWS)

Environment: PoC or pilot

Technologies: Analytics; Big data; Data lakes

AWS services: Amazon Athena; Amazon Redshift; Amazon S3; Amazon QuickSight

Summary

Security is an integral part of database operations on the Amazon Web Services (AWS) Cloud. Your organization should ensure that it monitors database user activities and connections to detect potential security incidents and risks. This pattern helps you monitor your databases for security and troubleshooting purposes, which is a process known as database auditing.

This pattern provides a SQL script that automates the creation of an Amazon Athena table and views for a reporting dashboard in Amazon QuickSight that helps you audit Amazon Redshift logs. This ensures that users responsible for monitoring database activities have convenient access to data security features.  

Prerequisites and limitations

Prerequisites 

Architecture

Technology stack  

  • Athena

  • Amazon Redshift 

  • Amazon S3 

  • QuickSight

Tools

  • Amazon Athena – Athena is an interactive query service that makes it easy to analyze data in Amazon S3 using standard SQL. 

  • Amazon QuickSight – QuickSight is a scalable, serverless, embeddable, machine learning-powered business intelligence (BI) service. 

  • Amazon Redshift – Amazon Redshift is an enterprise-level, petabyte scale, fully managed data warehousing service. 

  • Amazon S3 – Amazon Simple Storage Service (Amazon S3) is storage for the internet.

Epics

TaskDescriptionSkills required
Enable audit logging for the Amazon Redshift cluster.

  1. Sign in to the AWS Management Console, open the Amazon Redshift console, choose CLUSTERS, and then choose the cluster that you want to enable logging for.

  2. Choose the Properties tab and then enable auditing by following the instructions from Configuring auditing using the console in the Amazon Redshift documentation.

DBA, Data engineer
Enable logging in the Amazon Redshift cluster parameter group.

You can enable auditing of connection logs, user logs, and user activity logs at the same time by using the AWS Management Console, the Amazon Redshift API reference, or AWS Command Line Interface (AWS CLI).  

For auditing of user activity logs, you must enable the enable_user_activity_logging database parameter. If you only enable the audit logging feature but not the associated parameter, the database audit logs the logging information for the connection and user logs but not for the user activity logs. The enable_user_activity_logging parameter is not enabled by default, but you can enable it by changing it from false to true.

Important: You need to create a new cluster parameter group with the user_activity_logging parameter enabled and attach it to your Amazon Redshift cluster. For more information about this, see Modifying a cluster in the Amazon Redshift documentation.

For more information about this task, see Amazon Redshift parameter groups and Configuring auditing using the console in the Amazon Redshift documentation.

DBA, Data engineer
Configure S3 bucket permissions for Amazon Redshift cluster logging.

When you enable logging, Amazon Redshift collects logging information and uploads it to log files stored in an S3 bucket. You can use an existing S3 bucket or create a new bucket.

Important: Make sure that Amazon Redshift has the required IAM permissions to access the S3 bucket. For more information about this, see Bucket permissions for Amazon Redshift audit logging from Database audit logging in the Amazon Redshift documentation.

DBA, Data engineer
TaskDescriptionSkills required
Create the Athena table and views to query Amazon Redshift audit log data from the S3 bucket.

Open the Amazon Athena console and use the data definition language (DDL) query from the AuditLogging.sql SQL script (attached) to create the table and views for user activity logs, user logs, and connection logs.

For more information and instructions, see the Create tables and run queries tutorial from the Amazon Athena Workshop.

Data engineer
TaskDescriptionSkills required
Create a QuickSight dashboard using Athena as the data source.

Open the Amazon QuickSight console and create a QuickSight dashboard by following the instructions in the Visualize with QuickSight using Athena tutorial from the Amazon Athena Workshop.

DBA, Data engineer

Related resources

Attachments

attachment.zip