Limit 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.
  • This method is strictly global, and does not allow for management of individual accounts.
  • It limits only the number of simultaneous connections made using a single account.

• An administrator 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.

• Here is the syntax to limit the resources for a user.

  mysql> GRANT ALL ON database.* TO 'user'@'localhost'
    -> IDENTIFIED BY 'password'
    -> WITH MAX_QUERIES_PER_HOUR 40
    -> MAX_UPDATES_PER_HOUR 10
    -> MAX_CONNECTIONS_PER_HOUR 5
    -> MAX_USER_CONNECTIONS 2;

• To set or change limits for an existing account, use a GRANT USAGE statement at the global level.
  • For example: GRANT USAGE ON *.* TO 'user'@'localhost' WITH MAX_QUERIES_PER_HOUR 10;

• To remove an existing limit, set its value to zero.
  • For example: GRANT USAGE ON *.* TO 'user'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 0;

• Issue a FLUSH USER_RESOURCES statement to reset the current counts for all accounts.

How do I assign account passwords?

---

---

© 2007 John Michael Pierobon

Notes