Managing Users and Privileges - Learning MySQL - page 337

Resource-Limit Controls
MySQL 4.0.2 added new resource-limit controls for users. These are maintained along
with the global privileges, and affect users rather than client connections. With these
controls, you can limit:
• The number of SQL statements per hour, using the MAX_QUERIES_PER_HOUR clause.
All statements executed by a user are counted toward this limit.
• The number of updates per hour, using the MAX_UPDATES_PER_HOUR clause. Any
statement that modifies a database or its tables counts toward this limit.
• The number of connections per hour, using the MAX_CONNECTIONS_PER_HOUR clause.
Any connection, from the monitor, a program, or a web script, counts toward this
limit.
These clauses can be added to a GRANT statement, or you can set them manually using
SQL as discussed later in “Managing Privileges with SQL.”
For example, to set limits for the existing user 'partmusic'@'localhost' , giving this user
a maximum of 100 queries per hour, 10 updates, and 5 connections, you'd type:
mysql> GRANT USAGE ON *.* to 'partmusic'@'localhost' WITH
-> MAX_QUERIES_PER_HOUR 100
-> MAX_UPDATES_PER_HOUR 10
-> MAX_CONNECTIONS_PER_HOUR 5;
Query OK, 0 rows affected (0.06 sec)
Since we've used USAGE , the privileges aren't affected when the new limits are imposed.
After imposing these limits, you'll find an error message is returned when you exceed
them. For example, after running and quitting the monitor five times in succession,
you'll see this:
$ mysql --user=partmusic
ERROR 1226 (42000): User 'partmusic' has exceeded the 'max_connections'
resource (current value: 5)
Remember, these limits apply per user and not per connection. If you start two separate
instances of the MySQL monitor client and log in to the server as the same user, both
connections contribute towards reaching the user's hourly limits.
Another useful parameter to manage the MySQL server load is the MAX_USER_CONNEC
TIONS option. This limits the number of simultaneous clients that can access the server
and is usually set when you start mysqld or in an options file. We discuss options files
in Chapter 11.
The mysql_setpermission Program
mysql_setpermission is an interactive program that allows you to choose from a menu
of routine database and user administration tasks, such as creating a database, setting
a user password, and modifying user privileges. The program menu is shown here: