6.2.1 Privileges Provided by MySQL

MySQL provides privileges that apply in different contexts and at different levels of operation:

Information about account privileges is stored in the user, db, host, tables_priv, columns_priv, and procs_priv tables in the mysql database (see Section 6.2.2, “Privilege System Grant Tables”). The MySQL server reads the contents of these tables into memory when it starts and reloads them under the circumstances indicated in Section 6.2.6, “When Privilege Changes Take Effect”. Access-control decisions are based on the in-memory copies of the grant tables.

Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features. To make sure that you can take advantage of any new capabilities, update your grant tables to have the current structure whenever you update to a new version of MySQL. See Section 4.4.7, “mysql_upgrade — Check and Upgrade MySQL Tables”.

The following table shows the privilege names used at the SQL level in the GRANT and REVOKE statements, along with the column name associated with each privilege in the grant tables and the context in which the privilege applies.

Table 6.2 Permissible Privileges for GRANT and REVOKE

PrivilegeColumnContext
CREATECreate_privdatabases, tables, or indexes
DROPDrop_privdatabases, tables, or views
GRANT OPTIONGrant_privdatabases, tables, or stored routines
LOCK TABLESLock_tables_privdatabases
REFERENCESReferences_privdatabases or tables
EVENTEvent_privdatabases
ALTERAlter_privtables
DELETEDelete_privtables
INDEXIndex_privtables
INSERTInsert_privtables or columns
SELECTSelect_privtables or columns
UPDATEUpdate_privtables or columns
CREATE TEMPORARY TABLESCreate_tmp_table_privtables
TRIGGERTrigger_privtables
CREATE VIEWCreate_view_privviews
SHOW VIEWShow_view_privviews
ALTER ROUTINEAlter_routine_privstored routines
CREATE ROUTINECreate_routine_privstored routines
EXECUTEExecute_privstored routines
FILEFile_privfile access on server host
CREATE TABLESPACECreate_tablespace_privserver administration
CREATE USERCreate_user_privserver administration
PROCESSProcess_privserver administration
PROXYsee proxies_priv tableserver administration
RELOADReload_privserver administration
REPLICATION CLIENTRepl_client_privserver administration
REPLICATION SLAVERepl_slave_privserver administration
SHOW DATABASESShow_db_privserver administration
SHUTDOWNShutdown_privserver administration
SUPERSuper_privserver administration
ALL [PRIVILEGES] server administration
USAGE server administration

The following list provides a general description of each privilege available in MySQL. Particular SQL statements might have more specific privilege requirements than indicated here. If so, the description for the statement in question provides the details.

It is a good idea to grant to an account only those privileges that it needs. You should exercise particular caution in granting the FILE and administrative privileges: