• Choose a category
• All
• Classic-Fiction

By Root 22551 0

queries that a user account may execute per hour. The MAX_UPDATES_PER_HOUR option is used to give the maximum number of UPDATE statements that may be issued per hour by the user account. The maximum number of connections by a user account to the server per hour can be set with the MAX_CONNECTIONS_PER_HOUR option. The default values for these three options are all 0. This value indicates that there is no limit or restrictions for these resources. The MAX_USER_CONNECTIONS option is used to set the maximum number of simultaneous connections the given user account may have. If this value is not set or is set to 0, the value of the system variable max_user_connections is used instead. Here is an example of how a user might be limited in such a way:

GRANT SELECT ON catalogs.*

TO 'webuser'@'%'

WITH MAX_QUERIES_PER_HOUR 1000

MAX_CONNECTIONS_PER_HOUR 100;

This account is designed for large numbers of users running queries through a web server. The statement creates the webuser user and allows it to read tables from the catalogs database. The user may not run more than 1,000 queries in an hour and may establish only 100 connections in an hour.

To change an existing user account’s resources without changing the account’s existing privileges, you can use the USAGE keyword. Simply enter a statement like this:

GRANT USAGE ON catalogs.*

TO 'webuser'@'%'

WITH MAX_QUERIES_PER_HOUR 10000

MAX_CONNECTIONS_PER_HOUR 100;

In this example, the existing user account has been limited in resources without changing the user account’s privileges. See Table 4-2 for a list of privileges.

Table 4-2. Privileges in GRANT and REVOKE

Privilege

Description

ALL [PRIVILEGES]

Grants all of the basic privileges. Does not include GRANT OPTION.

ALTER

Allows use of the ALTER TABLE statement.

ALTER ROUTINE

Allows the user account to alter or drop stored routines. This includes the ALTER FUNCTION and ALTER PROCEDURE statements, as well as the DROP FUNCTION and DROP PROCEDURE statements.

CREATE

Grants CREATE TABLE statement privileges.

CREATE ROUTINE

Allows the user account to create stored routines. This includes the CREATE FUNCTION and CREATE PROCEDURE statements. The user has ALTER ROUTINE privileges to any routine he creates.

CREATE TEMPORARY TABLES

Allows the CREATE TEMPORARY TABLES statement to be used.

CREATE USER

Allows the user account to execute several user account management statements: CREATE USER, RENAME USER, REVOKE ALL PRIVILEGES, and the DROP USER statements.

CREATE VIEW

Allows the CREATE VIEW statement. This was first enabled in version 5.0.1 of MySQL.

DELETE

Allows the DELETE statement to be used.

DROP

Allows the user to execute DROP TABLE and TRUNCATE statements.

EVENT

Allows the user account to create events for the event scheduler. As of version 5.1.12 of MySQL, this privilege allows the use of the CREATE EVENT, ALTER EVENT, and DROP EVENT statements.

EXECUTE

Allows the execution of stored procedures. This is available as of version 5 of MySQL.

FILE

Allows the use of SELECT...INTO OUTFILE and LOAD DATA INFILE statements to export from and import to a file.

GRANT OPTION

Allows the use of the GRANT statement to grant privileges to users. This option is specified with the WITH clause of the GRANT statement.

INDEX

Allows the use of CREATE INDEX and DROP INDEX statements.

INSERT

Allows the use of INSERT statements.

LOCK TABLES

Allows the use of LOCK TABLES statement for tables for which the user has SELECT privileges.

PROCESS

Allows the use of SHOW FULL PROCESSLIST statements.

REFERENCES

This is not used. It’s for future releases.

RELOAD

Allows the use of FLUSH and RESET statements.

REPLICATION CLIENT

Allows the user to query master and slave servers for status information.

REPLICATION SLAVE

Required for replication slave servers. Allows binary log events to be read from the master server.

SELECT

Allows the use of the SELECT statement.

SHOW DATABASES

Permits the use of the SHOW DATABASES statement for all databases, not just the ones for