CCNA Database Security Questions

The most secure method to allow an EC2 instance to connect to an RDS for MySQL DB instance is to use IAM roles for EC2 combined with IAM database authentication. By attaching an IAM role to the EC2 instance that has permissions to generate RDS authentication tokens, and configuring the DB instance to accept authentication tokens instead of passwords, the application can obtain temporary credentials without hardcoding or storing any secrets. Option A is insecure because storing credentials in application code exposes them to anyone with access to the code.

Option B is insecure because opening the security group to all traffic (0.0.0.0/0) exposes the database to the internet. Option C is insecure because storing credentials in S3 and downloading them at runtime still involves storing static credentials and adds potential risk from S3 access misconfiguration.

Options A, B, and D are correct. Option A: Enabling CloudTrail logging and creating a metric filter for Scan API calls allows the security team to detect unauthorized scan attempts in real time and trigger alerts. Option B: Using fine-grained access control with IAM conditions (e.g., 'dynamodb:LeadingKeys' or 'dynamodb:Attributes') restricts access to specific items or attributes, preventing unauthorized scans from retrieving sensitive data.

Option D: Creating an IAM policy that denies the 'Scan' action for unauthorized users using a condition key (e.g., 'aws:SourceIp' or custom tag) provides a preventive control to block unauthorized scans at the API level. Option C is incorrect because disabling the Scan operation on the DynamoDB table would break legitimate use cases that require scanning (e.g., admin queries, reporting) and is not a recommended control. Option E is incorrect because VPC Flow Logs capture network traffic to DynamoDB, not the DynamoDB API operations themselves, so they cannot detect scans.

The security team's concern likely stems from the possibility that the KMS key specified for encryption might have been disabled or deleted. Even though 'StorageEncrypted' is true, if the KMS key is not available, the database may become inaccessible or the encryption may not be effective. Option A is incorrect because using a default AWS managed key still provides encryption; the security team's concern is about encryption itself, not key type.

Option C is incorrect because the CLI output clearly shows 'StorageEncrypted' is true, so the field is not false. Option D is incorrect because there is no indication that a CloudHSM key is involved, and using a KMS key is the standard approach for RDS encryption.

Amazon Aurora MySQL supports Database Activity Streams, which capture detailed audit information including login attempts (both successful and failed). These streams can be sent to Amazon CloudWatch Logs for monitoring and then exported to Amazon S3. To ensure immutability and restricted access, S3 Object Lock can be enabled, preventing any updates or deletions, and S3 bucket policies can limit access to only the security team.

Option A is incorrect because VPC Flow Logs capture network-level traffic, not database login events. Option B is incorrect because AWS CloudTrail logs API calls made to AWS services, not database-level login attempts. Option D is incorrect because the standard MySQL audit log plugin stores logs in a table that can be modified, lacking immutability, and does not natively integrate with S3 Object Lock for long-term immutable storage.

RDS for MySQL supports audit logs that can capture authentication failures and queries. By setting the 'audit_log_enabled' parameter to 1 in the DB parameter group and exporting logs to CloudWatch Logs, you meet the requirement to log all access including failed authentication attempts. Option B is incorrect because Enhanced Monitoring provides OS-level metrics, not query logs.

Option C is incorrect because AWS CloudTrail logs control plane API calls for RDS, not data plane activities like database queries. Option D is incorrect because S3 server access logs capture requests made to S3 buckets, not database connections.

For Amazon RDS for SQL Server, encryption at rest can be achieved using two methods: enabling RDS encryption at rest with AWS KMS (option E) or enabling Transparent Data Encryption (TDE) natively within SQL Server (option B). Option A is incorrect because EBS encryption is automatically handled by RDS when you enable encryption at rest via KMS, but you cannot enable EBS encryption directly on the underlying volumes. Option C is incorrect because AWS CloudHSM can be used for key management but is not required for RDS encryption at rest.

Option D is incorrect because SSL/TLS encrypts data in transit, not at rest.

DynamoDB does not support changing the KMS key or SSE configuration after table creation. To use the AWS managed key instead of a customer managed key, you must create a new table with SSE-KMS enabled using the AWS managed key (alias 'aws/dynamodb'), export the existing table data (via DynamoDB export to S3 or using a scan/write process), and import it into the new table. Option B is incorrect because a customer managed KMS key cannot be converted into an AWS managed key; they are distinct key types.

Option C is incorrect because you cannot modify the SSE configuration of an existing DynamoDB table; encryption settings are immutable after creation. Option D is incorrect because the UpdateTable API does not allow changing the KMS key used for encryption; it only supports updating other table settings like billing mode or provisioned throughput.

The policy condition 'kms:ViaService' restricts KMS operations to only when they are made through S3. However, the condition also includes 'kms:CallerAccount' which is redundant but not harmful. The error likely occurs because the EC2 instance does not have the correct IAM role, or the policy is missing 'kms:Decrypt' for the specific ciphertext.

But the most common mistake is that the application is not using the S3 service to decrypt; if the application reads the encrypted object and then calls KMS directly to decrypt, the 'kms:ViaService' condition will fail because the call is not via S3. Option B addresses this. Option A is wrong because the condition is present.

Option C is wrong because the role is not explicitly denied. Option D is wrong because the policy allows KMS actions.

When a failover occurs, the RDS Database Activity Stream session is tied to the original primary instance. The Lambda function must re-establish the stream session to the new primary instance to continue processing events. If it does not, events during failover are lost.

Option A is incorrect because the security group controls network access to the RDS endpoint, which remains the same after failover; connectivity is not the issue. Option C is incorrect because parameter group settings apply to both primary and standby instances; the activity stream setting is not parameter group-dependent. Option D is incorrect because the KMS key used for encryption is available in the same region; region unavailability is not a factor in Multi-AZ deployments within a single region.

Amazon RDS for PostgreSQL does not support Transparent Data Encryption (TDE). TDE is only available for Oracle and SQL Server. The most efficient way to meet the requirements is to use RDS encryption with a customer-managed key (CMK) in AWS KMS, which encrypts the underlying storage and automated backups.

The security team can manage the CMK via CloudHSM by using a KMS custom key store, thus satisfying the key management requirement. Option A is incorrect because EBS encryption is not directly applicable to RDS instances; RDS encryption uses KMS. Option B is incorrect because TDE is not supported for PostgreSQL.

Option D is incorrect because client-side encryption introduces application changes and does not ensure encryption at rest within the database.

Amazon Redshift supports encryption at rest using AWS KMS with automatic key rotation. When you enable encryption for a Redshift cluster, you can choose a KMS key with automatic rotation enabled, which meets the requirement of rotating the encryption key automatically every year.

Option A is incorrect because manual rotation using a Lambda function is not the recommended approach when automatic rotation is available. Redshift does not support custom manual rotation; it relies on KMS for key management.

Option C is incorrect because Redshift does not support CloudHSM for encryption at rest; it uses AWS KMS. CloudHSM does not provide automatic rotation as a built-in feature in the same way KMS does.

Option D is incorrect because Redshift does not store cluster data in an S3 bucket; S3 server-side encryption is not applicable to the Redshift cluster itself. Redshift uses its own encryption mechanism integrated with KMS.

To encrypt an unencrypted read replica that is sourced from an encrypted DB instance, you cannot directly enable encryption on an existing read replica or modify it. Instead, you must delete the unencrypted read replica and create a new read replica from the encrypted source, enabling encryption on the new replica. This is because encryption at rest can only be enabled during instance creation, and read replicas inherit encryption settings from the source at creation time.

Option A is incorrect because the StorageEncrypted parameter cannot be modified on an existing instance. Option C is incorrect because enabling encryption on the source does not encrypt existing replicas; it only affects new replicas. Option D is incorrect because creating a snapshot of the unencrypted replica and restoring it as a new encrypted instance is not the standard procedure for encrypting a read replica; the correct approach is to recreate the read replica from the encrypted source with encryption enabled.

To enable encryption on an existing unencrypted RDS MySQL DB instance, you must take a snapshot of the instance, copy the snapshot with encryption enabled using the desired KMS key, and then restore the encrypted snapshot to a new DB instance. This process results in some downtime but is the standard method. Option A is incorrect because you cannot create an encrypted read replica from an unencrypted source instance; encryption on a read replica requires the source to be encrypted.

Option B is incorrect because you cannot enable encryption on an existing unencrypted DB instance by modifying it; encryption can only be enabled at creation time or by restoring an encrypted snapshot. Option D is incorrect because you cannot restore an encrypted snapshot to the same DB instance ID; you must restore to a new instance, then update the application endpoint.

Using an AWS Organizations service control policy (SCP) to deny creation of unencrypted RDS instances is the most efficient way to enforce encryption at rest for all new RDS DB instances across multiple accounts. SCPs are applied at the organization, organizational unit, or account level and cannot be overridden by users, ensuring consistent enforcement. Option A is wrong because IAM policies with condition keys only affect specified users or roles, not service-linked roles or resources, and can be bypassed if users have permissions to modify policies.

Option B is wrong because there is no 'encryption at rest' default setting in the RDS console; encryption must be enabled per instance or via other mechanisms. Option D is wrong because CloudFormation StackSets require manual creation and maintenance of templates, and do not enforce encryption if users launch instances outside the stack.

Configuring the Aurora cluster's security group to allow inbound traffic from the EC2 instances' security group is the correct approach because security groups can reference other security groups as a source, enabling dynamic, instance-level access control without managing individual IP addresses. Option A is incorrect because IAM database authentication manages user authentication at the database level, not network access. Option B is incorrect because the DB subnet group defines the subnets where the Aurora cluster can be placed, not traffic filtering rules.

Option C is incorrect because network ACLs are stateless and operate at the subnet boundary, making them less granular and harder to manage for instance-specific access compared to security group references.

Enabling audit logs for the RDS for SQL Server instance captures detailed SQL statement execution, including the users and timestamps. Sending these logs to CloudWatch Logs allows for easy searching and alerting. Option B is correct because it directly addresses the need to identify the user who executed a specific SQL statement.

Option A is incorrect because AWS CloudTrail logs API actions (e.g., creating or modifying RDS instances), not SQL queries. Option C is incorrect because VPC Flow Logs capture network traffic metadata, not database-level activity. Option D is incorrect because AWS Trusted Advisor provides best-practice checks and does not offer fine-grained auditing.

A security group associated with the RDS instance can be configured to restrict inbound traffic to specific IP addresses, even if the instance is publicly accessible. Option A is incorrect because a VPC endpoint is used for private connectivity from within a VPC, not for restricting public access. Option B is incorrect because IAM policies control permissions for API actions, not network-level access.

Option D is incorrect because network ACLs are stateless and applied at the subnet level, and while they can filter IP traffic, security groups are the more appropriate and granular control for individual RDS instances.

The scenario states that the user cannot modify any RDS DB instance even when MFA is enabled. This suggests that the IAM policy likely includes a condition requiring MFA or a specific resource prefix (e.g., 'prod-*' or 'dev-*'). If MFA is not actually enabled on the user's account, or if the instance name does not match the allowed prefixes, the condition would fail, denying all ModifyDBInstance actions.

Option A is incorrect because EC2 permissions are irrelevant to RDS. Option B is incorrect because the absence of a Deny statement does not cause failure; the issue is an Allow condition not being met. Option D is incorrect because the console versus API distinction does not explain the inability to modify any instance.

IAM database authentication for RDS PostgreSQL allows the application to connect using an IAM user or role without storing any database credentials. The authentication token is generated by the AWS SDK and is valid for 15 minutes. This eliminates the need to store credentials in the application code.

Option A is incorrect because a hardcoded password is not secure. Option B (Secrets Manager) and Option D (Systems Manager Parameter Store SecureString) both require the application to retrieve a secret at runtime, which still introduces credential management overhead and potential exposure. IAM authentication is the most secure option as it removes static credentials entirely.

Setting the parameter 'rds.force_ssl' to 1 in the RDS for PostgreSQL custom parameter group forces all connections to use SSL/TLS, ensuring encryption in transit. Option A is incorrect because IAM roles provide authentication, not transport encryption. Option B is incorrect because changing the port does not encrypt data.

Option C is incorrect because setting 'ssl' to off disables encryption. Option E is incorrect because modifying a security group to allow inbound traffic on port 5432 does not enforce encryption; the '--ssl' option is a client-side parameter, not enforceable via security groups.

DynamoDB supports encryption at rest with AWS KMS customer-managed CMKs. Automatic key rotation every 12 months is a feature of KMS for CMKs. CloudTrail logs KMS API calls for auditing.

Option A is incorrect because SSE-S3 is for S3, not DynamoDB. Option B is incorrect because CloudHSM does not provide automatic key rotation every 12 months and requires application-side encryption. Option C is incorrect because the default DynamoDB encryption uses an AWS owned key, which is not customer-managed and rotates automatically every 3 years, not 12 months.

Option D is correct: it uses a customer-managed CMK with yearly rotation and CloudTrail for auditing.

To encrypt data in transit for new connections to an Amazon RDS for MySQL DB instance, you must modify the DB instance to require SSL/TLS connections. This forces all connections to use SSL/TLS, encrypting the data transmitted between the application and the database. After enabling this, the application connection string must be updated to use SSL (e.g., by adding ssl=true or equivalent).

The other options do not encrypt data in transit: VPC peering (B) does not encrypt traffic; encryption at rest using AWS KMS (C) only protects stored data; and IAM database authentication (D) manages authentication but does not encrypt the connection itself.

The correct actions to enforce SSL for all connections are A and E. Option A: Setting 'rds.force_ssl' to '1' in the DB parameter group forces the RDS PostgreSQL instance to require SSL for all incoming connections at the server level. Option E: Modifying the 'pg_hba.conf' file to require SSL for all users via 'hostssl' entries ensures that only SSL connections are allowed, complementing the server-side enforcement.

Option C is incorrect because associating a custom SSL certificate is not required for enforcing SSL; AWS RDS provides a valid certificate. Option B is a client-side configuration that does not enforce server-side SSL. Option D is client-side and not a server-side enforcement action.

Options A and E together provide robust server-side enforcement.

You must modify the VPC security group to allow inbound traffic on the database port from the specific CIDR, and the DB instance must be configured as publicly accessible with that security group attached. Option B is wrong because the DB subnet group determines which subnets the instance can be deployed in, not inbound access restrictions. Option C is wrong because network ACLs operate at the subnet level and are less granular; security groups are the appropriate mechanism for RDS access control.

Option D is wrong because the rds.force_ssl parameter enforces encrypted connections but does not restrict by source IP or CIDR.

Amazon RDS Database Activity Streams (Option A) is the correct service to capture and store audit logs of SQL statements, including SELECT queries, on an Aurora MySQL DB cluster. It streams database activity to Amazon CloudWatch Logs and Amazon Kinesis Data Streams for further analysis and storage. Option B (Amazon RDS Enhanced Monitoring) provides OS-level metrics, not SQL statements.

Option C (Amazon CloudWatch Logs) can store logs but does not capture SQL statements directly; it requires a source like Database Activity Streams. Option D (Amazon RDS Performance Insights) monitors database performance, not SQL audit logs.

Options B, D, and E are correct. To encrypt data at rest with a customer-managed KMS key, you must specify the KMS key during DB instance creation (B). Multi-AZ with encryption enabled (D) ensures both primary and standby instances are encrypted.

Encryption at rest must be enabled when creating the DB instance (E); it cannot be added later. Option A is wrong because a separate KMS key is not required—the same key encrypts both instances. Option C is wrong because Transparent Data Encryption (TDE) is a SQL Server feature for encrypting the database files, but RDS handles encryption at the storage layer using KMS, not TDE.

TDE is not applicable when using RDS encryption with KMS.

Amazon Redshift supports encryption at rest using a KMS key. You can enable automatic key rotation on a customer-managed KMS key. Option A is wrong because launching without encryption and enabling later using CloudHSM is not supported; Redshift does not support enabling encryption after launch without reloading data, and CloudHSM requires manual key rotation.

Option B is wrong because Redshift does not use S3-managed keys for encryption; you must use a KMS key or HSM. Option C is correct because it meets the requirements. Option D is wrong because using an HSM for key storage does not provide automatic key rotation; you would need to rotate the key manually.

A KMS key policy can grant the kms:Decrypt permission to a specific IAM role, ensuring only that role can decrypt the backups. Option A is incorrect because an S3 bucket policy cannot grant kms:Decrypt; KMS permissions are controlled via KMS key policies or IAM policies. Option B is incorrect because s3:GetObject alone does not enable decryption of SSE-KMS encrypted objects; decryption also requires kms:Decrypt.

Option C is incorrect because an IAM policy attached to the role that grants kms:Decrypt is not sufficient unless the KMS key policy also allows the role to use the key; the key policy must explicitly grant permission to the role or the root account.

Use Oracle Transparent Data Encryption (TDE) with CloudHSM as the key store. RDS for Oracle supports TDE, which allows encryption at rest using keys stored in CloudHSM. Option A is incorrect because RDS encryption at rest uses AWS KMS, and while KMS can use a CloudHSM key as a custom key store, the question specifies the key must be stored in CloudHSM directly, which is achieved via TDE integration.

Option B is incorrect because RDS does not support custom file system encryption; encryption at rest is managed at the database or instance level. Option C is incorrect because SSL/TLS provides encryption in transit, not at rest.

Oracle Transparent Data Encryption (TDE) with AWS CloudHSM as the key store enables transparent encryption of PII columns and stores the encryption keys in CloudHSM, meeting the security requirements. Option A is wrong because Amazon RDS encryption at rest uses AWS KMS, not CloudHSM, and it encrypts the entire database instance, not specific columns. Option B is wrong because modifying an Oracle database to use AWS KMS for column-level encryption is not supported natively in RDS; Oracle TDE is required for transparent column encryption.

Option C is wrong because Oracle Data Pump is an export/import utility, not a column-level encryption solution; it does not provide transparent encryption of PII columns in the live database.

Enabling automatic key rotation for the KMS key ensures that encryption keys are rotated regularly, meeting key control requirements. Option B is correct because using a customer-managed KMS key allows the company to control the encryption keys themselves. Option E is correct because Amazon Redshift requires encryption to be enabled at cluster creation time, and using a KMS key accomplishes this.

Option C is incorrect because AWS CloudHSM is not necessary for this requirement; KMS provides sufficient key management. Option D is incorrect because encryption cannot be enabled on an existing Redshift cluster; it must be enabled at creation.