Database Specialty - Monitoring, Encryption, Security and Auditing

Question 1

AWS KMS (Key Management Service)

Answer 1

• Anytime you hear “encryption” for an AWS service, it’s most likely KMS
• Easy way to control access to your data, AWS manages keys for us
• Fully integrated with IAM for authorization
• Seamlessly integrated into:
  
  • Amazon EBS: encrypt volumes
  • Amazon S3: Server side encryption of objects
  • Amazon Redshift: encryption of data
  • Amazon RDS: encryption of data
  • Amazon SSM: Parameter store
  • Etc…
  • But you can also use the CLI / SDK

Question 2

Cross Region Snapshots for Redshift Snapshot Copy Grant

Answer 2

• https://docs.aws.amazon.com/redshift/latest/mgmt/working-with-snapshots.html#cross- region-snapshot-copy
• In the destination AWS Region, create a snapshot copy grant by doing the following:
  
  • Create a KMS key in the destination region
  • Specify a name for the snapshot copy grant. This name must be unique in that AWS
    Region for your AWS account.
  • Specify the AWS KMS key ID for which you are creating the grant
• In the source AWS Region, enable copying of snapshots and specify the name of the
  snapshot copy grant that you created in the destination AWS Region

Question 3

Encrypting an un-encrypted RDS database

Answer 3

1. Take a snapshot of the RDS database
   
   • The snapshot will be un-encrypted
2. Create an encrypted copy of the snapshot
   
   • Using the KMS key of your choosing
3. Restore a database from the encrypted snapshot
   
   • The database will be encrypted !

Question 4

Database Logging – RDS

Answer 4

• Engine log files available (Oracle, MSSQL, PostgreSQL, MySQL, MariaDB)
  
  • List log files: aws rds describe-db-log-files
  • Download log files: aws rds download-db-log-file-portion
• Normal log retention in RDS up to 7 days (configurable per DB)
• Logs can be published into CloudWatch Logs
  
  • you can perform real-time analysis of the log data
  • store the data in highly durable storage (retention period configurable / infinite)
  • From CloudWatch Logs you can export to S3
  • Must create a custom parameter group (can’’t modify default)

Question 5

Database Logging – Aurora

Answer 5

• Engine log files available (PostgreSQL, MySQL)
  
  • List log files: aws rds describe-db-log-files
  • Download log files: aws rds download-db-log-file-portion
• Normal log retention in RDS up to 7 days (configurable per DB)
• Logs can be published into CloudWatch Logs
  
  • you can perform real-time analysis of the log data
  • store the data in highly durable storage (retention period configurable / infinite)
  • From CloudWatch Logs you can export to S3
  • Not available for transaction logs

Question 6

Database Logging – Redshift

Answer 6

• Amazon Redshift logs information about connections and user activities in your database (for troubleshooting + audit)
  
  • Connection log — logs authentication attempts, and connections and
    disconnections.
  • User log — logs information about changes to database user definitions.
  • User activity log — logs each query before it is run on the database.
• The logs are stored in Amazon S3 buckets (must be enabled)
  
  • Set lifecycle policies accordingly
  • Ensure S3 bucket policies allows for Redshift to write to the bucket

Question 7

Database Logging – DynamoDB

Answer 7

• All API calls to DynamoDB are logged into CloudTrail
• From CloudTrail you can send to :
  
  • CloudWatch Logs
  • Amazon S3 buckets
• There are no ”log files” in DynamoDB, it’s a proprietary technology

Question 8

Database Logging – DocumentDB

Answer 8

• You can audit DocumentDB Events (must opt in)
• Examples of logged events
  
  • successful and failed authentication attempts
  • dropping a collection in a database
  • creating an index
  • Data Definition Language (DDL)
• Logs are sent into CloudWatch Logs
• To opt in, set the audit_logs parameter to enabled (parameter group)

Question 9

Database Logging - Other

Answer 9

• ElastiCache: no logs to access yet’
• Neptune:
  
  • publish audit log data to a log group in Amazon CloudWatch Logs
  • Use the neptune_enable_audit_log parameter to enable (1) or disable (0)
• QLDB:
  
  • No logs are accessible
• DMS:
  
  • Can set task-logging level to LOGGER_SEVERITY_DETAILED_DEBUG (most detailed log)

Question 10

AWS Secrets Manager

Answer 10

• Newer service, meant for storing secrets
• Capability to force rotation of secrets every X days
• Uses a Lambda function
  
  • Generated by AWS for integrated DB types (Amazon RDS, Redshift, DocumentDB)
  • For other secrets, you need to code a Lambda function to generate the next secret
• Secrets are encrypted using KMS
• Mostly meant for RDS integration

Question 11

Active Directory with RDS SQL Server

Answer 11

• Use AWS Managed Microsoft AD
  
  • If you need to join your on-premises AD, create a trust relationship
• Create an IAM role with AmazonRDSDirectoryServiceAccess to access AWS Managed Microsoft AD
• Create and configure users and groups in the AWS Managed
  Microsoft AD directory
• Create a new or modify an existing Amazon RDS instance with
  
  • A reference to the IAM role that has access to the AD
  • A reference to the AWS Managed Microsoft AD
  • No need to stop the DB if you are modifying it
• Ensure security groups allow communication between RDS and
  Microsoft AD
• Log in to the DB using the Master User Credentials and create logins