(mysql.info) user-resources

(mysql.info) user-account-management
 
 5.9.4 Limiting Account Resources
 --------------------------------
 
 One means of limiting use of MySQL server resources is to set the
 `max_user_connections' system variable to a non-zero value. However,
 this method is strictly global, and does not allow for management of
 individual accounts. In addition, it limits only the number of
 simultaneous connections made using a single account, and not what a
 client can do once connected. Both types of control are interest to
 many MySQL administrators, particularly those working for Internet
 Service Providers.
 
 In MySQL 5.0, 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.
 
 From MySQL 5.0.3 on, it is also possible to 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.
 
 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',
 `max_connections', and `max_user_connections' columns. If your `user'
 table doesn't have these columns, it must be upgraded; see 
 mysql-upgrade.
 
 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
          ->          MAX_USER_CONNECTIONS 2;
 
 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 per-hour 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). For `MAX_USER_CONNECTIONS', the
 limit is an integer indicating the maximum number of simultaneous
 connections the account can make at any one time. If the limit is set
 to the default value of zero, the `max_user_connections' system variable
 determines the number of simultaneous connections for the account.
 
 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 per-hour resource-use counts can be reset globally for all
 accounts, or individually for a given account:
 
    * 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 currently has.
 
 Counter resets do not affect the `MAX_USER_CONNECTIONS' limit.
 
 All counts begin at zero when the server starts; counts are not carried
 over through a restart.
Info Catalog
(mysql.info) removing-users
(mysql.info) user-account-management
(mysql.info) passwords
automatically generated byinfo2html