GRANT privileges ON db.table TO user@host IDENTIFIED BY 'password';
Use the GRANT command to give a database user a set of privileges on a database or on a list of tables. The GRANT command creates the specified user account if it does not already exist. In fact, in MySQL version 4.1 and earlier where there is no CREATE USER command, you have to use GRANT to create a new user.
The privileges that can be set are shown in Table 6.1. Specifying ALL PRIVILEGES grants each of these options.
Table 6.1. Privileges Allocated Using GRANT
Allows use of ALTER TABLE.
Alter or drop stored routines.
Allows use of CREATE TABLE.
Create stored routines.
CREATE TEMPORARY TABLES
Allows use of CREATE TEMPORARY TABLE.
Allows use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
Allows use of CREATE VIEW.
Allows use of DELETE.
Allows use of DROP TABLE.
Allows the user to run stored routines.
Allows use of SELECT ... INTO OUTFILE and LOAD DATA INFILE.
Allows use of CREATE INDEX and DROP INDEX.
Allows use of INSERT.
Allows use of LOCK TABLES on tables for which you also have SELECT privileges.
Allows use of SHOW FULL PROCESSLIST.
Allows use of FLUSH.
Allows the user to ask where slave or master servers are.
Needed for replication slaves.
Allows use of SELECT.
Allows use of SHOW DATABASES.
Allows use of SHOW CREATE VIEW.
Allows use of mysqladmin shutdown.
Allows use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL SQL statements. Allows mysqladmin debug command. Allows one extra connection to be made if maximum connections are reached.
Allows use of UPDATE.
Allows connection without any specific privileges.
A comma-separated list of privileges can be used with GRANT to give a user several permissions in one statement. The following example gives the user zak permission to query the table mytable and insert new records, but not to update or delete existing records:
GRANT SELECT, INSERT ON sampdb.mytable TO 'zak'@'localhost' IDENTIFIED BY 'phrasebook';
To give a user all the privileges listed in Table 6.1, you can use ALL PRIVILEGES, or simply the keyword ALL, in the GRANT statement. The following example gives zak full access to the table:
GRANT ALL PRIVILEGES ON sampdb.mytable TO 'zak'@'localhost' IDENTIFIED BY 'phrasebook';
If you are granting new privileges to a user that already exists, you do not need to specify the IDENTIFIED BY clause. If IDENTIFIED BY is used, the user's password is overwritten. If it is omitted, the password remains unchanged.