Important: MySQL does not automatically revoke any privileges when you drop a database or table. However, if you drop a routine, any routine-level privileges granted for that routine are revoked.

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';

The CREATE TABLESPACE, CREATE USER, FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN, and SUPER privileges are administrative and can only be granted globally.

Other privileges can be granted globally or at more specific levels.

MySQL stores global privileges in the mysql.user table.

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';

If you use ON * syntax (rather than ON *.*) and you have selected a default database, privileges are assigned at the database level for the default database. An error occurs if there is no default database.

The CREATE, DROP, EVENT, GRANT OPTION, LOCK TABLES, and REFERENCES privileges can be specified at the database level. Table or routine privileges also can be specified at the database level, in which case they apply to all tables or routines in the database.

MySQL stores database privileges in the mysql.db table.

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 permissible priv_type values at the table level are ALTER, CREATE VIEW, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, and UPDATE.

MySQL stores table privileges in the mysql.tables_priv table.

Column privileges apply to single columns in a given table. Each privilege to be granted at the column level must be followed by the column or columns, enclosed within parentheses.

GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';

The permissible priv_type values for a column (that is, when you use a column_list clause) are INSERT, REFERENCES, SELECT, and UPDATE.

MySQL stores column privileges in the mysql.columns_priv table.

The ALTER ROUTINE, CREATE ROUTINE, EXECUTE, and GRANT OPTION privileges apply to stored routines (procedures and functions). They can be granted at the global and database levels. Except for CREATE ROUTINE, these privileges can be granted at the routine level for individual routines.

GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';

The permissible priv_type values at the routine level are ALTER ROUTINE, EXECUTE, and GRANT OPTION. CREATE ROUTINE is not a routine-level privilege because you must have this privilege to create a routine in the first place.

MySQL stores routine-level privileges in the mysql.procs_priv table.

The PROXY privilege enables one user to be a proxy for another. The proxy user impersonates or takes the identity of the proxied user.

GRANT PROXY ON 'localuser'@'localhost' TO 'externaluser'@'somehost';

When PROXY is granted, it must be the only privilege named in the GRANT statement, the REQUIRE clause cannot be given, and the only permitted WITH option is WITH GRANT OPTION.

Proxying requires that the proxy user authenticate through a plugin that returns the name of the proxied user to the server when the proxy user connects, and that the proxy user have the PROXY privilege for the proxied user. For details and examples, see Section 6.3.8, “Proxy Users”.

MySQL stores proxy privileges in the mysql.proxies_priv table.

mysql -Bse "SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user" | mysql -Bs | sed 's/$/;/g'

mysql> GRANT FILE, SELECT, SHOW VIEW, LOCK TABLES, RELOAD, REPLICATION CLIENT ON
*.* TO 'backup_user'@'localhost' IDENTIFIED BY 'some_password';
Query OK, 0 rows affected (0.01 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

CREATE USER 'peter'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'peter'@'localhost';
GRANT SELECT ON db2.invoice TO 'peter'@'localhost';
GRANT USAGE ON *.* TO 'peter'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;