How to Limit Account Resources in MySQL

If you use MySQL 5.1 you can limit the following server resources for individual accounts: the number of queries that an account can issue per hour, the number of updates that an account can issue per hour and the number of times an account can connect to the server per hour. You can also limit the number of simultaneous connections to the server on a per-account basis.

An account in this context is a single row in the user table. Each account is uniquely identified by its User and Host column values. Before we begin, check if the user table contains the resource-related columns. Resource limits are stored in the max_questions, max_updates, max_connections, and max_user_connections columns. To set the resource limits you may use the GRANT statement and a WITH clause that names each resource to be limited and a per-hour count indicating the limit value.

In the following example, I'll show you how to create an account named softpedia that can access the customer database only for a period of time. The user can do maximum 30 queries per hour and 15 updates per hour.

mysql> GRANT ALL ON customer.* TO 'softpedia'@'localhost'
-> IDENTIFIED BY 'softpedia'
-> WITH MAX_QUERIES_PER_HOUR 30
-> MAX_UPDATES_PER_HOUR 15;

The value for MAX_QUERIES_PER_HOUR and MAX_UPDATES_PER_HOUR limits should be an integer representing a count per hour. If the GRANT statement has no WITH clause, the limits are each set to the default value of zero that means no limit.

If you want to change or to set limits for an existing account, use the GRANT USAGE statement for global level.

mysql> GRANT USAGE ON *.* TO 'softpedia'@'localhost'
-> WITH MAX_QUERIES_PER_HOUR 50
-> MAX_USER_CONNECTIONS 1;

Using these queries, I increased the maximum queries per hour for user softpedia from 30 queries to 50 queries and I added a limit named max_user_connections which determines the number of simultaneous connections for the account and the limit will be 1 connection for softpedia.

If you want to remove the limits, set the values to 0.

mysql> GRANT USAGE ON *.* TO 'softpedia'@'localhost'
-> WITH MAX_USER_CONNECTIONS 0
-> MAX_UPDATES_PER_HOUR 0;