4.5.3 Removing User Accounts from MySQL - MySQL

To remove an account, use the DROP USER statement, which was added in MySQL 4.1.1. For older versions of MySQL, use DELETE instead.

To remove a MySQL user account, you should use the following procedure, performing the steps in the order shown:

1. Use SHOW GRANTS to determine what privileges the account has.

2. Use REVOKE to revoke the privileges displayed by SHOW GRANTS. This removes records for the account from all the grant tables except the user table, and revokes any global privileges listed in the user table.

3. Delete the account by using DROP USER to remove the user table record.

The DROP USER statement was added in MySQL 4.1.1. Before 4.1.1, you should first revoke the account privileges as just described. Then delete the user table record and flush the grant tables like this:

mysql> DELETE FROM mysql.user

-> WHERE User='user_name' and Host='host_name';
mysql> FLUSH PRIVILEGES;

4.5.4 Limiting Account Resources

Before MySQL 4.0.2, the only available method for limiting use of MySQL server resources is to set the max_user_connections system variable to a non-zero value. But that method is strictly global. It does not allow for management of individual accounts. Also, it limits only the number of simultaneous connections made using a single account, not what a client can do once connected. Both types of control are of interest to many MySQL administrators, particularly those for Internet Service Providers.

Starting from MySQL 4.0.2, 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

• The number of times an account can connect to the server per hour

Any statement that a client can issue counts against the query limit. Only statements that modify databases or tables count against the update limit.

An account in this context is a single record in the user table. Each account is uniquely identified by its User and Host column values.

As a prerequisite for using this feature, the user table in the mysql database must contain the resource-related columns. Resource limits are stored in the max_questions, max_updates, and max_connections columns. If your user table doesn't have these columns, it must be upgraded; see Section 2.5.8, "Upgrading the Grant Tables."

To set resource limits with a GRANT statement, use a WITH clause that names each resource to be limited and a per-hour count indicating the limit value. For example, to create a new account that can access the customer database, but only in a limited fashion, issue this statement:

mysql> GRANT ALL ON customer.* TO

'francis'@'localhost'

-> IDENTIFIED BY 'frank'

-> WITH MAX_QUERIES_PER_HOUR 20

-> MAX_UPDATES_PER_HOUR 10

-> MAX_CONNECTIONS_PER_HOUR 5;

The limit types need not all be named in the WITH clause, but those named can be present in any order. The value for each limit 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 is, no limit).

To set or change limits for an existing account, use a GRANT USAGE statement at the global level (ON *.*). The following statement changes the query limit for francis to 100:

mysql> GRANT USAGE ON *.* TO 'francis'@'localhost'

-> WITH MAX_QUERIES_PER_HOUR 100;

This statement leaves the account's existing privileges unchanged and modifies only the limit values specified.

To remove an existing limit, set its value to zero. For example, to remove the limit on how many times per hour francis can connect, use this statement:

mysql> GRANT USAGE ON *.* TO 'francis'@'localhost'

-> WITH MAX_CONNECTIONS_PER_HOUR 0;

Resource-use counting takes place when any account has a non-zero limit placed on its use of any of the resources.

As the server runs, it counts the number of times each account uses resources. If an account reaches its limit on number of connections within the last hour, further connections for the account are rejected until that hour is up. Similarly, if the account reaches its limit on the number of queries or updates, further queries or updates are rejected until the hour is up. In all such cases, an appropriate error message is issued.

Resource counting is done per account, not per client. For example, if your account has a query limit of 50, you cannot increase your limit to 100 by making two simultaneous client connections to the server. Queries issued on both connections are counted together.

The current resource-use counts can be reset globally for all accounts, or individually for a given count:

• To reset the current counts to zero for all accounts, issue a FLUSH USER_RESOURCES statement. The counts also can be reset by reloading the grant tables (for example, with a FLUSH PRIVILEGES statement or a mysqladmin reload command).

• The counts for an individual account can be set to zero by re-granting it any of its limits. To do this, use GRANT USAGE as described earlier and specify a limit value equal to the value that the account already has.