Security best practices for Amazon RDS for MySQL and MariaDB instances

At AWS, cloud security is the highest priority. The implementation of security and compliance is a shared responsibility. While AWS is responsible for the security of the cloud, it provides multiple features to help you manage the security of your databases in the cloud. For example, you can integrate services like AWS Identity and Access Management (IAM) and AWS Key Management Service (AWS KMS) into Amazon Relational Database Service (Amazon RDS). Additionally, the different database engines in Amazon RDS come with different security enhancement options.

In this post, we discuss the different best practices you can follow in order to run Amazon RDS for MySQL and Amazon RDS for MariaDB databases securely. We discuss the best practices at network, database instance, and DB engine (MySQL and MariaDB) levels.

Network-level security

In this section, we discuss best practices for security at the network level.

Database subnet groups

In Amazon RDS, all recent engine versions and instance classes are only supported within a Virtual Private Cloud (VPC). To launch instances in a VPC, we need to create a database subnet group that contains two or more VPC subnet groups. Each subnet is a range of IP addresses in a particular VPC. The RDS instance is launched in the subnets specified in the database subnet group.

VPC subnets can be private or public. When a subnet is associated with a route table, which has a route to the internet (via an internet or virtual gateway), it’s considered a public subnet. Otherwise, it’s considered a private subnet.

When creating a database subnet group, it’s important to consider whether you’re using either all public subnets or private subnets based on the use case and architecture of the respective application. Usage of public and private subnet groups can cause unexpected issues. For example, when you launch an RDS instance in Multi-AZ, it chooses two subnets from the database subnet group to launch primary and secondary instances and configure the synchronous replication. If one of these instances (secondary) is launched in a private subnet and the primary is launched in a public subnet, after a Multi-AZ failover the RDS instance becomes inaccessible to the public network because the promoted secondary (new primary) instance was launched in the private subnet.

We recommend launching your RDS instances in private subnets and allow access only from the application servers with in the same VPC or a different VPC. To access RDS instances across different VPCs without a public route, consider using VPC peering or AWS PrivateLink accordingly. For more information about using AWS PrivateLink, refer to Access Amazon RDS across VPCs using AWS PrivateLink and Network Load Balancer.

Disable Public accessibility to your RDS instances

In addition to disabling public access at the subnet level, Amazon RDS provides a feature to enable or disable public access to the respective instances. Even if an instance is launched in a public subnet for any reason, it’s still possible to disable internet access to the instance by disabling public access. When you disable public access on the RDS instance, RDS end point resolve to private IP address only and accessible to the instances in the same VPC (or VPC connected via other means like VPC peering).

If all your applications servers are in the same VPC as your RDS instance, consider disabling public access to the instance. Furthermore, to help developers or admins who need access to the RDS instance to perform required tasks, create bastion instances in the same VPC as the RDS instances. These bastion instances have public access (with proper security group rules), and users (developers and admins) can connect to the bastion instances via the internet and connect to the RDS instance from the respective bastion instances. For more information refer Hiding a DB instance in a VPC from the internet.

Configure Network access control lists

Network access control lists (ACLs) provide an additional layer of security. They act as a firewall at the subnet level. These can be used to allow or block incoming or outgoing traffic on the resources launched with the respective subnets.

For example, below ACL configuration provide access to instances in my public subnet group with VPC CIDR (10.0.1.0/28) to communicate to database on port 3306 in the inbound rules. The network ACL outbound rules configured to allow traffic on the ephemeral port ranges that are used by MySQL clients to communicate to the database.

In addition, set up the rules to allow traffic on any additional ports required by additional features enabled on your instance like Memcached for RDS for MySQL. Further, it is important to group all the subnets with similar configurations into required database subnet group used for launching the RDS instances. For details information on Network ACLs, refer to Control traffic to subnets using Network ACLs.

Configure Security groups

Security groups are associated with individual instances. These can be configured to provide inbound and outbound access to the instances. Consider configuring the appropriate inbound rules on the security group attached to the RDS instance to allow access from the application servers.

In addition to having least privilege to AWS APIs, it’s very important to perform regular audits on the security rules to ensure that public access to any instance isn’t enabled due to human or automation errors. In these cases, it’s recommended to use AWS Config to create rules to check for any changes and perform remediation automatically. For example, we can create an AWS Config rule to identify any RDS instances with public access enabled and perform remediation using managed rules (rds-instance-public-access-check). In a similar way, we can utilize existing managed rules or create custom rules to perform required checks and remediation.

Below is sample security group attached to one of the RDS instances that allows access from VPC subnet group with CIDR 10.0.1.0/28 and from default security group association. Providing access to another security group allows traffic based on the private IP addresses of the resources associated with the specified security group.

For more information related to security group configurations, refer to Work with security groups in the AWS documentation.

Database instance-level security

In this section, we discuss best practices for security at the database level.

Run database on non-default port

The RDS for MySQL and MariaDB default port is 3306. While launching the instance, we can configure the instance using a non-default port. For additional security, it’s recommended to run the instance on a non-default port and configure network rules (ACLs, security groups) accordingly.

Enable encryption at rest for RDS instances

RDS encrypted instances provide an additional protection to data by securing your data at the underlying storage level. You can use AWS KMS keys for encrypting storage of your Amazon RDS instances to increase data protection of database instances deployed in the cloud to fulfill applicable compliance requirements for encryption at rest.

When you enable encryption at rest on RDS instances, all logs and snapshots are encrypted. Read replicas created from the encrypted instance are also encrypted. Amazon RDS uses AWS KMS to encrypt data. You can use default or custom KMS keys to implement the encryption. Amazon RDS provides a straightforward one-click option to enable encryption for new database instances.

Note that the snapshots encrypted using default KMS keys can’t be shared with different accounts or copied across Regions. In these cases, the snapshot needs to be encrypted using custom KMS keys. As a best practice, consider using custom KMS keys for encryption.

It’s recommended to enable encryption at rest for your RDS instances. For detailed instructions, refer to How can I encrypt an unencrypted Amazon RDS DB instance for MySQL or MariaDB with minimal downtime?

Database authentications

Amazon RDS for MySQL and MariaDB provides multiple authentication methods to authenticate database users’ access to the engine:

• DB engine native authentication – This is regular password-based authentication. Note that the Amazon RDS primary user created as part of database instance creation is created with this authentication method only. MySQL and MariaDB supports different plugins to provide security for the passwords created for respective database users using the password authentication method, which we discuss later in this post.
• IAM database authentication – You can use this feature in addition to the password authentication method. With this method, you don’t need to use a password to connect to the RDS instance. Instead, you use an authentication token, which is a string of characters that Amazon RDS generates on request. Authentication tokens are generated using AWS Signature Version 4. This authentication token is valid only for 15 minutes, and you don’t need to store user credentials in the database, because authentication is managed externally using IAM. Also, it provides encryption of data over transit as well. Depending on your application use case, you can consider enabling IAM authentication. For more information, refer to the limitations and recommendations of this feature.
• Kerberos authentication (MySQL) – You can use Kerberos authentication to authenticate users when they connect to your RDS for MySQL instance. The DB instance works with AWS Directory Service for Microsoft Active Directory (AWS Managed Microsoft AD) to enable Kerberos authentication. When users authenticate with a MySQL DB instance joined to the trusting domain, authentication requests are forwarded. Forwarded requests go to the domain directory that you create with AWS Managed Microsoft AD. For more information, refer to Overview of Setting up Kerberos authentication for MySQL DB instances.

Restrict access to cloud users on Amazon RDS using IAM

IAM helps you access AWS resources securely. You can control access permission for multiple IAM users on different AWS services and features. You can use the same service to grant required permissions on RDS resources like DB clusters and instances.

It’s important to follow the least privilege model while providing access to cloud users (only provide access to required services and actions to complete the required tasks). Also, consider enabling multi-factor authentication for IAM users. For more information, refer to Security best practices in IAM.

Additional logging and monitoring

You can use the following services and features for additional logging and monitoring:

• AWS CloudTrail – CloudTrail provides a record of actions taken by a user, role, or AWS service in Amazon RDS. CloudTrail captures all API calls for Amazon RDS as events, including calls from the Amazon RDS console and from code calls to Amazon RDS API operations. It’s important to monitor the API calls to understand the different operations performed by the users and applications in your AWS account. This can help you perform audits on different operations and manage permissions. It’s also helpful to provide an incident report when unintended operations are run on Amazon RDS resources. For more information, refer to Monitoring Amazon RDS API calls in AWS CloudTrail.
• Amazon RDS recommendations – This feature is enabled by default and provides recommendations on different details related to the DB instances, read replicas, and parameter groups. These recommendations provide best practice guidance by analyzing DB instance configuration, usage, and performance data. For example, any pending engine version upgrades or instance maintenance operations are included in the recommendations. You can consider taking action on the provided recommendations immediately or in the following maintenance window.
• Amazon RDS event notifications – Event notifications are the best way to track changes and get notifications when an Amazon RDS event occurs. For example, if you subscribe to a configuration change category for a DB security group, you’re notified when the DB security group is changed. This helps you address unintended changes immediately and take appropriate action to remediate them.
• AWS Trusted Advisor – Trusted Advisor draws upon best practices learned from serving hundreds of thousands of AWS customers and helps close any security gaps in your AWS account. With respect to Amazon RDS security, Trusted Advisor checks for any security group access risks. For more information, refer to AWS Trusted Advisor check reference.

Automatic minor version upgrades

We strongly recommend keeping your database engines upgraded to the recent minor version because it contains the latest security and functional fixes. Soon after a new minor version is available in Amazon RDS, we set it to be the preferred minor version for new DB instances.

When we identify any version as the preferred minor version because it contains significant fixes, we will consider performing an automatic minor version upgrade of the RDS instance when the Auto minor version upgrade option is enabled on your RDS instance and the current engine version is less than the preferred version. These database upgrades incur downtime and are performed in the defined maintenance window. It’s recommended to enable the Auto minor version upgrade flag for your RDS instances and configure your maintenance window accordingly to stay up to date with the latest fixes. For more information, refer to Best Practices for Upgrading Amazon RDS for MySQL and Amazon RDS for MariaDB.

For example, we can use the following AWS CLI command to identify the preferred version in the mentioned Region for an instance currently running on 8.0.23 version. As we can see in the following output the AutoUpgrade value is True for MySQL version 8.0.28. So, the automatic minor upgrade target is MySQL version 8.0.28, which is highlighted in the output.

[root@testbox ~]# aws rds describe-db-engine-versions --engine mysql --engine-version 8.0.23 --region us-west-2 --query "DBEngineVersions[*].ValidUpgradeTarget[*].{AutoUpgrade:AutoUpgrade,EngineVersion:EngineVersion}"
[
    [
        {
            "AutoUpgrade": false,
            "EngineVersion": "8.0.25"
        },
        {
            "AutoUpgrade": false,
            "EngineVersion": "8.0.26"
        },
        {
            "AutoUpgrade": false,
            "EngineVersion": "8.0.27"
        },
		{
			"AutoUpgrade": true,
			"EngineVersion": "8.0.28"
        },
        {
            "AutoUpgrade": false,
            "EngineVersion": "8.0.30"
        }
    ]
]

Stay current on all RDS-recommended maintenance operations

Amazon RDS occasionally schedules maintenance operations to keep you up to date with the latest security and functional fixes. Also, in case any of the underlying hardware is degraded, it schedules the replacement of the hardware. These scheduled operations are performed in the next defined maintenance window. You can modify the maintenance window to one more suitable for your business needs, but every instance must be defined with a maintenance window. If you haven’t defined the maintenance window, Amazon RDS automatically chooses a window for you based on the Region the instance is located in.

It’s always recommended to perform these operations at your earliest convenience to keep the instances up to date with all required fixes. For more information, refer to What do I need to know about the Amazon RDS maintenance window?

Enable Delete protection

In some cases, when necessary IAM privileges aren’t restricted, RDS instances may be accidentally deleted by your developers or through automation. These types of incidents are costly for production applications. To avoid such scenarios, it’s recommended to enable delete protection for your RDS instances. Enabling or disabling the delete protection option is a quick operation and doesn’t incur any downtime. You must disable delete protection in order to delete the respective instances.

Client access guidelines

It’s important to store passwords securely in application servers. The database credentials (user names and passwords) may become vulnerable when they’re stored in plain text in an application’s configuration files. Instead, consider using AWS Secrets Manager to securely store the passwords of database users. These passwords can be retrieved programmatically and be used to connect to the RDS instances. Also, you can rotate the passwords in Secrets Manager at regular intervals. For more information, refer to Connect to a SQL database with credentials in an AWS Secrets Manager secret.

DB engine-level security

In this section, we discuss best practices for security at the database engine (MySQL and MariaDB) level. Unless explicitly mentioned all of the mentioned features are available for both the engines.

Implement encryption in transit using SSL/TLS

Amazon RDS supports SSL/TLS encryption for MySQL and MariaDB DB instances. With SSL/TLS, you can encrypt a connection between your application client and your RDS DB instance. Amazon RDS creates an SSL/TLS certificate and installs the certificate on the DB instance when Amazon RDS provisions the instance. These certificates are signed by a certificate authority. The SSL/TLS certificate includes the DB instance endpoint as the common name (CN) for the SSL/TLS certificate to guard against spoofing attacks.

You can enable connection using SSL for a database user by using the following command:

ALTER USER 'encrypted_user'@'%' REQUIRE SSL;

Also, with RDS for MySQL, it is possible to mandate the usage of SSL for all connections by enabling the parameter require_secure_transport in the custom parameter group attached to the respective RDS instance. This parameter is dynamic, can be applied to an instance without reboot, and is applicable to all new client connections. For instructions on enabling SSL on different versions, refer to Using SSL/TLS with MySQL and MariaDB instances.

Implement least privilege model for DB users

In a MySQL or MariaDB database, a user is a combination of the user and host, represented as ‘user’@‘host’. Here, the user is the database user name and the host is the client server that connects to the DB instance. We can create different wildcards in the user names or domain names. For example, ‘%’@‘10.1.1.10’ allows any user to connect from that specified IP address. The database engine (MySQL or MariaDB) always matches the users to the closest value possible. For example, if there are two users named ‘test’@‘%.domain.org’ and ‘test’@‘%simple.domain.com’, then when a user named test tries to connect from a domain named oversimple.domain.org, it matches with the user ‘test’@‘%simple.domain.com’ because it most closely reflects the client host.

With this option, it’s recommended to create users with specific IP addresses or domains. If multiple hosts share a specific range of IP addresses or a pattern of domain names, consider providing wildcards as close as possible to their respective details.

Furthermore, it’s strongly recommended to create separate DB users for applications and other purposes rather than using the primary user. The primary user specified at instance create time should be used for administrative purposes, and you should create separate users for respective operations accordingly.

When providing privileges to database users, you should consider allocating only required privileges. In MySQL and MariaDB, it’s possible to allocate privileges at the instance level, database level, table level, and column level. You should always allocate privileges at the required level to protect against unintended data access requests and protect the data against misuse. For example, let’s consider a table named users inside a database named app. When the user needs read-only access on the email column in the users table, the most effective way to grant privileges is option 3 in the following SQL code, as compared to 1 and 2:

## (1) Read access on entire database
GRANT SELECT ON app.* to appuser@‘<domain>’;

## (2) Read access on entire table
GRANT SELECT on app.users to appuser@‘<domain>’;

## (3) Read access on required column only
GRANT SELECT (email) on app.users to appuser@‘<domain>’;

Password validation plugin (MySQL)

Amazon RDS for MySQL supports the usage of the validate_password plugin, which provides additional security. The plugin enforces password policies using parameters in the DB parameter group for your MySQL DB instance.

The plugin is only applicable to new user-managed commands that modify the password or create new users. Also, note that this plugin related settings will be ignored while resetting the primary user via the AWS Management Console or AWS Command Line Interface (AWS CLI). If the provided new primary password doesn’t follow the defined policies, the operation fails even if the respective API call completes.

Effective Password management

In MySQL or MariaDB, you can implement additional actions to enhance security by enforcing certain actions on DB user password management:

• Password expiration requires passwords to be changed periodically. This can help users change passwords on a regular basis. We can set the current user password to expire after a certain number of days. For example, the following command sets the password expiry for user vidya to 90 days, after which the user should change the password. Also, we can set the password expiration policy for users at the global level by modifying the parameter default_password_lifetime in a custom parameter group attached to the respective RDS for MySQL or MariaDB instance accordingly.

CREATE USER 'vidya'@'10.1.12.112' PASSWORD EXPIRE INTERVAL 90 DAY;

## After 90 days the user should change the password to perform any operations.

mysql> SELECT 1;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> ALTER USER USER() IDENTIFIED BY 'my_secret_password';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

• Password reuse restrictions (MySQL) prevent old passwords from being chosen again. This ensures that users aren’t reusing passwords. We can specify the password history and reuse interval. This can be defined either at the individual account level or at the global level using the parameters password_history and password_reuse_interval. For example, the following ALTER command enables a minimum of five password changes before permitting reuse and requires a minimum interval of 365 days before permitting reuse:

ALTER USER 'vidya'@'10.1.12.112' PASSWORD HISTORY 5;
ALTER USER 'vidya'@'10.1.12.112' PASSWORD REUSE INTERVAL 365 DAY;

• Password verification (MySQL) requires that password changes also specify the current password to be replaced. Note that the parameter password_require_current is not available in RDS for MySQL parameter groups. However, we can set the verification restrictions at the individual account level during user creation or later by using the ALTER USER command. For example, the following command enforces specifying the current password before updating the user with a new password:

ALTER USER 'vidya'@'10.1.12.112' PASSWORD REQUIRE CURRENT;

• Random password generation (MySQL) is an alternative to requiring explicit administrator-specified literal passwords. This helps set random and strong passwords for database users. For example, the following command sets a random password to a user and displays the password after the change. Note that the random password is only displayed once after the command is run.

MySQL [(none)]> ALTER USER 'vidya'@'10.1.12.112' IDENTIFIED BY RANDOM PASSWORD;
+-------+-------------+----------------------+
| user  | host        | generated password   |
+-------+-------------+----------------------+
| vidya | 10.1.12.112 | -gF3i@Gg{Q+&oJyP7JZd |
+-------+-------------+----------------------+
1 row in set (0.00 sec)

• Password failure tracking (MySQL) enables a temporary account lock after too many consecutive incorrect password login failures. This provides a way to lock the database users used in unsuccessful login attempts. After a specified number of attempts, the account is locked automatically for a certain number of days. For example, the following command creates a user named u1 with a maximum of three failed attempts and a lock time of 3 days:

MySQL [(none)]> CREATE USER 'u1' IDENTIFIED BY 'my_secret_password'   FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 3;
Query OK, 0 rows affected (0.00 sec)

# After 3 login failures the account will be locked automatically.

[root@testbox ~]# mysql -u u1 -p'wrong_password' -h test999.**********.us-west-2.rds.amazonaws.com -A
ERROR 1045 (28000): Access denied for user 'u1'@'172.31.21.51' (using password: YES)
[root@testbox ~]# mysql -u u1 -p'wrong_password' -h test999.**********.us-west-2.rds.amazonaws.com -A
ERROR 1045 (28000): Access denied for user 'u1'@'172.31.21.51' (using password: YES)
[root@testbox ~]# mysql -u u1 -p'wrong_password' -h test999.**********.us-west-2.rds.amazonaws.com -A
ERROR 3955 (HY000): Access denied for user 'u1'@'172.31.21.51'. Account is blocked for 3 day(s) (3 day(s) remaining) due to 3 consecutive failed logins.
[root@testbox ~]#

# In this case the user should wait for 3 days before attempting to login with correct password.
# Alternatively, you can unblock the user by login to mysql from mater user and execute below command.

MySQL [(none)]> ALTER USER u1 account unlock;
Query OK, 0 rows affected (0.00 sec)

• max_password_errors (MariaDB) parameter can be configured to define the max allowed failed connect attempts due to invalid password. If there is more than this number of failed connect attempts due to invalid password, user will be blocked from further connections until FLUSH_PRIVILEGES.

MariaDB [(none)]> show variables like 'max_password_errors';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| max_password_errors | 2 |
+---------------------+-------+
1 row in set (0.01 sec)

-- The user bloked after configured number of max_password_errors (2) unsuccessful attempts due to wrong password

[root@testbox ~]# mysql -u vidya -p -h test99.**********.us-west-2.rds.amazonaws.com
Enter password:
ERROR 1045 (28000): Access denied for user 'vidya'@'172.31.21.51' (using password: YES)
[root@testbox ~]# mysql -u vidya -p -h test99.**********.us-west-2.rds.amazonaws.com
Enter password:
ERROR 1045 (28000): Access denied for user 'vidya'@'172.31.21.51' (using password: YES)
[root@testbox ~]# mysql -u vidya -p -h test99.**********.us-west-2.rds.amazonaws.com
Enter password:
ERROR 4150 (HY000): User is blocked because of too many credential errors; unblock with 'FLUSH PRIVILEGES'
[root@testbox ~]#

-- Once we run flush privileges the user unblocked and able to connect to database instance as expected

[root@testbox ~]# mysql -u test -p -h test99.**********.us-west-2.rds.amazonaws.com
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 51
Server version: 10.6.10-MariaDB-log managed by https://aws.amazon.com/rds/
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> \q
Bye
[root@testbox ~]#

[root@testbox ~]# mysql -u vidya -p -h test99.**********.us-west-2.rds.amazonaws.com
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 52
Server version: 10.6.10-MariaDB-log managed by https://aws.amazon.com/rds/
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> \q
Bye
[root@testbox ~]#

• max_connect_errors parameters in MySQL and MariaDB will be helpful to define successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections. If a connection from a host is established successfully within fewer than max_connect_errors attempts after a previous connection was interrupted, the error count for the host is cleared to zero. To unblock, flush the host cache with a FLUSH HOSTS statement or mysqladmin flush-hosts.

MariaDB [(none)]> show global variables like 'max_connect_errors';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 3 |
+--------------------+-------+
1 row in set (0.00 sec)

-- The host was blocked after interrupting successive connections

[root@testbox ~]# mysql -u test -ptest1234 -h test99.**********.us-west-2.rds.amazonaws.com
ERROR 1129 (HY000): Host '172.31.21.51' is blocked because of many connection errors; unblock with 'mariadb-admin flush-hosts'
[root@testbox ~]#

-- We have to connect to the database from different host and execute flush hosts command

[root@testbox-123 ~]# mysql -u test -ptest1234 -h test99.**********.us-west-2.rds.amazonaws.com
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.6.10-MariaDB-log managed by https://aws.amazon.com/rds/
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> flush hosts;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> \q
Bye
[root@testbox-123 ~]#

-- After executing flush hosts, we are able to connect to the database normally

[root@testbox ~]# mysql -u test -ptest1234 -h test99.**********.us-west-2.rds.amazonaws.com
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 10.6.10-MariaDB-log managed by https://aws.amazon.com/rds/
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> \q
Bye
[root@testbox ~]#

Restrict operations using Account resource limits

In MySQL and MariaDB, you can restrict the number of connections and queries run by a user by applying account resource limits. These help guard the users against unexpected or unintended operations. Both MySQL and MariaDB allows the following limits for individual accounts:

• The number of queries an account can issue per hour
• The number of updates an account can issue per hour
• The number of times an account can connect to the server per hour
• The number of simultaneous connections to the server by an account

Consider applying these limitations only when you know the exact number of connections needed or queries being run by specific database users. For example, the following query modifies the limits for existing user vidya@'10.12.34.20' to run only 100 queries per hour:

ALTER USER 'vidya'@'10.12.34.20' WITH MAX_QUERIES_PER_HOUR 100;

The following query limits the same user to establish no more than 10 concurrent connections to the database:

ALTER USER 'vidya'@'10.12.34.20' WITH MAX_USER_CONNECTIONS 10;

In addition, you can modify the parameter max_user_connections to set the maximum concurrent connection limit at a global level, which is applied to all users.

Usage of the Memcached plugin (MySQL)

Amazon RDS supports usage of the Memcached interface for InnoDB tables. The Memcached plugin doesn’t support authentication. Therefore, it’s important to follow these best practices whenever using the plugin on your RDS for MySQL instances:

• Always configure Memcached to use a non-default port.
• Consider configuring security groups and network ACLs appropriately to allow access from application servers only. It’s even preferred to provide access to the servers in a VPC rather than providing access to servers outside a VPC.
• Frequently revisit the security rules and ensure they’re up to date.
• Ensure that no sensitive information is stored in the InnoDB tables configured with Memcached.

No-login authentication (MySQL)

The mysql_no_login server-side plugin provides additional security to create and utilize the database users specifically for routines. In databases, we often define routines (procedures and functions) to perform predefined tasks, and these routines must run from a privileged user depending on the operation.

The recommended way to perform these types of operations is by defining a separate user for the routines with the mysql_no_login plugin and grant all required privileges to the users. The users defined with this plugin can’t be used to connect to the database. Instead, they can be used to provide an appropriate way to run routines with the high-level privileges that aren’t provided to regular users to perform database operations.

The following commands install the plugin and create a database user using certain privileges:

INSTALL PLUGIN mysql_no_login SONAME 'mysql_no_login.so';
CREATE USER 'nologin'@'localhost' IDENTIFIED WITH mysql_no_login;
GRANT SELECT, EXECUTE ON secured_database.* TO 'nologin'@'localhost';

Now we can create a procedure as follows to fetch the count of application users without granting access to the application database:

delimiter //
CREATE DEFINER = 'nologin'@'localhost' PROCEDURE users_count()
BEGIN
SELECT COUNT(*) as users_count FROM secured_database.app_users;
END//
delimiter ;

Record all activities on database using Audit logging

RDS for MySQL and MariaDB databases support usage of the MariaDB Audit Plugin. The MariaDB Audit Plugin can record database activities like user logins and logouts to the database, queries run against the database, and more. The audit plugin can be attached to the RDS instance with the help of option groups. You can create a custom option group with the audit plugin enabled and attach it to the RDS for MySQL or MariaDB instance accordingly. Furthermore, you can control the data to be logged into the audit logs that log every activity by default. You can configure the audit plugin to log only connections or specific type of queries (DDL/DML) and include or exclude the activity from certain database users.

Audit logging works as the best way to monitor activity on the database instance and ensure certain compliance. It’s always better to log the specific type of query or activity from a particular user on the database based on your requirements. Note that enabling audit logging on the instance will consume additional resources on the database instance.

Client access guidelines

Rather than using the passwords directly on the command line, consider using mysql_config_editor to define login paths on your client instances and use it to connect to your RDS for MySQL instances. The config editor stores the passwords in a restricted file in an encrypted format.

Also, it’s important to handle the data received as input to the applications to avoid SQL injections. For additional measures that you can implement to enhance security, refer to Client Programming Security Guidelines.

Summary

When migrating from another DB engine to Amazon RDS for MySQL/MariaDB or migrating from MySQL/MariaDB running on premises or on Amazon Elastic Compute Cloud (Amazon EC2) to Amazon RDS for MySQL/MariaDB, security is one of the major areas that you should focus on in order to meet the necessary guidelines. In this post, I covered different security options available with RDS for MySQL and MariaDB instances at different layers like network, database, and engine. Based on the different solutions discussed in this post, you can determine the best security practices for your databases.

For more information about Amazon RDS security, see Security in Amazon RDS.

About the Author

Chelluru V N S S Vidyadhar is a database engineer with the Relational Database Services (RDS) team at Amazon Web Services.