Permissible Privileges for GRANT and REVOKE

Table 6.2 Permissible Privileges for GRANT and REVOKE

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:

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