Permissible Privileges for GRANT and REVOKE

Table 6.2 Permissible Privileges for GRANT and REVOKE

<col>

Privilege

Column

Context

<code>ALL [PRIVILEGES]</code>

Synonym for “all

privileges”

Server administration

<code>ALTER</code>

<code>Alter_priv</code>

Tables

<code>ALTER ROUTINE</code>

<code>Alter_routine_priv</code>

Stored routines

<code>CREATE</code>

<code>Create_priv</code>

Databases, tables, or indexes

<code>CREATE ROUTINE</code>

<code>Create_routine_priv</code>

<code>CREATE TABLESPACE</code>

<code>Create_tablespace_priv</code>

<code>CREATE TEMPORARY TABLES</code>

<code>Create_tmp_table_priv</code>

<code>CREATE USER</code>

<code>Create_user_priv</code>

<code>CREATE VIEW</code>

<code>Create_view_priv</code>

Views

<code>DELETE</code>

<code>Delete_priv</code>

<code>DROP</code>

<code>Drop_priv</code>

Databases, tables, or views

<code>EVENT</code>

<code>Event_priv</code>

Databases

<code>EXECUTE</code>

<code>Execute_priv</code>

<code>FILE</code>

<code>File_priv</code>

File access on server host

<code>GRANT OPTION</code>

<code>Grant_priv</code>

Databases, tables, or stored routines

<code>INDEX</code>

<code>Index_priv</code>

<code>INSERT</code>

<code>Insert_priv</code>

Tables or columns

<code>LOCK TABLES</code>

<code>Lock_tables_priv</code>

<code>PROCESS</code>

<code>Process_priv</code>

<code>PROXY</code>

See <code>proxies_priv</code> table

<code>REFERENCES</code>

<code>References_priv</code>

Databases or tables

<code>RELOAD</code>

<code>Reload_priv</code>

<code>REPLICATION CLIENT</code>

<code>Repl_client_priv</code>

<code>REPLICATION SLAVE</code>

<code>Repl_slave_priv</code>

<code>SELECT</code>

<code>Select_priv</code>

<code>SHOW DATABASES</code>

<code>Show_db_priv</code>

<code>SHOW VIEW</code>

<code>Show_view_priv</code>

<code>SHUTDOWN</code>

<code>Shutdown_priv</code>

<code>SUPER</code>

<code>Super_priv</code>

<code>TRIGGER</code>

<code>Trigger_priv</code>

<code>UPDATE</code>

<code>Update_priv</code>

<code>USAGE</code>

Synonym for “no

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 <code>ALL</code> or

<code>ALL PRIVILEGES</code> privilege specifier is shorthand. It stands for “all privileges available at a given privilege

level” (except <code>GRANT OPTION</code>). For example, granting <code>ALL</code> at

the global or table level grants all global privileges or all table-level

privileges.

The <code>ALTER</code> privilege enables use of the <code>ALTER TABLE</code> statement to

change the structure of tables. <code>ALTER TABLE</code> also requires

the <code>CREATE</code> and <code>INSERT</code> privileges. Renaming a table requires <code>ALTER</code> and <code>DROP</code> on

the old table, <code>CREATE</code>, and <code>INSERT</code> on the new table.

The <code>ALTER ROUTINE</code> privilege is needed to alter or drop stored routines

(procedures and functions).

The <code>CREATE</code> privilege enables creation of new databases and

tables.

The <code>CREATE ROUTINE</code> privilege is needed to create stored routines (procedures and

functions).

The <code>CREATE TABLESPACE</code> privilege is needed to create, alter,

or drop tablespaces and log file groups.

The <code>CREATE TEMPORARY TABLES</code> privilege enables the creation

of temporary tables using the <code>CREATE TEMPORARY TABLE</code> 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 <code>DROP TABLE</code>, <code>INSERT</code>, <code>UPDATE</code>, or <code>SELECT</code>. For more information, see Section 13.1.18.3, “CREATE TEMPORARY TABLE

Syntax”.

The <code>CREATE USER</code> privilege enables use of the <code>ALTER USER</code>, <code>CREATE USER</code>, <code>DROP USER</code>, <code>RENAME USER</code>, and <code>REVOKE ALL PRIVILEGES</code> statements.

The <code>CREATE VIEW</code> privilege enables use of the <code>CREATE VIEW</code> statement.

The <code>DELETE</code> privilege enables rows to be deleted from tables

in a database.

The <code>DROP</code> privilege enables you to drop (remove) existing databases, tables, and views.

The <code>DROP</code> privilege is required in order to use the

statement <code>ALTER TABLE ... DROP PARTITION</code> on a

partitioned table. The <code>DROP</code> privilege is also required for <code>TRUNCATE TABLE</code>. If you grant

the <code>DROP</code> privilege for the <code>mysql</code> database to a user, that user can drop the database in which the MySQL access

privileges are stored.

The <code>EVENT</code> privilege is required to create, alter, drop, or see events for the Event

Scheduler.

The <code>EXECUTE</code> privilege is required to execute stored

routines (procedures and functions).

The <code>FILE</code> privilege gives you permission to read and write files on the server host using

the <code>LOAD DATA INFILE</code> and <code>SELECT ... INTO OUTFILE</code> statements and the <code>LOAD_FILE()</code> function. A user who has the <code>FILE</code> 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 <code>FILE</code> 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 <code>FILE</code> privilege is required to use the <code>DATA DIRECTORY</code> or

<code>INDEX DIRECTORY</code> table option for the <code>CREATE TABLE</code> statement.

To limit the location in which files can be read and written, set the <code>secure_file_priv</code> system to a specific directory. See Section 5.1.5, “Server System

Variables”.

The <code>GRANT OPTION</code> privilege enables you to give to other users or remove from

other users those privileges that you yourself possess.

The <code>INDEX</code> privilege enables you to create or drop (remove) indexes. <code>INDEX</code> applies to existing tables. If you have the <code>CREATE</code> privilege for a table, you can include index

definitions in the <code>CREATE TABLE</code> statement.

The <code>INSERT</code> privilege enables rows to be inserted into

tables in a database. <code>INSERT</code> is also required for the <code>ANALYZE TABLE</code>, <code>OPTIMIZE TABLE</code>, and <code>REPAIR TABLE</code> table-maintenance statements.

The <code>LOCK TABLES</code> privilege enables the use of explicit <code>LOCK TABLES</code> statements to

lock tables for which you have the <code>SELECT</code> privilege. This includes the use of write locks,

which prevents other sessions from reading the locked table.

The <code>PROCESS</code> 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 <code>SHOW PROCESSLIST</code> or

mysqladmin

processlist to see threads belonging to other accounts; you

can always see your own threads. The <code>PROCESS</code> privilege also enables use of <code>SHOW ENGINE</code>.

The <code>PROXY</code> privilege enables a user to impersonate or become known as another user. See Section 6.3.9,

“Proxy Users”.

The <code>REFERENCES</code> privilege is unused before MySQL 5.7.6. As

of 5.7.6, creation of a foreign key constraint requires the <code>REFERENCES</code> privilege for the parent table.

The <code>RELOAD</code> privilege enables use of the <code>FLUSH</code> statement. It also enables mysqladmin commands that are

equivalent to <code>FLUSH</code> operations: <code>flush-hosts</code>, <code>flush-logs</code>, <code>flush-privileges</code>, <code>flush-status</code>,

<code>flush-tables</code>, <code>flush-threads</code>, <code>refresh</code>, and

<code>reload</code>.

The <code>reload</code> command tells the server to reload the

grant tables into memory. <code>flush-privileges</code> is a

synonym for <code>reload</code>. The <code>refresh</code> command closes and reopens the log files and

flushes all tables. The other <code>flush-<code>xxx</code></code> commands perform functions

similar to <code>refresh</code>, but are more specific and may be

preferable in some instances. For example, if you want to flush just the log

files, <code>flush-logs</code> is a better choice than <code>refresh</code>.

The <code>REPLICATION CLIENT</code> privilege enables the use of the <code>SHOW MASTER STATUS</code>, <code>SHOW SLAVE STATUS</code>,

and <code>SHOW BINARY LOGS</code> statements.

The <code>REPLICATION SLAVE</code> 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 <code>SELECT</code> privilege enables you to select rows from tables

in a database. <code>SELECT</code> statements require the

<code>SELECT</code> privilege only if they actually retrieve rows

from a table. Some <code>SELECT</code> statements do not

access tables and can be executed without permission for any database. For

example, you can use <code>SELECT</code> as a simple calculator

to evaluate expressions that make no reference to tables:

The <code>SELECT</code> privilege is also needed for other statements

that read column values. For example, <code>SELECT</code> is needed for columns referenced on the right

hand side of <code>col_name</code>=<code>expr</code> assignment in <code>UPDATE</code> statements or for columns named in the <code>WHERE</code> clause of <code>DELETE</code> or <code>UPDATE</code> statements.

The <code>SELECT</code> privilege is also needed for tables or views

being used with <code>EXPLAIN</code>, including any

underlying tables of views.

The <code>SHOW DATABASES</code> privilege enables the account to see database names by

issuing the <code>SHOW DATABASE</code> 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 <code>--skip-show-database</code> option. Note that any global privilege is a privilege for the

database.

The <code>SHOW VIEW</code> privilege enables use of the <code>SHOW CREATE VIEW</code> statement. This privilege is also

needed for views being used with <code>EXPLAIN</code>.

The <code>SHUTDOWN</code> privilege enables use of the <code>SHUTDOWN</code> statement, the mysqladmin

shutdown command, and the <code>mysql_shutdown()</code> C API function.

The <code>SUPER</code> privilege enables these operations and server behaviors:

You may also need the <code>SUPER</code> 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 <code>KILL</code> 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 <code>SUPER</code> client even if the connection limit controlled by the <code>max_connections</code> system variable is reached.

Updates can be performed even when the <code>read_only</code> system variable is enabled. This applies to

table updates and use of account-management statements such as <code>GRANT</code> and <code>REVOKE</code>.

The server does not execute <code>init_connect</code> system variable content when <code>SUPER</code> clients connect.

A server in offline mode (<code>offline_mode</code> enabled) does not terminate <code>SUPER</code> client connections at the next client request, and accepts new connections from

<code>SUPER</code> clients.

Enables configuration changes by modifying global system variables. For some

system variables, setting the session value also requires the <code>SUPER</code> privilege; if so, it is indicated in the variable description. Examples include

<code>binlog_format</code>, <code>sql_log_bin</code>, and <code>sql_log_off</code>.

Enables starting and stopping replication on slave servers, including Group

Replication.

Enables use of the <code>CHANGE MASTER TO</code> and

<code>CHANGE REPLICATION FILTER</code> statements.

Enables binary log control by means of the <code>PURGE BINARY LOGS</code> and <code>BINLOG</code> 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 <code>DEFINER</code> attribute of a view or stored program.

Enables use of the <code>CREATE SERVER</code>, <code>ALTER SERVER</code>, and <code>DROP SERVER</code> statements.

Enables use of the mysqladmin

debug command.

Enables <code>InnoDB</code> key rotation.

Enables reading the DES key file by the <code>DES_ENCRYPT()</code> function.

Enables execution of Version Tokens user-defined functions.

Enables control over client connections not permitted to non-<code>SUPER</code> accounts:

The <code>TRIGGER</code> 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 <code>INSERT</code>, <code>UPDATE</code>, or <code>DELETE</code> statements for the table associated with the

trigger), trigger execution requires that the user who defined the trigger still

have the <code>TRIGGER</code> privilege.

The <code>UPDATE</code> privilege enables rows to be updated in tables

The <code>USAGE</code> privilege specifier stands for “no

privileges.” It is used at the global level with <code>GRANT</code> to modify account attributes such as resource

limits or SSL characteristics without naming specific account privileges. <code>SHOW GRANTS</code> displays <code>USAGE</code> 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 <code>FILE</code> and administrative privileges:

The <code>FILE</code> 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 <code>SELECT</code> to transfer its

contents to the client host.

The <code>GRANT OPTION</code> privilege enables users to give their

privileges to other users. Two users that have different privileges and with the

<code>GRANT OPTION</code> privilege are able to combine privileges.

The <code>ALTER</code> privilege may be used to subvert the privilege

system by renaming tables.

The <code>SHUTDOWN</code> privilege can be abused to deny service to

other users entirely by terminating the server.

The <code>PROCESS</code> privilege can be used to view the plain text of

currently executing statements, including statements that set or change

passwords.

The <code>SUPER</code> privilege can be used to terminate other sessions

or change how the server operates.

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

using that account.