Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.3Mb
PDF (A4) - 33.3Mb
PDF (RPM) - 31.3Mb
HTML Download (TGZ) - 7.9Mb
HTML Download (Zip) - 8.0Mb
HTML Download (RPM) - 6.8Mb
Man Pages (TGZ) - 144.9Kb
Man Pages (Zip) - 205.9Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb


MySQL 8.0 Reference Manual  /  ...  /  Privileges Provided by MySQL

Pre-General Availability Draft: 2017-09-20

6.2.1 Privileges Provided by MySQL

The privileges granted to a MySQL account determine which operations the account can perform. MySQL privileges differ in the contexts in which they apply 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).

Privileges also differ in terms of whether they are static (built in to the server) or dynamic (defined at runtime). Whether a privilege is static or dynamic affects its availability to be granted to user accounts and roles. See Section 6.2.2, “Static Versus Dynamic Privileges”.

Information about account privileges is stored in the user, db, tables_priv, columns_priv, procs_priv, and global_grants tables in the mysql system database (see Section 6.2.3, “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.8, “When Privilege Changes Take Effect”. Access-control decisions are based on the in-memory copies of the grant tables.

Some MySQL releases 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 upgrade MySQL. See Section 4.4.5, “mysql_upgrade — Check and Upgrade MySQL Tables”.

The following tables show the static and dynamic privilege names used in 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 Static Privileges for GRANT and REVOKE

PrivilegeColumnContext
ALL [PRIVILEGES]Synonym for all privilegesServer administration
ALTERAlter_privTables
ALTER ROUTINEAlter_routine_privStored routines
CREATECreate_privDatabases, tables, or indexes
CREATE ROLECreate_role_privServer administration
CREATE ROUTINECreate_routine_privStored routines
CREATE TABLESPACECreate_tablespace_privServer administration
CREATE TEMPORARY TABLESCreate_tmp_table_privTables
CREATE USERCreate_user_privServer administration
CREATE VIEWCreate_view_privViews
DELETEDelete_privTables
DROPDrop_privDatabases, tables, or views
DROP ROLEDrop_role_privServer administration
EVENTEvent_privDatabases
EXECUTEExecute_privStored routines
FILEFile_privFile access on server host
GRANT OPTIONGrant_privDatabases, tables, or stored routines
INDEXIndex_privTables
INSERTInsert_privTables or columns
LOCK TABLESLock_tables_privDatabases
PROCESSProcess_privServer administration
PROXYSee proxies_priv tableServer administration
REFERENCESReferences_privDatabases or tables
RELOADReload_privServer administration
REPLICATION CLIENTRepl_client_privServer administration
REPLICATION SLAVERepl_slave_privServer administration
SELECTSelect_privTables or columns
SHOW DATABASESShow_db_privServer administration
SHOW VIEWShow_view_privViews
SHUTDOWNShutdown_privServer administration
SUPERSuper_privServer administration
TRIGGERTrigger_privTables
UPDATEUpdate_privTables or columns
USAGESynonym for no privilegesServer administration

Table 6.3 Permissible Dynamic Privileges for GRANT and REVOKE

PrivilegeColumnContext
AUDIT_ADMINSee global_grants tableServer administration
BACKUP_ADMINSee global_grants tableServer administration
BINLOG_ADMINSee global_grants tableServer administration
CONNECTION_ADMINSee global_grants tableServer administration
ENCRYPTION_KEY_ADMINSee global_grants tableServer administration
FIREWALL_ADMINSee global_grants tableServer administration
FIREWALL_USERSee global_grants tableServer administration
GROUP_REPLICATION_ADMINSee global_grants tableServer administration
PERSIST_RO_VARIABLES_ADMINSee global_grants tableServer administration
REPLICATION_SLAVE_ADMINSee global_grants tableServer administration
ROLE_ADMINSee global_grants tableServer administration
SET_USER_IDSee global_grants tableServer administration
SYSTEM_VARIABLES_ADMINSee global_grants tableServer administration
VERSION_TOKEN_ADMINSee global_grants tableServer administration
XA_RECOVER_ADMINSee global_grants tableServer administration

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:

  • The FILE privilege 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 SELECT to transfer its contents to the client host.

  • The GRANT OPTION privilege enables users to give their privileges to other users. Two users that have different privileges and with the GRANT OPTION privilege are able to combine privileges.

  • The ALTER privilege may be used to subvert the privilege system by renaming tables.

  • The SHUTDOWN privilege can be abused to deny service to other users entirely by terminating the server.

  • The PROCESS privilege can be used to view the plain text of currently executing statements, including statements that set or change passwords.

  • The SUPER privilege can be used to terminate other sessions or change how the server operates.

  • Privileges granted for the mysql database 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 user table authentication_string column can change an account's password, and then connect to the MySQL server using that account.

    The SELECT privilege is also needed for tables or views being used with EXPLAIN, including any underlying tables of views.

The following sections provide general descriptions of the static and dynamic privileges available in MySQL. (For information about the differences between these two types of privileges, see Section 6.2.2, “Static Versus Dynamic Privileges”.) Particular SQL statements might have more specific privilege requirements than indicated here. If so, the description for the statement in question provides the details.

Static Privileges

Static privileges are built in to the server, in contrast to dynamic privileges, which are defined at runtime. The following list describes the static privileges available in MySQL.

  • 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 ROLE privilege enables use of the CREATE ROLE statement. (The CREATE USER privilege also enables use of the CREATE ROLE statement.)

  • 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.16.3, “CREATE TEMPORARY TABLE Syntax”.

  • The CREATE USER privilege enables use of the ALTER USER, CREATE ROLE, CREATE USER, DROP ROLE, 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 DROP ROLE privilege enables use of the DROP ROLE statement. (The CREATE USER privilege also enables use of the DROP ROLE statement.)

  • 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. 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.11, “Proxy Users”.

  • The 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 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”.

  • 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.

Dynamic Privileges

Dynamic privileges are defined at runtime, in contrast to static privileges, which are built in to the server. The following list describes the dynamic privileges available in MySQL.

  • AUDIT_ADMIN: Enables audit log configuration.

  • BACKUP_ADMIN: Enables execution of the LOCK INSTANCE FOR BACKUP statement; introduced in MySQL 8.0.3.

    The BACKUP_ADMIN privilege is automatically granted to users with the RELOAD privilege when performing an in-place upgrade to MySQL 8.0 from an earlier version.

  • BINLOG_ADMIN: Enables binary log control by means of the PURGE BINARY LOGS and BINLOG statements. Defined at server startup.

  • CONNECTION_ADMIN: Enables setting system variables related to client connections, or circumventing restrictions related to client connections. Defined at server startup.

    CONNECTION_ADMIN applies to the effects of these system variables:

  • ENCRYPTION_KEY_ADMIN: Enables InnoDB encryption key rotation. Defined at server startup.

  • FIREWALL_ADMIN: Enables a user to administer firewall rules for any user.

  • FIREWALL_USER: Enables users to update their own firewall rules.

  • GROUP_REPLICATION_ADMIN: On a slave server, enables starting and stopping Group Replication. Defined at server startup.

  • PERSIST_RO_VARIABLES_ADMIN: Enables use of SET PERSIST_ONLY to persist global system variables to the mysqld-auto.cnf option file in the data directory. This statement is similar to SET PERSIST but does not modify the runtime global system variable value, making it suitable for configuring read-only system variables that can be set only at server startup. Defined at server startup.

  • REPLICATION_SLAVE_ADMIN: On a slave server, enables connecting to and disconnecting from the master server, starting and stopping replication, and use of the CHANGE MASTER TO and CHANGE REPLICATION FILTER statements. Defined at server startup. This privilege does not apply to Group Replication; use GROUP_REPLICATION_ADMIN for that.

  • ROLE_ADMIN: Enables use of the WITH ADMIN OPTION clause of the GRANT statement. Enables nonempty <graphml> element content in the result from the ROLES_GRAPHML() function. Defined at server startup.

  • SET_USER_ID: 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. Defined at server startup.

  • SYSTEM_VARIABLES_ADMIN: Enables configuration changes by modifying or persisting global system variables. For some system variables, setting the session value also requires the SYSTEM_VARIABLES_ADMIN privilege; if so, it is indicated in the variable description. Examples include binlog_format, sql_log_bin, and sql_log_off. Defined at server startup.

  • VERSION_TOKEN_ADMIN: Enables execution of Version Tokens user-defined functions. Defined by the version_tokens plugin; see Section 5.6.4, “Version Tokens”.

  • XA_RECOVER_ADMIN: Enables execution of the XA RECOVER statement; see Section 13.3.8.1, “XA Transaction SQL Syntax”. Defined at server startup.

    Prior to MySQL 8.0, any user could execute the XA RECOVER statement to discover the XID values for outstanding prepared XA transactions, possibly leading to commit or rollback of an XA transaction by a user other than the one who started it. In MySQL 8.0, XA RECOVER is permitted only to users who have the XA_RECOVER_ADMIN privilege, which is expected to be granted only to administrative users who have need for it. This might be the case, for example, for administrators of an XA application if it has crashed and it is necessary to find outstanding transactions started by the application so they can be rolled back. This privilege requirement prevents users from discovering the XID values for outstanding prepared XA transactions other than their own. It does not affect normal commit or rollback of an XA transaction because the user who started it knows its XID.


User Comments
  Posted by Dietrich Feist on November 25, 2003
One workaround to give users permissions on temporary tables that you don't want to give them on regular tables is the following. We just have to keep in mind that users have the same access rights on temporary tables that they have on all tables in a particular database:

1) create a dedicated database for temporary tables:

mysql> CREATE DATABASE tmp;

2) Give your users all the access privileges that they need to create and use temporary tables:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, DROP, ALTER, CREATE TEMPORARY TABLES ON tmp.* TO user@localhost;

Be sure that you do not give them CREATE or GRANT privileges!

3) Have you users create all temporary tables in that 'tmp' database instead of the current database:

mysql> USE mydb
mysql> CREATE TEMPORARY TABLE tmp.dummy SELECT * from mytable;

Your users have to explicitly call their temporary tables as tmp.<tablename> in all requests. There is no problem if two users use the same name for a temporary table since they will not be able to see each other's temporary tables. You can also put the 'tmp' database on a dedicated disk.
  Posted by Randy Austin on August 25, 2009
One side-effect of priv_super is that users with priv_super are allowed to write to the database, regardless of the setting of the read_only global variables.

  Posted by David Tonhofer on December 24, 2010
A little query to write the wide privilege table out in narrower form:

SELECT password, host, user,
CONCAT(Select_priv, Lock_tables_priv) AS selock,
CONCAT(Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv) AS modif,
CONCAT(Grant_priv, References_priv, Index_priv, Alter_priv) AS meta,
CONCAT(Create_tmp_table_priv, Create_view_priv, Show_view_priv) AS views,
CONCAT(Create_routine_priv, Alter_routine_priv, Execute_priv) AS funcs,
CONCAT(Repl_slave_priv, Repl_client_priv) AS replic,
CONCAT(Super_priv, Shutdown_priv, Process_priv, File_priv, Show_db_priv, Reload_priv) AS admin
FROM USER ORDER BY user, host;

+-------------------------------------------+-----------+--------+--------+-------+------+-------+-------+--------+--------+
| password | host | user | selock | modif | meta | views | funcs | replic | admin |
+-------------------------------------------+-----------+--------+--------+-------+------+-------+-------+--------+--------+
| *......... | localhost | backup | YY | NNNNN | NNNN | NNN | NNN | NN | NNNNNN |
| *......... | localhost | nagios | XX | NNNNN | NNNN | NNN | NNN | NN | NNNNNN |
| *......... | 127.0.0.1 | root | YY | YYYYY | YYYY | YYY | YYY | YY | YYYYYY |
| *......... | localhost | root | YY | YYYYY | YYYY | YYY | YYY | YY | YYYYYY |
| | localhost | wheel | NY | NNNNN | NNNN | NNN | NNN | NN | NNNNNY |
+-------------------------------------------+-----------+--------+--------+-------+------+-------+-------+--------+--------+

  Posted by Eli Skoczylas on November 6, 2012
The FILE privilege can not be restricted to a single table, so the syntax for it is:

GRANT FILE ON *.* TO 'username'@'host'....

Hope that saves someone else from having to dig for the answer.
  Posted by Tss Tss on November 8, 2012
Please note that "escape" clause doesnot work in view

If you have WHERE condition " like 'ABC/_%' escape '/' " and you mean select string like 'ABC_'+'something' you'll suddenly find that you got 'ABC'+'something' instead.

  Posted by Jörg Brühe on May 12, 2014
We just found that a user account needs the "process" privilege to collect performance values from the MySQL server. In our case, these are values for Graphite/Icinga, as provided by "show status".
  Posted by Peter Burns on January 26, 2015
Note that although REFERENCES privilege is currently "unused", granting it on a table allows the user to query the information_schema database for column names etc. We found this useful for creating database documentation from the schema without needing to grant even SELECT privilege to the user (our wiki, in fact).
Sign Up Login You must be logged in to post a comment.