GRANT - Time and Number of Connection Limits

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

GRANT privilege[,...] [(column[,...])][,  . . . ] 
ON [TABLE|FUNCTION|PROCEDURE] {[{database|*}.{table|*}] | *}
TO 'user'@'host' [IDENTIFIED BY [PASSWORD] 'password'][, ...]

[type of connection restrictions]

[WITH [MAX_QUERIES_PER_HOUR count |
       MAX_UPDATES_PER_HOUR count |
       MAX_CONNECTIONS_PER_HOUR count |
       MAX_USER_CONNECTIONS count] ...]

Explanation

This page of our MySQL documentation relates the MySQL statement, GRANT, which may be used to create new MySQL users, as well as granting user privileges. The text on this page relates to user restrictions as it concerns time limitations and user connection limits.

You can use this MySQL statement with the WITH clause along with the MAX_QUERIES_PER_HOUR option to specify the maximum number of queries that a user account may execute per hour. The MAX_UPDATES_PER_HOUR option is used to give the maximum number of UPDATE statements that may be issued per hour by the user account. The maximum number of connections by a user account to the server per hour can be set with the MAX_CONNECTIONS_PER_HOUR option. The default values for these three options are all 0. This value indicates that there is no limit restrictions for these resources. The MAX_USER_CONNECTIONS option is used to set the maximum number of simultaneous connections the given user account may have. If this value is not set or is set to 0, the value of the system variable max_user_connections is used instead.

Examples

Here is an example of how a user might be limited in such a way using this MySQL statement:

GRANT SELECT ON catalogs.*
TO 'webuser'@'%'
WITH MAX_QUERIES_PER_HOUR 1000
MAX_CONNECTIONS_PER_HOUR 100;

This account is designed for large numbers of users running queries through a web server. The statement creates the webuser user and allows it to read tables from the catalogs database. The user may not run more than 1,000 queries in an hour and may establish only 100 connections in an hour.

To change an existing user account's resources without changing their existing privileges, you can use the USAGE keyword. Simply enter a statement like:

GRANT USAGE ON catalogs.*
TO 'webuser'@'%'
WITH MAX_QUERIES_PER_HOUR 1000
MAX_CONNECTIONS_PER_HOUR 100;

In this example, the existing user account has been limited in resources without changing the user account's privileges.