The following list provides general descriptions of the privileges 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.
The ALL or
ALL
PRIVILEGES privilege specifier is shorthand. It stands for “all privileges available at a given privilege
level” (except GRANT
OPTION). For example, granting ALL at
the global or table level grants all global privileges or all table-level
privileges.
The ALTER privilege enables use of the ALTER TABLE statement to
change the structure of tables. ALTER TABLE also requires
the CREATE and INSERT privileges. Renaming a table requires ALTER and DROP on
the old table, CREATE, and INSERT on the new table.
The ALTER
ROUTINE privilege is needed to alter or drop stored routines
(procedures and functions).
The CREATE privilege enables creation of new databases and
tables.
The CREATE
ROUTINE privilege is needed to create stored routines (procedures and
functions).
The CREATE TABLESPACE privilege is needed to create, alter,
or drop tablespaces and log file groups.
The CREATE TEMPORARY TABLES privilege enables the creation
of temporary tables using the CREATE TEMPORARY
TABLE statement.
After a session has created a temporary table, the server performs no further
privilege checks on the table. The creating session can perform any operation on
the table, such as DROP TABLE, INSERT, UPDATE, or SELECT. For more information, see Section 13.1.18.3, “CREATE TEMPORARY TABLE
Syntax”.
The CREATE
USER privilege enables use of the ALTER USER, CREATE USER, DROP
USER, RENAME USER, and REVOKE ALL PRIVILEGES statements.
The CREATE
VIEW privilege enables use of the CREATE VIEW statement.
The DELETE privilege enables rows to be deleted from tables
in a database.
The DROP privilege enables you to drop (remove) existing databases, tables, and views.
The DROP privilege is required in order to use the
statement ALTER TABLE ... DROP PARTITION on a
partitioned table. The DROP privilege is also required for TRUNCATE TABLE. If you grant
the DROP privilege for the mysql database to a user, that user can drop the database in which the MySQL access
privileges are stored.
The EVENT privilege is required to create, alter, drop, or see events for the Event
Scheduler.
The EXECUTE privilege is required to execute stored
routines (procedures and functions).
The FILE privilege gives you permission to read and write files on the server host using
the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. A user who has the FILE privilege 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 FILE privilege 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. As
of MySQL 5.7.17, the FILE privilege is required to use the DATA DIRECTORY or
INDEX DIRECTORY table option for the CREATE TABLE statement.
To limit the location in which files can be read and written, set the secure_file_priv system to a specific directory. See Section 5.1.5, “Server System
Variables”.
The GRANT
OPTION privilege enables you to give to other users or remove from
other users those privileges that you yourself possess.
The INDEX privilege enables you to create or drop (remove) indexes. INDEX applies to existing tables. If you have the CREATE privilege for a table, you can include index
definitions in the CREATE TABLE statement.
The INSERT privilege enables rows to be inserted into
tables in a database. INSERT is also required for the ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE table-maintenance statements.
The LOCK
TABLES privilege enables the use of explicit LOCK TABLES statements to
lock tables for which you have the SELECT privilege. This includes the use of write locks,
which prevents other sessions from reading the locked table.
The PROCESS privilege 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 PROCESSLIST or
mysqladmin
processlist to see threads belonging to other accounts; you
can always see your own threads. The PROCESS privilege also enables use of SHOW ENGINE.
The PROXY privilege enables a user to impersonate or become known as another user. See Section 6.3.9,
“Proxy Users”.
The REFERENCES privilege is unused before MySQL 5.7.6. As
of 5.7.6, creation of a foreign key constraint requires the REFERENCES privilege for the parent table.
The RELOAD privilege enables use of the FLUSH statement. It also enables mysqladmin commands that are
equivalent to FLUSH operations: flush-hosts, flush-logs, flush-privileges, flush-status,
flush-tables, flush-threads, refresh, and
reload.
The reload command tells the server to reload the
grant tables into memory. flush-privileges is a
synonym for reload. The refresh command closes and reopens the log files and
flushes all tables. The other flush-xxx 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-logs is a better choice than refresh.
The REPLICATION CLIENT privilege enables the use of the SHOW MASTER
STATUS, SHOW SLAVE STATUS,
and SHOW BINARY LOGS statements.
The REPLICATION SLAVE privilege 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.
The SELECT privilege enables you to select rows from tables
in a database. SELECT statements require the
SELECT privilege only if they actually retrieve rows
from a table. Some SELECT statements do not
access tables and can be executed without permission for any database. For
example, you can use SELECT as a simple calculator
to evaluate expressions that make no reference to tables:
SELECT 1+1;
SELECT PI()*2;
The SELECT privilege is also needed for other statements
that read column values. For example, SELECT is needed for columns referenced on the right
hand side of col_name=expr assignment in UPDATE statements or for columns named in the WHERE clause of DELETE or UPDATE statements.
The SELECT privilege is also needed for tables or views
being used with EXPLAIN, including any
underlying tables of views.
The SHOW
DATABASES privilege enables the account to see database names by
issuing the SHOW DATABASE statement. 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-database option. Note that any global privilege is a privilege for the
database.
The SHOW
VIEW privilege enables use of the SHOW CREATE VIEW statement. This privilege is also
needed for views being used with EXPLAIN.
The SHUTDOWN privilege enables use of the SHUTDOWN statement, the mysqladmin
shutdown command, and the mysql_shutdown() C API function.
The SUPER privilege enables these operations and server behaviors:
You may also need the SUPER privilege to create or alter stored functions if binary logging is enabled, as
described in Section 23.7, “Binary Logging of Stored
Programs”.
Enables use of the KILL statement or mysqladmin
kill command to kill threads belonging to other accounts.
(You can always kill your own threads.)
The server accepts one connection from a SUPER client even if the connection limit controlled by the max_connections system variable is reached.
Updates can be performed even when the read_only system variable is enabled. This applies to
table updates and use of account-management statements such as GRANT and REVOKE.
The server does not execute init_connect system variable content when SUPER clients connect.
A server in offline mode (offline_mode enabled) does not terminate SUPER client connections at the next client request, and accepts new connections from
SUPER clients.
Enables configuration changes by modifying global system variables. For some
system variables, setting the session value also requires the SUPER privilege; if so, it is indicated in the variable description. Examples include
binlog_format, sql_log_bin, and sql_log_off.
Enables starting and stopping replication on slave servers, including Group
Replication.
Enables use of the CHANGE MASTER TO and
CHANGE REPLICATION
FILTER statements.
Enables binary log control by means of the PURGE BINARY LOGS and BINLOG statements.
Enables setting the effective authorization ID when executing a view or
stored program. A user with this privilege can specify any account in the DEFINER attribute of a view or stored program.
Enables use of the CREATE SERVER, ALTER SERVER, and DROP SERVER statements.
Enables use of the mysqladmin
debug command.
Enables InnoDB key rotation.
Enables reading the DES key file by the DES_ENCRYPT() function.
Enables execution of Version Tokens user-defined functions.
Enables control over client connections not permitted to non-SUPER accounts:
The TRIGGER privilege enables trigger operations. You must
have this privilege for a table to create, drop, execute, or display triggers
for that table.
When a trigger is activated (by a user who has privileges to execute INSERT, UPDATE, or DELETE statements for the table associated with the
trigger), trigger execution requires that the user who defined the trigger still
have the TRIGGER privilege.
The UPDATE privilege enables rows to be updated in tables
in a database.
The USAGE privilege specifier stands for “no
privileges.” It is used at the global level with GRANT to modify account attributes such as resource
limits or SSL characteristics without naming specific account privileges. SHOW GRANTS displays USAGE to indicate that an account has no privileges at a privilege level.
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:
