MySQL in a Nutshell [29]
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 which the user has privileges.
SHOW VIEW
Allows the use of the SHOW CREATE VIEW statement. This is for version 5.0.1 and above of MySQL.
SHUTDOWN
Allows the use of the shutdown option with the mysqladmin utility.
SUPER
Allows the use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, and the debug option with the command-line utility mysqladmin.
TRIGGER
Allows the user account to create and drop triggers: the CREATE TRIGGER and the DROP TRIGGER statements.
UPDATE
Allows the use of the UPDATE statement.
USAGE
Used to create a user without privileges, or to modify resource limits on an existing user without affecting the existing privileges.
Name
RENAME USER
Synopsis
RENAME USER 'user'[@'host'] TO 'user'[@'host'][,...]
Use this statement to change the username or the host of an existing user account. It does not change the user privileges or necessarily migrate any privileges to specific databases, events, stored routines, tables, triggers, or views. Here is an example:
RENAME USER 'michaelzabalaoui'@'localhost' TO 'zabb'@'%',
'richardstringer'@'localhost' TO 'littlerichard'@'localhost';
The first user’s name and host have been changed here, whereas the second user’s name only was changed.