CCNA Database Security Questions

To encrypt data at rest, you must enable encryption when creating the DB instance by specifying a KMS key (Option C). RDS automatically encrypts automated backups for encrypted instances, so Option B is unnecessary. To encrypt data in transit, you must enforce SSL/TLS connections by setting the parameter rds.force_ssl to 1 in the DB parameter group (Option E).

Option A is incorrect because client-side encryption is not required when using RDS encryption and SSL; the requirement is to use RDS features. Option D is incorrect because creating an encrypted read replica does not encrypt the original master instance; you need to enable encryption on the master from the start.

The quickest remediation is to modify the snapshot permissions to make it private. This immediately revokes public access. Option A is incorrect because deleting the snapshot would remove the backup entirely, which is unnecessary and could risk data loss if no other backup exists.

Option B is incorrect because copying the snapshot does not change the original's permissions; the original remains public. Option C is incorrect because sharing with a specific account does not revoke public access; it only adds an additional permission. Therefore, modifying the snapshot to private is the direct and fastest fix.

Modifying the DB instance's security group to allow inbound traffic only from the application servers' security group restricts access to only those instances, using the security group as a source for a more dynamic and manageable solution. Option B is incorrect because network ACLs are stateless and apply at the subnet level, not the instance level, and allowing from the application servers' IP range is less flexible and secure than using security group references. Option C is incorrect because enabling encryption at rest protects data at rest but does not control network access.

Option D is incorrect because moving to a private subnet and using a bastion host is unnecessary when the application servers are in the same VPC; a security group rule is simpler and more appropriate.

AWS Backup can be used to manage automated backups of RDS instances and store them in a cross-region S3 bucket. AWS Backup natively supports RDS and allows you to create backup plans that automatically take snapshots and copy them to a different region, storing them in S3. The backups are encrypted using the KMS key, and you can set retention policies for 3 years.

This solution requires minimal administrative effort as AWS Backup automates the entire process. Option B is incorrect because RDS cross-Region backup copy stores backups in the destination region's RDS-managed storage, not in an S3 bucket as required. Option C is incorrect because AWS DMS is designed for migrations, not for ongoing backup management.

Option D is incorrect because manual snapshots require manual intervention and do not provide automated backups.

A KMS key policy that restricts decryption permissions to specific IAM roles ensures only authorized applications can use the client-side encryption keys. Option A is incorrect because storing keys in CloudHSM does not by itself enforce application-level authorization; key policies are still needed. Option B is incorrect because Secrets Manager is for storing secrets, not for managing key permissions; it does not replace KMS key policies.

Option C is incorrect because IAM policies alone cannot deny DynamoDB access based on encryption keys; access control is managed via the key policy and IAM permissions.

Options A and E are correct. Option A: Using a security group to restrict inbound traffic to only required IP addresses limits exposure to the internet. Option E: Placing the DB instance in a private subnet without a public IP address ensures it is not directly accessible from the internet.

Option B is incorrect because disabling encryption at rest reduces security and does not address internet accessibility. Option C is incorrect because using the default VPC with a public subnet and a network ACL that allows all traffic increases exposure. Option D is incorrect because launching the DB instance in a public subnet with a public IP address makes it directly reachable from the internet.

AWS Secrets Manager (option A) is the correct service because it is designed to centrally manage database credentials and can automatically rotate them for Amazon RDS for MySQL instances on a schedule you define. Option B (AWS CloudHSM) provides hardware security modules for cryptographic key storage, not credential management or rotation. Option C (IAM) is used for controlling access to AWS resources and supports IAM database authentication for RDS, but it does not manage database user passwords or provide automatic rotation of those passwords.

Option D (AWS Systems Manager Parameter Store) can store secrets securely, but it does not offer native automatic rotation of RDS database credentials; that functionality requires Secrets Manager.

A and B are correct. VPC security groups (A) act as a virtual firewall to control inbound and outbound traffic to the RDS instance at the network level. DB subnet groups (B) determine which subnets the RDS instance can be deployed in, effectively controlling network access by restricting the IP ranges that can reach the instance.

AWS WAF (C) is a web application firewall for HTTP/HTTPS traffic, not for network-level RDS access. IAM policies (D) control API-level permissions, not network traffic. DB parameter groups (E) manage database engine configuration settings and do not affect network access.

To enable cross-region restores of an encrypted DynamoDB table, you need to have the table's backups available in the destination region. Cross-region restores require point-in-time recovery (PITR) to be enabled on the source table (Option A). Additionally, because the table uses a customer managed KMS key, you must create a multi-Region KMS key (or replicate the key) in the destination region so that DynamoDB can use it to decrypt the backup during restore (Option D).

Option B (global tables) is for live replication, not for backup/restore scenarios. Option C (export to S3 and copy) does not preserve the same encryption because the exported data is not encrypted with the KMS key. Option E (CloudHSM) is not supported for DynamoDB encryption.

Amazon RDS for Oracle supports fine-grained auditing (FGA) to create audit policies on specific columns, and audit logs can be stored in a custom table with the desired retention period. Option B is incorrect because database activity streams capture all database activities and do not filter by specific columns, and they integrate with CloudWatch Logs where retention must be set separately. Option C is incorrect because RDS Enhanced Monitoring is for OS-level metrics, not SQL auditing.

Option D is incorrect because Oracle Audit Vault and Database Firewall are not supported on Amazon RDS for Oracle.

Setting `log_statement = 'all'` captures every SQL statement, including SELECT, and `log_min_duration_statement = 0` ensures all statements are logged regardless of duration. Enabling CloudWatch Logs export then sends these logs to CloudWatch Logs for real-time monitoring. Option B is incorrect because while pgAudit can log all statements, it is not necessary; the built-in PostgreSQL logging with `log_statement = 'all'` meets the requirement without additional extensions.

Option C is incorrect because `log_statement = 'ddl'` only logs data definition language statements, not SELECT queries. Option D is incorrect because `log_min_duration_statement = -1` disables logging of all statements.

Enabling the 'require_secure_transport' parameter in the DB parameter group requires explicitly setting it to '1'. By default, this parameter is '0' (disabled). Even if the parameter group is associated with the RDS instance, if the parameter value is not set to '1', the database will still accept non-SSL connections.

After setting it to '1', the instance must be rebooted for the change to take effect. Option A is incorrect because 'require_secure_transport', when properly configured, rejects non-SSL connections. Option B is incorrect because the MySQL client does not automatically upgrade to SSL; it only uses SSL if explicitly requested or if the client defaults to SSL (which depends on client configuration).

Option D is incorrect because connections from within the same VPC are still subject to the database's SSL enforcement settings.

Using a VPC endpoint for DynamoDB with a policy that restricts access to the specific IAM role ensures that only requests from that role are allowed, and using IMDSv2 with a hop limit prevents credential theft by ensuring that only the intended application process on the EC2 instance can access the credentials. Option B is incorrect because security groups control network traffic but do not enforce IAM role usage. Option C is incorrect because storing AWS access keys on the instance is less secure and exposes credentials to any process.

Option D is incorrect because DynamoDB does not support resource-based policies; IAM roles are authorized via identity-based policies attached to the role.

Modifying the security group associated with the RDS instance to allow inbound traffic only from the application's IP addresses restricts network access at the instance level, preventing unauthorized IP addresses from connecting even if credentials are compromised. Option A (attaching a resource-based policy) does not restrict network access—it controls IAM permissions. Option B (creating a new RDS instance in a VPC with a network ACL) is unnecessarily complex and involves migrating data; the requirement can be met by modifying the existing security group without creating a new instance.

Option D (enabling IAM database authentication) authenticates database users via IAM but does not restrict source IP addresses, so it does not meet the stated requirement.

Pgcrypto is a PostgreSQL extension that provides column-level encryption functions, allowing the application to encrypt and decrypt data transparently at the column level. Option A is incorrect because RDS encryption at rest encrypts the entire database storage, not individual columns. Option B is incorrect because using AWS Lambda would require application modifications and introduce additional latency.

Option C is incorrect because AWS KMS is a key management service, not a direct column-level encryption solution for databases, and using its APIs would require building encryption logic in the application.

DynamoDB Streams can capture TTL deletions as 'REMOVE' events. By processing these events with a Lambda function and logging them to CloudWatch Logs, the security team can audit TTL deletions. On-demand backups reflect the current table state, so items deleted by TTL before the backup is taken will not be included.

Option A is incorrect because disabling TTL and implementing a custom deletion process adds complexity and may not be as efficient. Option C is incorrect because CloudTrail logs the UpdateTimeToLive API call (control plane), not the actual TTL deletions (data plane). Option D is incorrect because CloudTrail data events for DynamoDB capture GetItem, PutItem, etc., but not TTL deletions.

AWS Secrets Manager provides built-in support for automatic rotation of RDS database passwords using a custom or pre-built AWS Lambda rotation function. You can configure the rotation interval to 90 days to meet the requirement. Option A is incorrect because IAM database authentication does not rotate passwords; it uses IAM roles and credentials, not password rotation.

Option B is incorrect because AWS Systems Manager Parameter Store does not have native rotation capabilities for RDS passwords; it requires a custom solution. Option D is incorrect while it describes a manual approach via Lambda and CloudWatch Events, it does not leverage Secrets Manager's automatic rotation feature, which is the recommended and simplest method.

AWS Secrets Manager. Secrets Manager is designed to securely store and manage secrets such as database credentials. It natively supports automatic rotation of credentials for Amazon RDS databases, including PostgreSQL, with a customizable rotation interval (e.g., every 90 days).

Option A (AWS Systems Manager Parameter Store) can store secrets but does not provide built-in automatic rotation for RDS credentials. Option B (AWS CloudHSM) provides hardware security modules for encryption key storage, not for managing database credentials. Option C (IAM roles) allow EC2 instances to assume roles for API access but do not store or rotate database credentials; while IAM database authentication can be used with RDS PostgreSQL, it does not meet the requirement to store and rotate credentials automatically.

Options D and E are correct. To audit SQL statements in an Aurora MySQL DB cluster, you need to enable the Aurora MySQL audit plugin. This is done by setting the `server_audit_logging` parameter to 1 in the DB cluster parameter group (option D).

Then you can configure the DB cluster to publish the audit logs to Amazon CloudWatch Logs (option E) for centralized monitoring and analysis. Option A is incorrect because AWS CloudTrail captures API calls, not SQL statements. Option B is incorrect because Enhanced Monitoring captures OS-level metrics, not SQL queries.

Option C is incorrect because RDS event subscriptions notify about events like DB instance changes, not SQL execution.

To enforce SSL for all connections to an Amazon Aurora MySQL database, you must modify the DB cluster parameter group to set require_secure_transport to ON, which rejects non-SSL connections. Additionally, the application's JDBC connection string must be updated to include SSL parameters (e.g., useSSL=true) so that the application initiates an SSL connection. Option A is incomplete because modifying the parameter group alone does not enforce SSL on existing connections if the application does not use SSL.

Option C is incorrect because security groups do not have an SSL flag; they control network access based on IP and port. Option D is incorrect because IAM roles do not enforce SSL encryption; they handle authentication and authorization.

IAM database authentication for Amazon RDS PostgreSQL requires an encrypted connection to protect the authentication token. Setting the 'rds.force_ssl' parameter to 1 enforces TLS/SSL connections between the client and the database, which is a prerequisite for IAM authentication. Option A is incorrect because the port (default 5432) does not need to change for IAM.

Option C is incorrect because the DB instance can be private within a VPC; IAM authentication works over private or public connections as long as TLS is enforced. Option D is incorrect because the master username is not changed; IAM authentication uses database users that are mapped to IAM identities.

Options A, C, and E are correct. Option A: Configuring the RDS instance to require SSL/TLS encrypts data in transit, meeting the encryption-in-transit requirement. Option C: Enabling encryption at rest for the RDS instance at launch time allows use of AWS KMS for encryption, satisfying the at-rest encryption requirement.

Option E: Creating a customer managed key (CMK) in AWS KMS enables the company to manage the encryption key, meeting the requirement for a key managed by the company. Option B is incorrect because setting rds.force_ssl to 1 in the DB parameter group is a step within configuring SSL/TLS, but the primary action is option A; moreover, the correct method to enforce SSL for Oracle RDS involves using the option group with the SSL option, not just the parameter group. Option D is incorrect because Transparent Data Encryption (TDE) using CloudHSM is not required; the at-rest encryption requirement is already fulfilled by enabling RDS encryption with a CMK (options C and E), and TDE with CloudHSM adds unnecessary complexity and potential licensing issues.

A KMS key policy can use the 'kms:ViaService' condition key to restrict usage of the key to requests that originate from DynamoDB (dynamodb.amazonaws.com). This ensures the key is used only for DynamoDB and no other AWS service. Option A is incorrect because IAM policies alone cannot restrict key usage if the key policy allows all principals; the key policy must explicitly enforce the restriction.

Option B is incorrect because AWS Organizations SCPs do not control KMS key permissions; they control permissions for IAM entities. Option D is incorrect because IAM policies cannot prevent other services from using the key if the key policy allows them; the key policy itself must include the restriction.

Amazon RDS for PostgreSQL can publish database audit logs to Amazon CloudWatch Logs. CloudWatch Logs provides immutability through IAM policies that prevent log modification and deletion, combined with log group encryption using AWS KMS. Option D is correct.

Option A (AWS KMS) is wrong because KMS manages encryption keys, not log storage. Option B (AWS CloudTrail) is wrong because CloudTrail records API activity, not database audit logs. Option C (Amazon S3) can store logs, but enabling immutability requires additional configuration like S3 Object Lock, making CloudWatch Logs the more straightforward and recommended service for this requirement.

CloudWatch Logs can be used to monitor CloudTrail logs and trigger alerts on specific patterns like unauthorized access. CloudTrail logs all DynamoDB API calls, including queries that return no results. By creating a metric filter on the CloudTrail log group for unauthorized access patterns (e.g., AccessDenied exceptions) and setting a CloudWatch alarm, the company can get immediate alerts.

Option A (Amazon Inspector) is for vulnerability assessment, not access logging. Option B (DAX) is a caching layer and does not log all read requests; it only caches and can be configured for logging, but it does not replace CloudTrail for auditing. Option D (VPC Flow Logs) captures network traffic, not API call details.

To encrypt an Amazon RDS for Oracle DB instance at rest using AWS KMS, you must enable encryption when creating the DB instance and specify a KMS key. Encryption cannot be added to an existing unencrypted DB instance directly; you must take a snapshot, create an encrypted copy, and restore it. Option A is incorrect because modifying an existing instance does not allow enabling encryption.

Option B is incorrect because SSL/TLS encrypts data in transit, not at rest. Option C is incorrect because while CloudHSM can be used, AWS KMS is the simpler and more common approach, and the question specifically mentions AWS KMS.

To securely grant an AWS Lambda function access to DynamoDB without using long-term credentials, you should create an IAM role that grants the necessary DynamoDB permissions (option B) and then attach that IAM role to the Lambda function's execution role (option E). This allows Lambda to obtain temporary credentials via the IAM role, eliminating the need for hardcoded credentials. Option A is incorrect because hardcoding credentials in environment variables is not secure and uses long-term credentials.

Option C is incorrect because the function does not need internet access for authentication; IAM roles provide temporary credentials internally. Option D is incorrect because while Secrets Manager can store credentials, it still requires managing secrets and may not align with the mandate to avoid long-term credentials; IAM roles are the preferred method.

Options A and E are correct. AWS Secrets Manager is specifically designed to centrally manage secrets, including database credentials, with features like automatic rotation. AWS Systems Manager Parameter Store can also securely store secrets (e.g., using SecureString parameters).

Option B (AWS CloudFormation) is incorrect because it is an infrastructure-as-code service, not a secrets manager. Option C (Amazon S3) is incorrect because it is an object storage service, not designed for secret storage. Option D (AWS IAM) is incorrect because it manages access permissions and identities, not secrets.

Setting the 'require_secure_transport' parameter to 1 in the DB parameter group forces clients to use TLS/SSL for connections, ensuring encryption in transit. Option A is incorrect because enabling encryption at rest (RDS encryption) secures data on disk but does not enforce encryption of data in transit. Option B is incorrect because storing passwords in AWS Secrets Manager manages credentials but does not enforce encrypted connections.

Option C is incorrect because modifying a network ACL to allow traffic on port 3306 controls network access but does not enforce encryption of the data transmitted over that port.

Enabling encryption at rest using AWS KMS during database creation is the simplest and most secure approach. Option A is correct. Option B is wrong because EBS encryption only protects the underlying storage, not the Aurora database engine layer, and it adds operational overhead.

Option C is wrong because you cannot encrypt an existing unencrypted Aurora cluster directly; you must perform a manual snapshot and restore to a new encrypted cluster, which increases complexity. Option D is wrong because client-side encryption requires managing encryption keys in the application, increasing complexity and operational overhead.

The simplest way to achieve annual key rotation for a customer-managed AWS KMS key used with DynamoDB is to enable automatic key rotation on the existing key. AWS KMS supports automatic rotation of customer-managed keys once per year when enabled, which can be done through the KMS console or API without any manual intervention or table modification. Option A is incorrect because creating a new key and updating the DynamoDB table requires manual effort and is more complex than enabling automatic rotation.

Option B is incorrect because deleting and recreating the key is disruptive and not simpler. Option D is incorrect because importing new key material does not provide automatic rotation and is not the simplest method.

For IAM database authentication to work, the RDS instance must have the 'IAM DB authentication' setting enabled. If it is not enabled, the authentication token generated by `generate-db-auth-token` will be rejected with an 'Access denied' error. Option A is incorrect because RDS does not use resource-based policies; IAM policies are attached to users/roles.

Option B is incorrect because SSL is required for IAM auth, but a missing SSL connection would result in a different error (e.g., 'SSL required'). Option C is incorrect because network issues would typically cause a timeout or connection refused, not an authentication error.

To achieve an RPO of 5 minutes and RTO of 1 hour across regions, you need automated backups with a 5-minute interval (D) so that transaction logs are backed up frequently. A cross-Region read replica (E) can be promoted quickly in the secondary region to meet the RTO. Since the database uses a customer-managed KMS key, you must create a cross-Region KMS key replica (C) in the secondary region so that the replica can be encrypted.

Multi-AZ (B) only provides high availability within a single region and does not support cross-region disaster recovery, so it does not help meet these RPO/RTO requirements.

The most secure approach is to grant the EC2 IAM role permission to read the secret using an IAM policy attached to the role, and then have the application call the Secrets Manager API to retrieve the secret at runtime. This avoids storing secrets in code or configuration files. Option B is wrong because storing the secret in application code is insecure.

Option C is wrong because resource-based policies are not attached to EC2 instances; IAM roles are the correct mechanism. Option D is wrong because storing secrets in an S3 bucket introduces additional complexity and potential exposure; using Secrets Manager with IAM roles is more secure.