GRANT Syntax

The GRANT statement enables system administrators to create MySQL user accounts and to grant rights to accounts. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. The REVOKE statement is related and enables administrators to remove account privileges. To determine what privileges an account has, use SHOW GRANTS.

Global privileges

Global privileges are administrative or apply to all databases on a given server. To assign global privileges, use ON *.* syntax:

GRANT ALL ON *.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';

Database privileges

Database privileges apply to all objects in a given database. To assign database-level privileges, use ON db_name.* syntax:

GRANT ALL ON mydb.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';

Privileges also are assigned at the database level (for the default database) if you use ON * syntax and you have selected a default database.

Database privileges are stored in the mysql.db and mysql.host tables. GRANT and REVOKE affect the db table, but not the host table, which is rarely used.

Table privileges

Table privileges apply to all columns in a given table. To assign table-level privileges, use ON db_name.tbl_name syntax:

GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

If you specify tbl_name rather than db_name.tbl_name, the statement applies to tbl_name in the default database. An error occurs if there is no default database.

The allowable priv_type values for a table are ALTER, CREATE VIEW, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, SELECT, SHOW VIEW, and UPDATE.

Table privileges are stored in the mysql.tables_priv table.