There are several options to implement firewall like features for a MySQL instance. Some of these are described below. When an account is mentioned it refers to the combination of a username connected from a given host, i.e. username@hostname.
MySQL Enterprise Firewall
MySQL Enterprise Firewall will allow you to create a whitelist per account specifying the normalized queries that the account are allowed to execute. It is available in the Enterprise Edition of MySQL Server in versions 5.6.24/5.7 and later.
You can limit the number of connections for all users by setting the max_connections option. This is useful to avoid for example excessive memory usage and contention from having too many connections at the same time that potentially all may execute queries. In addition to the specified number of connections, there is one extra connection reserved for a user with the CONNECTION_ADMIN (in MySQL 8.0+) or the SUPER (earlier versions) privilege.
Important: Given that one extra connection is reserved for a user with the CONNECTION_ADMIN/SUPER privilege, it is important not to give these privileges to application users as that would just cause the extra connection to be used by the application and defeat the purpose of it. For the same reason, do not leave users with these privileges logged in when you are not using them.
Account Resource Limits
It is possible to limit the activity of an individual account by setting one of the following user options with the CREATE USER or ALTER USER (MySQL 5.7 and later) or the GRANT statement (MySQL 5.6 and earlier):
- MAX_QUERIES_PER_HOUR – maximum number of queries per hour
- MAX_UPDATES_PER_HOUR – maximum number of updates per hour
- MAX_CONNECTIONS_PER_HOUR – maximum number of connections per hour
- MAX_USER_CONNECTIONS – maximum number of connections the account can have at the same time
In MySQL 8.0 and later, you can also limit the operating system resources a connection is allowed to use. Currently it is only supported to limit the CPU resources. This is done using a resource group which can be assigned to a thread (i.e. for users, the assignment must be done after creating the connection).
Block Access Based on Audit Events
In Enterprise Edition of MySQL Server 5.7.20 and later, you can use MySQL Enterprise Audit to block access based on the audit log filtering rules.