MySQL provides privileges that apply in different contexts and at different levels of operation:
Administrative privileges enable users to manage operation of the MySQL server. These privileges are global because they are not specific to a particular database.
Database privileges apply to a database and to all objects within it. These privileges can be granted for specific databases, or globally so that they apply to all databases.
Privileges for database objects such as tables, indexes, views, and stored routines can be granted for specific objects within a database, for all objects of a given type within a database (for example, all tables in a database), or globally for all objects of a given type in all databases).
Information about account privileges is stored in the
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.8, “mysql_upgrade — Check and Upgrade MySQL Tables”.
The following table shows the privilege names used at the SQL
level in the
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
|databases, tables, or indexes|
|databases, tables, or views|
|databases, tables, or stored routines|
|databases or tables|
|tables or columns|
|tables or columns|
|tables or columns|
|file access on server host|
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.
ALL PRIVILEGESprivilege specifier is shorthand. It stands for “all privileges available at a given privilege level” (except
GRANT OPTION). For example, granting
ALLat the global or table level grants all global privileges or all table-level privileges.
ALTERprivilege enables use of
ALTER TABLEto change the structure of tables.
ALTER TABLEalso requires the
INSERTprivileges. Renaming a table requires
DROPon the old table,
INSERTon the new table.
ALTER ROUTINEprivilege is needed to alter or drop stored routines (procedures and functions).
CREATEprivilege enables creation of new databases and tables.
CREATE ROUTINEprivilege is needed to create stored routines (procedures and functions).
However, other operations on a temporary table, such as
SELECT, require additional privileges for those operations for the database containing the temporary table, or for the nontemporary table of the same name.
To keep privileges for temporary and nontemporary tables separate, a common workaround for this situation is to create a database dedicated to the use of temporary tables. Then for that database, a user can be granted the
CREATE TEMPORARY TABLESprivilege, along with any other privileges required for temporary table operations done by that user.
DELETEprivilege enables rows to be deleted from tables in a database.
DROPprivilege enables you to drop (remove) existing databases, tables, and views. Beginning with MySQL 5.1.10, the
DROPprivilege is also required to use the statement
ALTER TABLE ... DROP PARTITIONon a partitioned table. Beginning with MySQL 5.1.16, the
DROPprivilege is required for
TRUNCATE TABLE(before that,
TRUNCATE TABLErequires the
DELETEprivilege). If you grant the
DROPprivilege for the
mysqldatabase to a user, that user can drop the database in which the MySQL access privileges are stored.
EVENTprivilege is required to create, alter, drop, or see events for the Event Scheduler. This privilege was added in MySQL 5.1.6.
EXECUTEprivilege is required to execute stored routines (procedures and functions).
FILEprivilege gives you permission to read and write files on the server host using the
LOAD DATA INFILEand
SELECT ... INTO OUTFILEstatements and the
LOAD_FILE()function. A user who has the
FILEprivilege can read any file on the server host that is either world-readable or readable by the MySQL server. (This implies the user can read any file in any database directory, because the server can access any of those files.) The
FILEprivilege also enables the user to create new files in any directory where the MySQL server has write access. This includes the server's data directory containing the files that implement the privilege tables. As a security measure, the server will not overwrite existing files.
GRANT OPTIONprivilege enables you to give to other users or remove from other users those privileges that you yourself possess.
INDEXprivilege enables you to create or drop (remove) indexes.
INDEXapplies to existing tables. If you have the
CREATEprivilege for a table, you can include index definitions in the
LOCK TABLESprivilege enables the use of explicit
LOCK TABLESstatements to lock tables for which you have the
SELECTprivilege. This includes the use of write locks, which prevents other sessions from reading the locked table.
PROCESSprivilege pertains to display of information about the threads executing within the server (that is, information about the statements being executed by sessions). The privilege enables use of
SHOW PROCESSLISTor mysqladmin processlist to see threads belonging to other accounts; you can always see your own threads. The
PROCESSprivilege also enables use of
REFERENCESprivilege is unused.
RELOADprivilege enables use of the
FLUSHstatement. It also enables mysqladmin commands that are equivalent to
reloadcommand tells the server to reload the grant tables into memory.
flush-privilegesis a synonym for
refreshcommand closes and reopens the log files and flushes all tables. The other
flush-commands perform functions similar to
refresh, but are more specific and may be preferable in some instances. For example, if you want to flush just the log files,
flush-logsis a better choice than
REPLICATION SLAVEprivilege should be granted to accounts that are used by slave servers to connect to the current server as their master. Without this privilege, the slave cannot request updates that have been made to databases on the master server.
SELECTprivilege enables you to select rows from tables in a database.
SELECTstatements require the
SELECTprivilege only if they actually retrieve rows from a table. Some
SELECTstatements do not access tables and can be executed without permission for any database. For example, you can use
SELECTas a simple calculator to evaluate expressions that make no reference to tables:
SELECT 1+1; SELECT PI()*2;
SELECTprivilege is also needed for other statements that read column values. For example,
SELECTis needed for columns referenced on the right hand side of
UPDATEstatements or for columns named in the
SHOW DATABASESprivilege enables the account to see database names by issuing the
SHOW DATABASEstatement. Accounts that do not have this privilege see only databases for which they have some privileges, and cannot use the statement at all if the server was started with the
--skip-show-databaseoption. Note that any global privilege is a privilege for the database.
SUPERprivilege enables an account to use
CHANGE MASTER TO,
KILLor mysqladmin kill to kill threads belonging to other accounts (you can always kill your own threads),
PURGE BINARY LOGS, configuration changes using
SET GLOBALto modify global system variables, the mysqladmin debug command, enabling or disabling logging, performing updates even if the
read_onlysystem variable is enabled, starting and stopping replication on slave servers, specification of any account in the
DEFINERattribute of stored programs and views, and enables you to connect (once) even if the connection limit controlled by the
max_connectionssystem variable is reached.
TRIGGERprivilege enables trigger operations. You must have this privilege for a table to create, drop, or execute triggers for that table. This privilege was added in MySQL 5.1.6. (Prior to MySQL 5.1.6, trigger operations required the
UPDATEprivilege enables rows to be updated in tables in a database.
USAGEprivilege specifier stands for “no privileges.” It is used at the global level with
GRANTto modify account attributes such as resource limits or SSL characteristics without affecting existing account privileges.
It is a good idea to grant to an account only those privileges
that it needs. You should exercise particular caution in granting
FILE and administrative
FILEprivilege can be abused to read into a database table any files that the MySQL server can read on the server host. This includes all world-readable files and files in the server's data directory. The table can then be accessed using
SELECTto transfer its contents to the client host.
ALTERprivilege may be used to subvert the privilege system by renaming tables.
SHUTDOWNprivilege can be abused to deny service to other users entirely by terminating the server.
PROCESSprivilege can be used to view the plain text of currently executing statements, including statements that set or change passwords.
SUPERprivilege can be used to terminate other sessions or change how the server operates.
Privileges granted for the
mysqldatabase itself can be used to change passwords and other access privilege information. Passwords are stored encrypted, so a malicious user cannot simply read them to know the plain text password. However, a user with write access to the
Passwordcolumn can change an account's password, and then connect to the MySQL server using that account.