4.3.6 Limiting user resources

Starting from MySQL 4.0.2 one can limit certain resources per user.

So far, the only available method of limiting usage of MySQL server resources has been setting the max_user_connections startup variable to a non-zero value. But this method is strictly global and does not allow for management of individual users, which could be of particular interest to Internet Service Providers.

Therefore, management of three resources is introduced on the individual user level:

• Number of all queries per hour: All commands that could be run by a user.
• Number of all updates per hour: Any command that changes any table or database.
• Number of connections made per hour: New connections opened per hour.

A user in the aforementioned context is a single entry in the user table, which is uniquely identified by its user and host columns.

All users are by default not limited in using the above resources, unless the limits are granted to them. These limits can be granted only via global GRANT (*.*), using this syntax:

GRANT ... WITH MAX_QUERIES_PER_HOUR N1
               MAX_UPDATES_PER_HOUR N2
               MAX_CONNECTIONS_PER_HOUR N3;

One can specify any combination of the above resources. N1, N2, and N3 are integers and represent counts per hour.

If a user reaches the limit on number of connections within one hour, no further connections will be accepted until that hour is up. Similarly, if the user reaches the limit on number of queries or updates, further queries or updates will be rejected until the hour is up. In all cases, an appropriate error message shall be issued.

Current usage values for a particular user can be flushed (set to zero) by issuing a GRANT statement with any of the above clauses, including a GRANT statement with the current values.

Also, current values for all users will be flushed if privileges are reloaded (in the server or using mysqladmin reload) or if the FLUSH USER_RESOURCES command is issued.

The feature is enabled as soon as a single user is granted with any of the limiting GRANT clauses.

As a prerequisite for enabling this feature, the user table in the mysql database must contain the additional columns, as defined in the table creation scripts mysql_install_db and mysql_install_db.sh in `scripts' subdirectory.

User Comments

Will be great if we can limit number of user per base
and not per hour.

How about something to limit the number of rows per hour.

Rows examined, sent, whatever.

There are many users we have that constantly send themselves an entire table, with their application sometimes limiting the number of rows displayed, other times not.

This feature does not seem to have yet been implemented for Win32. It would be helpful if the additional grant table columns could be described here, since in the Windows version of MySQL, there is no mysql_install_db script (usually unnecessary since the equivalent function is handled by the installer). Either that or update the installer to create the enhanced grant tables.

Since tables files are created with mysql:mysql user, it would be most useful to somewhat limit table/database sizes, i.e. "grant ... to user@host with 50MB storage"
or something like that

As of 4.0.12, this feature now works as shown above for Win32 (tested on Win2k Server).

If you're upgrading from 3.23.XX, you'll need to use mysql_fix_privilege_tables (as root, of course). If you don't have CygWin or ActivePerl installed, you'll have to do a bit of copying/pasting from the script to a command line. Be VERY VERY careful doing this, or you can trash your grant tables!

Thank you for getting this enabled for Win32. :)

I would like to see a feature that limits the number of simultaneous connections a user can make. This would be very important in a co-location environment.

Followup to my previous comment:
A "mysql_fix_privilege_tables.sql" script is included in the Win32 distribution beginning with MySQL 4.1-alpha. See the README.1st file for instructions on how to use it.

(Nice to see that somebody's listening. Thanks!)

It would also be nice to be able to limit the number of rows a user can get (i.e. enforced "limit") without imposing a rows per hour, queries per hour and other such restrictions. This would be useful for situations where you want to stop a user or application from using up too much resource but don't want to every deny service.

Being able to limit usage sounds great, but what about the
ability to be able to see what a user is doing to know where
to set the limits?

ie - if this is a current user and they are doing 1000
connections per hour already, I wouldn't want to set this
limit to 500 connections per hour.

Add your own comment.