8.2.2 Privileges Provided by MySQL

Skip to Main Content

MySQL 8.4 Reference Manual Including MySQL NDB Cluster 8.4

8.2.2 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. For information about the differences between static and dynamic privileges, see Static Versus Dynamic Privileges.)

Information about account privileges is stored in the grant tables in the mysql system database. For a description of the structure and contents of these tables, see Section 8.2.3, “Grant Tables”. The MySQL server reads the contents of the grant tables into memory when it starts, and reloads them under the circumstances indicated in Section 8.2.13, “When Privilege Changes Take Effect”. The server bases access-control decisions on the in-memory copies of the grant tables.

Important

Some MySQL releases introduce changes to 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 the current structure whenever you upgrade MySQL. See Chapter 3, Upgrading MySQL.

The following sections summarize the available privileges, provide more detailed descriptions of each privilege, and offer usage guidelines.

• Summary of Available Privileges
• Static Privilege Descriptions
• Dynamic Privilege Descriptions
• Privilege-Granting Guidelines
• Static Versus Dynamic Privileges
• Migrating Accounts from SUPER to Dynamic Privileges

Summary of Available Privileges

The following table shows the static 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 8.2 Permissible Static Privileges for GRANT and REVOKE

The following table shows the dynamic privilege names used in GRANT and REVOKE statements, along with the context in which the privilege applies.

Table 8.3 Permissible Dynamic Privileges for GRANT and REVOKE

Static Privilege Descriptions

Static privileges are built in to the server, in contrast to dynamic privileges, which are defined at runtime. The following list describes each static 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, ALL PRIVILEGES

These privilege specifiers are shorthand 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, respectively. - ALTER

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. - ALTER ROUTINE

Enables use of statements that alter or drop stored routines (stored procedures and functions). For routines that fall within the scope at which the privilege is granted and for which the user is not the user named as the routine DEFINER, also enables access to routine properties other than the routine definition. - CREATE

Enables use of statements that create new databases and tables. - CREATE ROLE

Enables use of the CREATE ROLE statement. (The CREATE USER privilege also enables use of the CREATE ROLE statement.) See Section 8.2.10, “Using Roles”.

The CREATE ROLE and DROP ROLE privileges are not as powerful as CREATE USER because they can be used only to create and drop accounts. They cannot be used as CREATE USER can be modify account attributes or rename accounts. See User and Role Interchangeability. - CREATE ROUTINE

Enables use of statements that create stored routines (stored procedures and functions). For routines that fall within the scope at which the privilege is granted and for which the user is not the user named as the routine DEFINER, also enables access to routine properties other than the routine definition. - CREATE TABLESPACE

Enables use of statements that create, alter, or drop tablespaces and log file groups. - CREATE TEMPORARY TABLES

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 15.1.20.2, “CREATE TEMPORARY TABLE Statement”. - CREATE USER

Enables use of the ALTER USER, CREATE ROLE, CREATE USER, DROP ROLE, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES statements. - CREATE VIEW

Enables use of the CREATE VIEW statement. - DELETE

Enables rows to be deleted from tables in a database. - DROP

Enables use of statements that drop (remove) existing databases, tables, and views. The DROP privilege is required to use the ALTER TABLE ... DROP PARTITION statement on a partitioned table. The DROP privilege is also required for TRUNCATE TABLE. - DROP ROLE

Enables use of the DROP ROLE statement. (The CREATE USER privilege also enables use of the DROP ROLE statement.) See Section 8.2.10, “Using Roles”.

The CREATE ROLE and DROP ROLE privileges are not as powerful as CREATE USER because they can be used only to create and drop accounts. They cannot be used as CREATE USER can be modify account attributes or rename accounts. See User and Role Interchangeability. - EVENT

Enables use of statements that create, alter, drop, or display events for the Event Scheduler. - EXECUTE

Enables use of statements that execute stored routines (stored procedures and functions). For routines that fall within the scope at which the privilege is granted and for which the user is not the user named as the routine DEFINER, also enables access to routine properties other than the routine definition. - FILE

Affects the following operations and server behaviors:

• Enables reading and writing files on the server host using the LOAD DATA 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.)
• Enables creating 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.
• Enables use of the DATA DIRECTORY or INDEX DIRECTORY table option for the CREATE TABLE statement.

As a security measure, the server does not overwrite existing files.

To limit the location in which files can be read and written, set the secure_file_priv system variable to a specific directory. See Section 7.1.8, “Server System Variables”. - GRANT OPTION

Enables you to grant to or revoke from other users those privileges that you yourself possess. - INDEX

Enables use of statements that 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. - INSERT

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. - LOCK TABLES

Enables use of explicit LOCK TABLES statements to lock tables for which you have the SELECT privilege. This includes use of write locks, which prevents other sessions from reading the locked table. - PROCESS

The PROCESS privilege controls access to information about threads executing within the server (that is, information about statements being executed by sessions). Thread information available using the SHOW PROCESSLIST statement, the mysqladmin processlist command, the Information Schema PROCESSLIST table, and the Performance Schema processlist table is accessible as follows:

• With the PROCESS privilege, a user has access to information about all threads, even those belonging to other users.
• Without the PROCESS privilege, nonanonymous users have access to information about their own threads but not threads for other users, and anonymous users have no access to thread information.

Note

The Performance Schema threads table also provides thread information, but table access uses a different privilege model. See Section 29.12.22.8, “The threads Table”.

The PROCESS privilege also enables use of the SHOW ENGINE statement, access to the INFORMATION_SCHEMA InnoDB tables (tables with names that begin with INNODB_), and access to the INFORMATION_SCHEMA FILES table. - PROXY

Enables one user to impersonate or become known as another user. See Section 8.2.19, “Proxy Users”. - REFERENCES

Creation of a foreign key constraint requires the REFERENCES privilege for the parent table. - RELOAD

The RELOAD enables the following operations:

• Use of the FLUSH statement.

• Use of mysqladmin commands that are equivalent to FLUSH operations: flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, 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. - Use of mysqldump options that perform various FLUSH operations: --flush-logs and --source-data. - Use of the RESET BINARY LOGS AND GTIDS and RESET REPLICA statements. - REPLICATION CLIENT

Enables use of the SHOW BINARY LOG STATUS, SHOW REPLICA STATUS, and SHOW BINARY LOGS statements. - REPLICATION SLAVE

Enables the account to request updates that have been made to databases on the replication source server, using the SHOW REPLICAS, SHOW RELAYLOG EVENTS, and SHOW BINLOG EVENTS statements. This privilege is also required to use the mysqlbinlog options --read-from-remote-server (-R) and --read-from-remote-source. Grant this privilege to accounts that are used by replicas to connect to the current server as their replication source server. - SELECT

Enables rows to be selected from tables in a database. SELECT statements require the SELECT privilege only if they actually access tables. 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 needed for tables or views used with EXPLAIN, including any underlying tables in view definitions. - SHOW DATABASES

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.

Caution

Because any static global privilege is considered a privilege for all databases, any static global privilege enables a user to see all database names with SHOW DATABASES or by examining the SCHEMATA table of INFORMATION_SCHEMA, except databases that have been restricted at the database level by partial revokes. - SHOW VIEW

Enables use of the SHOW CREATE VIEW statement. This privilege is also needed for views used with EXPLAIN. - SHUTDOWN

Enables use of the SHUTDOWN and RESTART statements, the mysqladmin shutdown command, and the mysql_shutdown() C API function. - SUPER

SUPER is a powerful and far-reaching privilege and should not be granted lightly. If an account needs to perform only a subset of SUPER operations, it may be possible to achieve the desired privilege set by instead granting one or more dynamic privileges, each of which confers more limited capabilities. See Dynamic Privilege Descriptions.

Note

SUPER is deprecated, and you should expect it to be removed in a future version of MySQL. See Migrating Accounts from SUPER to Dynamic Privileges.

SUPER affects the following operations and server behaviors:

• Enables system variable changes at runtime:

  • Enables server configuration changes to global system variables with SET GLOBAL and SET PERSIST.

  The corresponding dynamic privilege is SYSTEM_VARIABLES_ADMIN. - Enables setting restricted session system variables that require a special privilege.

  The corresponding dynamic privilege is SESSION_VARIABLES_ADMIN.

  See also Section 7.1.9.1, “System Variable Privileges”. - Enables changes to global transaction characteristics (see Section 15.3.7, “SET TRANSACTION Statement”).

  The corresponding dynamic privilege is SYSTEM_VARIABLES_ADMIN. - Enables the account to start and stop replication, including Group Replication.

  The corresponding dynamic privilege is REPLICATION_SLAVE_ADMIN for regular replication, GROUP_REPLICATION_ADMIN for Group Replication. - Enables use of CHANGE REPLICATION SOURCE TO and CHANGE REPLICATION FILTER statements.

  The corresponding dynamic privilege is REPLICATION_SLAVE_ADMIN. - Enables binary log control by means of the PURGE BINARY LOGS and BINLOG statements.

  The corresponding dynamic privilege is BINLOG_ADMIN. - 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.

  The corresponding dynamic privileges are SET_ANY_DEFINER and ALLOW_NONEXISTENT_DEFINER. - Enables use of the CREATE SERVER, ALTER SERVER, and DROP SERVER statements. - Enables use of the mysqladmin debug command. - Enables InnoDB encryption key rotation.

  The corresponding dynamic privilege is ENCRYPTION_KEY_ADMIN. - Enables execution of Version Tokens functions.

  The c

（本文超出站点单文档体积上限，已截断）