文档首页
MySQL 8.4 参考手册
8.4 中文镜像

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

打开官方英文原文 ↗

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 81%

8.2.2 MySQL 提供的权限8.2.2 Privileges Provided by MySQL

授予 MySQL 帐户的权限决定了该帐户可以执行哪些操作。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:

  • 管理权限使用户能够管理 MySQL 服务器的操作。这些权限是全局的，因为它们并不针对特定数据库。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.)

关于账户权限的信息存储在 mysql 系统数据库的授权表中。有关这些表的结构和内容的描述，请参见 第 8.2.3 节，“授权表”。MySQL 服务器在启动时会将授权表的内容读取到内存中，并在 第 8.2.13 节，“权限更改生效时间” 指示的情况下重新加载它们。服务器基于授权表的内存副本进行访问控制决策。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

一些 MySQL 版本在授权表中引入了更改，以添加新的权限或功能。为了确保您能够利用任何新功能，每当升级 MySQL 时，请将您的授权表更新到当前结构。请参阅 第 3 章，升级 MySQL。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

下表显示了在GRANT和REVOKE语句中使用的静态权限名称，以及与每个权限相关的授权表中的列名和该权限适用的上下文。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.

表 8.2 GRANT 和 REVOKE 允许的静态权限Table 8.2 Permissible Static Privileges for GRANT and REVOKE

权限Privilege 授予表列Grant Table Column 上下文Context
ALL [PRIVILEGES]ALL [PRIVILEGES] 同义词为“所有权限”Synonym for “all privileges” 服务器管理Server administration
ALTERALTER Alter_priv 表格Tables
ALTER ROUTINE Alter_routine_priv 存储过程Stored routines
CREATECREATE Create_privCreate_priv 数据库、表或索引Databases, tables, or indexes
CREATE ROLECREATE ROLE Create_role_privCreate_role_priv 服务器管理Server administration
CREATE ROUTINECREATE ROUTINE Create_routine_privCreate_routine_priv 存储过程Stored routines
CREATE TABLESPACECREATE TABLESPACE Create_tablespace_privCreate_tablespace_priv 服务器管理Server administration
CREATE TEMPORARY TABLESCREATE TEMPORARY TABLES Create_tmp_table_privCreate_tmp_table_priv 表格Tables
CREATE USERCREATE USER Create_user_privCreate_user_priv 服务器管理Server administration
CREATE VIEWCREATE VIEW Create_view_priv 浏览量Views
DELETEDELETE Delete_priv 表格Tables
DROPDROP Drop_priv 数据库、表或视图Databases, tables, or views
DROP ROLEDROP ROLE Drop_role_privDrop_role_priv 服务器管理Server administration
EVENTEVENT Event_privEvent_priv 数据库Databases
EXECUTEEXECUTE Execute_privExecute_priv 存储过程Stored routines
FILEFILE File_privFile_priv 服务器主机上的文件访问File access on server host
GRANT OPTIONGRANT OPTION Grant_privGrant_priv 数据库、表或存储过程Databases, tables, or stored routines
INDEXINDEX Index_priv 表格Tables
INSERTINSERT Insert_priv 表格或列Tables or columns
LOCK TABLESLOCK TABLES Lock_tables_priv 数据库Databases
PROCESSPROCESS Process_priv 服务器管理Server administration
PROXYPROXY 参见 proxies_priv 表See proxies_priv table 服务器管理Server administration
REFERENCESREFERENCES References_priv 数据库或表格Databases or tables
RELOAD Reload_priv 服务器管理Server administration
REPLICATION CLIENT Repl_client_priv 服务器管理Server administration
REPLICATION SLAVEREPLICATION SLAVE Repl_slave_priv 服务器管理Server administration
SELECTSELECT Select_priv 表格或列Tables or columns
SHOW DATABASESSHOW DATABASES Show_db_priv 服务器管理Server administration
SHOW VIEWSHOW VIEW Show_view_privShow_view_priv 浏览量Views
SHUTDOWNSHUTDOWN Shutdown_privShutdown_priv 服务器管理Server administration
SUPERSUPER Super_privSuper_priv 服务器管理Server administration
TRIGGERTRIGGER Trigger_privTrigger_priv 表格Tables
UPDATEUPDATE Update_privUpdate_priv 表格或列Tables or columns
USAGEUSAGE 同义词为“无权限”Synonym for “no privileges” 服务器管理Server administration

下表显示了在GRANT和REVOKE语句中使用的动态权限名称，以及权限适用的上下文。The following table shows the dynamic privilege names used in GRANT and REVOKE statements, along with the context in which the privilege applies.

表 8.3 GRANT 和 REVOKE 的允许动态权限Table 8.3 Permissible Dynamic Privileges for GRANT and REVOKE

权限Privilege 上下文Context
ALLOW_NONEXISTENT_DEFINER 孤立对象保护Orphan object protection
APPLICATION_PASSWORD_ADMIN 双重密码管理Dual password administration
AUDIT_ABORT_EXEMPTAUDIT_ABORT_EXEMPT 允许被审计日志过滤器阻止的查询Allow queries blocked by audit log filter
AUDIT_ADMINAUDIT_ADMIN 审计日志管理Audit log administration
AUTHENTICATION_POLICY_ADMIN 认证管理Authentication administration
BACKUP_ADMINBACKUP_ADMIN 备份管理Backup administration
BINLOG_ADMINBINLOG_ADMIN 备份与复制管理Backup and Replication administration
BINLOG_ENCRYPTION_ADMINBINLOG_ENCRYPTION_ADMIN 备份与复制管理Backup and Replication administration
CLONE_ADMINCLONE_ADMIN 克隆管理Clone administration
CONNECTION_ADMINCONNECTION_ADMIN 服务器管理Server administration
ENCRYPTION_KEY_ADMINENCRYPTION_KEY_ADMIN 服务器管理Server administration
FIREWALL_ADMINFIREWALL_ADMIN 防火墙管理Firewall administration
FIREWALL_EXEMPTFIREWALL_EXEMPT 防火墙管理Firewall administration
FIREWALL_USERFIREWALL_USER 防火墙管理Firewall administration
FLUSH_OPTIMIZER_COSTSFLUSH_OPTIMIZER_COSTS 服务器管理Server administration
FLUSH_PRIVILEGESFLUSH_PRIVILEGES 服务器管理Server administration
FLUSH_STATUSFLUSH_STATUS 服务器管理Server administration
FLUSH_TABLESFLUSH_TABLES 服务器管理Server administration
FLUSH_USER_RESOURCESFLUSH_USER_RESOURCES 服务器管理Server administration
GROUP_REPLICATION_ADMIN 复制管理Replication administration
GROUP_REPLICATION_STREAM 复制管理Replication administration
INNODB_REDO_LOG_ARCHIVE 重做日志归档管理Redo log archiving administration
INNODB_REDO_LOG_ENABLE 重做日志管理Redo log administration
MASKING_DICTIONARIES_ADMIN 服务器管理Server administration
NDB_STORED_USER NDB 集群NDB Cluster
OPTIMIZE_LOCAL_TABLE OPTIMIZE LOCAL TABLE 语句OPTIMIZE LOCAL TABLE statements
PASSWORDLESS_USER_ADMIN 认证管理Authentication administration
PERSIST_RO_VARIABLES_ADMIN 服务器管理Server administration
REPLICATION_APPLIER PRIVILEGE_CHECKS_USER 用于复制通道PRIVILEGE_CHECKS_USER for a replication channel
REPLICATION_SLAVE_ADMIN 复制管理Replication administration
RESOURCE_GROUP_ADMINRESOURCE_GROUP_ADMIN 资源组管理Resource group administration
RESOURCE_GROUP_USER 资源组管理Resource group administration
ROLE_ADMIN 服务器管理Server administration
SENSITIVE_VARIABLES_OBSERVER 服务器管理Server administration
SESSION_VARIABLES_ADMIN 服务器管理Server administration
SET_ANY_DEFINER 服务器管理Server administration
SHOW_ROUTINE 服务器管理Server administration
SKIP_QUERY_REWRITE 服务器管理Server administration
SYSTEM_USERSYSTEM_USER 服务器管理Server administration
SYSTEM_VARIABLES_ADMINSYSTEM_VARIABLES_ADMIN 服务器管理Server administration
TABLE_ENCRYPTION_ADMINTABLE_ENCRYPTION_ADMIN 服务器管理Server administration
TELEMETRY_LOG_ADMINTELEMETRY_LOG_ADMIN 在 AWS 上管理 MySQL HeatWave 的遥测日志Telemetry log administration for MySQL HeatWave on AWS
TP_CONNECTION_ADMIN 线程池管理Thread pool administration
TRANSACTION_GTID_TAG 复制管理Replication administration
VERSION_TOKEN_ADMIN 服务器管理Server administration
XA_RECOVER_ADMIN 服务器管理Server administration

静态权限描述Static Privilege Descriptions

静态权限是内置在服务器中的，与动态权限形成对比，动态权限是在运行时定义的。以下列表描述了 MySQL 中可用的每个静态权限。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.

某些特定的 SQL 语句可能有比此处所示更具体的权限要求。如果是这样，相关语句的描述将提供详细信息。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 ALL, ALL PRIVILEGES

    这些权限说明符是“在给定权限级别下可用的所有权限”的简写（除了GRANT OPTION）。例如，在全局或表级授予ALL，分别授予所有全局权限或所有表级权限。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 ALTER

    启用使用 ALTER TABLE 语句来更改表的结构。ALTER TABLE 也需要 CREATE 和 INSERT 权限。重命名表需要在旧表上具有 ALTER 和 DROP 权限，在新表上具有 CREATE 和 INSERT 权限。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 ALTER ROUTINE

    允许使用修改或删除存储例程（存储过程和函数）的语句。对于在授予权限的范围内的例程，并且用户不是被指定为例程 DEFINER 的用户，还允许访问例程定义之外的例程属性。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 CREATE

    启用使用创建新数据库和表的语句。Enables use of statements that create new databases and tables.

  • CREATE ROLE CREATE ROLE

    允许使用 CREATE ROLE 语句。（CREATE ROLE 权限也允许使用 CREATE USER 语句。）参见 第 8.2.10 节，“使用角色”。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”.

    CREATE ROLE 和 DROP ROLE 权限没有 CREATE USER 权限强大，因为它们只能用于创建和删除账户。它们不能像 CREATE USER 那样修改账户属性或重命名账户。参见 用户和角色的可互换性。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

    允许使用创建存储例程（存储过程和函数）的语句。对于在授予权限范围内的例程，并且用户不是指定为例程DEFINER的用户，也允许访问除例程定义之外的例程属性。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 CREATE TABLESPACE

    允许使用创建、修改或删除表空间和日志文件组的语句。Enables use of statements that create, alter, or drop tablespaces and log file groups.

  • CREATE TEMPORARY TABLES CREATE TEMPORARY TABLES

    允许使用 CREATE TEMPORARY TABLE 语句创建临时表。Enables the creation of temporary tables using the CREATE TEMPORARY TABLE statement.

    在会话创建临时表之后，服务器不会对该表进行进一步的权限检查。创建会话可以对该表执行任何操作，例如 DROP TABLE、INSERT、UPDATE 或 SELECT。欲了解更多信息，请参见 第 15.1.20.2 节，“CREATE TEMPORARY TABLE 语句”。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

    启用使用 ALTER USER、CREATE ROLE、CREATE USER、DROP ROLE、DROP USER、RENAME USER 和 REVOKE ALL PRIVILEGES 语句。Enables use of the ALTER USER, CREATE ROLE, CREATE USER, DROP ROLE, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES statements.

  • CREATE VIEW CREATE VIEW

    允许使用 CREATE VIEW 语句。Enables use of the CREATE VIEW statement.

  • DELETE DELETE

    允许从数据库中的表删除行。Enables rows to be deleted from tables in a database.

  • DROP

    允许使用删除（移除）现有数据库、表和视图的语句。使用分区表上的 ALTER TABLE ... DROP PARTITION 语句需要 DROP 权限。使用 TRUNCATE TABLE 语句也需要 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

    启用使用 DROP ROLE 语句。（CREATE USER 权限也能启用使用 DROP ROLE 语句。）请参阅 第 8.2.10 节，“使用角色”。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”.

    CREATE ROLE 和 DROP ROLE 权限没有 CREATE USER 权限强大，因为它们只能用于创建和删除账户。它们不能像 CREATE USER 那样修改账户属性或重命名账户。参见 用户和角色的可互换性。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

    启用使用执行存储程序（存储过程和函数）的语句。对于在授予权限的范围内且用户不是指定为程序 DEFINER 的用户的程序，还可以访问除程序定义以外的程序属性。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:

    • 启用使用 LOAD DATA 和 SELECT ... INTO OUTFILE 语句以及 LOAD_FILE() 函数在服务器主机上读写文件。拥有 FILE 权限的用户可以读取服务器主机上任何对所有人可读或对 MySQL 服务器可读的文件。（这意味着用户可以读取任何数据库目录中的任何文件，因为服务器可以访问这些文件中的任何一个。）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.)

    • 允许在 MySQL 服务器具有写入权限的任何目录中创建新文件。这包括包含实现权限表的文件的服务器数据目录。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.

    • 启用在 CREATE TABLE 语句中使用 DATA DIRECTORY 或 INDEX DIRECTORY 表选项。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.

    要限制文件读取和写入的位置，请将 secure_file_priv 系统变量设置为特定目录。参见 第 7.1.8 节，“服务器系统变量”。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 INDEX

    允许使用创建或删除（移除）索引的语句。INDEX适用于现有表。如果你拥有表的CREATE权限，你可以在CREATE TABLE语句中包含索引定义。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 INSERT

    允许向数据库中的表插入行。INSERT 也需要用于 ANALYZE TABLE、OPTIMIZE TABLE 和 REPAIR TABLE 表维护语句。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

    允许使用显式的 LOCK TABLES 语句锁定你拥有 SELECT 权限的表。这包括使用写锁，这会阻止其他会话读取被锁定的表。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

    PROCESS 权限控制对服务器中正在执行的线程信息的访问（即会话正在执行的语句的信息）。可通过以下方式访问线程信息：使用 SHOW PROCESSLIST 语句、mysqladmin processlist 命令、信息模式 PROCESSLIST 表以及性能模式 processlist 表，如下所示：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:

    • 拥有PROCESS权限的用户可以访问所有线程的信息，即使是属于其他用户的线程。With the PROCESS privilege, a user has access to information about all threads, even those belonging to other users.

    • 没有PROCESS权限的非匿名用户只能访问他们自己的线程信息，而无法访问其他用户的线程信息，匿名用户则无法访问任何线程信息。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

    Performance Schema 的threads表也提供线程信息，但表访问使用的是不同的权限模型。参见第 29.12.22.8 节，“threads 表”。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”.

    PROCESS 权限还允许使用 SHOW ENGINE 语句，访问 INFORMATION_SCHEMA 的 InnoDB 表（表名以 INNODB_ 开头），以及访问 INFORMATION_SCHEMA 的 FILES 表。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

    允许一个用户冒充或被识别为另一个用户。参见 第 8.2.19 节，“代理用户”。Enables one user to impersonate or become known as another user. See Section 8.2.19, “Proxy Users”.

  • REFERENCES

    创建外键约束需要对父表具有 REFERENCES 权限。Creation of a foreign key constraint requires the REFERENCES privilege for the parent table.

  • RELOAD

    RELOAD 权限允许以下操作：The RELOAD enables the following operations:

    • 使用 FLUSH 语句。Use of the FLUSH statement.

    • 使用与 FLUSH 操作等效的 mysqladmin 命令：flush-hosts、flush-logs、flush-privileges、flush-status、flush-tables、refresh 和 reload。Use of mysqladmin commands that are equivalent to FLUSH operations: flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, refresh, and reload.

      reload 命令告诉服务器将授权表重新加载到内存中。flush-privileges 是 reload 的同义词。refresh 命令关闭并重新打开日志文件，并刷新所有表。其他 flush-xxx 命令执行与 refresh 类似的功能，但更具体，在某些情况下可能更可取。例如，如果你只想刷新日志文件，flush-logs 比 refresh 更合适。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.

    • 使用 mysqldump 选项执行各种 FLUSH 操作：--flush-logs 和 --source-data。Use of mysqldump options that perform various FLUSH operations: --flush-logs and --source-data.

    • 使用 RESET BINARY LOGS AND GTIDS 和 RESET REPLICA 语句。Use of the RESET BINARY LOGS AND GTIDS and RESET REPLICA statements.

  • REPLICATION CLIENT REPLICATION CLIENT

    允许使用 SHOW BINARY LOG STATUS、SHOW REPLICA STATUS 和 SHOW BINARY LOGS 语句。Enables use of the SHOW BINARY LOG STATUS, SHOW REPLICA STATUS, and SHOW BINARY LOGS statements.

  • REPLICATION SLAVE REPLICATION SLAVE

    允许账户请求已在复制源服务器的数据库上进行的更新，使用 SHOW REPLICAS、SHOW RELAYLOG EVENTS 和 SHOW BINLOG EVENTS 语句。使用 mysqlbinlog 选项 --read-from-remote-server (-R) 和 --read-from-remote-source 也需要此权限。将此权限授予用于副本连接当前服务器作为其复制源服务器的账户。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

    允许从数据库中的表中选择行。SELECT 语句只有在实际访问表时才需要 SELECT 权限。有些 SELECT 语句不访问表，可以在没有任何数据库权限的情况下执行。例如，你可以使用 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;

    还需要SELECT权限来执行读取列值的其他语句。例如，在UPDATE语句中，SELECT权限是必须的，用于col_name=expr赋值右侧引用的列，或用于DELETE或UPDATE语句WHERE子句中指定的列。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.

    使用 SELECT 权限是对用在 EXPLAIN 的表或视图所必需的，包括视图定义中任何基础表。The SELECT privilege is needed for tables or views used with EXPLAIN, including any underlying tables in view definitions.

  • SHOW DATABASES

    允许账户通过执行 SHOW DATABASE 语句查看数据库名称。没有此权限的账户只能看到自己拥有某些权限的数据库，并且如果服务器是使用 --skip-show-database 选项启动的，则完全无法使用该语句。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

    因为任何静态全局权限都被视为对所有数据库的权限，所以任何静态全局权限都可以使用户通过 SHOW DATABASES 查看所有数据库名称，或通过检查 SCHEMATA 表（位于 INFORMATION_SCHEMA 中）来查看所有数据库名称，但被数据库级别的部分撤销限制的数据库除外。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

    启用使用 SHOW CREATE VIEW 语句。此权限对于与 EXPLAIN 一起使用的视图也是必需的。Enables use of the SHOW CREATE VIEW statement. This privilege is also needed for views used with EXPLAIN.

  • SHUTDOWN

    启用使用SHUTDOWN和RESTART语句，mysqladmin shutdown命令，以及mysql_shutdown() C API 函数。Enables use of the SHUTDOWN and RESTART statements, the mysqladmin shutdown command, and the mysql_shutdown() C API function.

  • SUPER

    SUPER 是一个强大且影响深远的权限，不应轻易授予。如果一个账户只需要执行 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 已被弃用，您应预计它将在 MySQL 的未来版本中被移除。请参阅 将账户从 SUPER 迁移到动态权限。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 影响以下操作和服务器行为：SUPER affects the following operations and server behaviors:

    如果启用了二进制日志，您可能还需要 SUPER 权限来创建或修改存储函数，如 第 27.7 节，“存储程序二进制日志” 所述。You may also need the SUPER privilege to create or alter stored functions if binary logging is enabled, as described in Section 27.7, “Stored Program Binary Logging”.

  • TRIGGER

    启用触发器操作。您必须拥有此表的此权限才能创建、删除、执行或显示该表的触发器。Enables trigger operations. You must have this privilege for a table to create, drop, execute, or display triggers for that table.

    当触发器被触发（由有权限执行与触发器相关的表的 INSERT、UPDATE 或 DELETE 语句的用户触发）、执行触发器需要定义该触发器的用户仍然拥有该表的 TRIGGER 权限。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 for the table.

  • UPDATE

    使表中的行可以在数据库中更新。Enables rows to be updated in tables in a database.

  • USAGE

    该权限指定符表示 “无权限。” 它在全局级别与 GRANT 一起使用，以在权限列表中未指定具体账户权限的情况下指定诸如 WITH GRANT OPTION 的子句。 SHOW GRANTS 会显示 USAGE，以表示某个账户在某个权限级别上没有权限。This privilege specifier stands for “no privileges.” It is used at the global level with GRANT to specify clauses such as WITH GRANT OPTION without naming specific account privileges in the privilege list. SHOW GRANTS displays USAGE to indicate that an account has no privileges at a privilege level.

动态权限描述Dynamic Privilege Descriptions

动态权限是在运行时定义的，与静态权限形成对比，静态权限是内置在服务器中的。以下列表描述了 MySQL 中可用的每个动态权限。Dynamic privileges are defined at runtime, in contrast to static privileges, which are built in to the server. The following list describes each dynamic privilege available in MySQL.

大多数动态权限在服务器启动时定义。其他权限则由特定组件或插件定义，如权限描述中所示。在这种情况下，除非启用了定义该权限的组件或插件，否则该权限不可用。Most dynamic privileges are defined at server startup. Others are defined by a particular component or plugin, as indicated in the privilege descriptions. In such cases, the privilege is unavailable unless the component or plugin that defines it is enabled.

某些特定的 SQL 语句可能有比此处所示更具体的权限要求。如果是这样，相关语句的描述将提供详细信息。Particular SQL statements might have more specific privilege requirements than indicated here. If so, the description for the statement in question provides the details.

权限授予指南Privilege-Granting Guidelines

建议只向账户授予其所需的权限。在授予 FILE 和管理权限时应特别谨慎：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:

  • FILE 可能被滥用来读取表中 MySQL 服务器在服务器主机上可以访问的任何文件。这包括所有可被全世界读取的文件以及服务器数据目录中的文件。然后可以使用 SELECT 访问该表，将其内容传输到客户端主机。FILE 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.

  • GRANT OPTION 允许用户将他们的权限授予其他用户。两个拥有不同权限且具有 GRANT OPTION 权限的用户能够合并权限。GRANT OPTION 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.

  • ALTER 可用于通过重命名表来规避权限系统。ALTER may be used to subvert the privilege system by renaming tables.

  • SHUTDOWN 可能被滥用，通过终止服务器完全拒绝其他用户的服务。SHUTDOWN can be abused to deny service to other users entirely by terminating the server.

  • PROCESS 可以用来查看当前正在执行的语句的明文，包括设置或更改密码的语句。PROCESS can be used to view the plain text of currently executing statements, including statements that set or change passwords.

  • SUPER 可以用来终止其他会话或更改服务器的操作方式。SUPER can be used to terminate other sessions or change how the server operates.

  • 授予 mysql 系统数据库本身的权限可以用来更改密码和其他访问权限信息：Privileges granted for the mysql system database itself can be used to change passwords and other access privilege information:

    • 密码是以加密形式存储的，因此恶意用户无法简单读取以得知明文密码。然而，具有写权限的用户可以修改 mysql.user 系统表的 authentication_string 列，从而更改账户的密码，然后使用该账户连接到 MySQL 服务器。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 mysql.user system table authentication_string column can change an account's password, and then connect to the MySQL server using that account.

    • INSERT 或 UPDATE 授予 mysql 系统数据库的权限分别允许用户添加权限或修改现有权限。INSERT or UPDATE granted for the mysql system database enable a user to add privileges or modify existing privileges, respectively.

    • DROP 对 mysql 系统数据库的权限允许用户删除权限表，或者甚至删除整个数据库。DROP for the mysql system database enables a user to remote privilege tables, or even the database itself.

静态权限与动态权限Static Versus Dynamic Privileges

MySQL 支持静态和动态权限：MySQL supports static and dynamic privileges:

  • 静态权限内置于服务器中。它们始终可以授予用户账户，且无法注销。Static privileges are built in to the server. They are always available to be granted to user accounts and cannot be unregistered.

  • 动态权限可以在运行时注册和注销。这会影响它们的可用性：未注册的动态权限无法被授予。Dynamic privileges can be registered and unregistered at runtime. This affects their availability: A dynamic privilege that has not been registered cannot be granted.

例如，SELECT 和 INSERT 权限是静态的且始终可用，而动态权限只有在实现它的组件已启用时才可用。For example, the SELECT and INSERT privileges are static and always available, whereas a dynamic privilege becomes available only if the component that implements it has been enabled.

本节的其余部分描述了 MySQL 中动态权限的工作原理。讨论中使用了术语 “组件”，但同样适用于插件。The remainder of this section describes how dynamic privileges work in MySQL. The discussion uses the term “components” but applies equally to plugins.

Note

服务器管理员应了解哪些服务器组件定义了动态权限。对于 MySQL 发行版，定义动态权限的组件的文档会描述这些权限。Server administrators should be aware of which server components define dynamic privileges. For MySQL distributions, documentation of components that define dynamic privileges describes those privileges.

第三方组件也可能定义动态权限；管理员应了解这些权限，并避免安装可能冲突或影响服务器运行的组件。例如，当两个组件都定义同名权限时，它们会产生冲突。组件开发者可以通过选择以组件名称为前缀的权限名称来降低此类情况发生的可能性。Third-party components may also define dynamic privileges; an administrator should understand those privileges and not install components that might conflict or compromise server operation. For example, one component conflicts with another if both define a privilege with the same name. Component developers can reduce the likelihood of this occurrence by choosing privilege names having a prefix based on the component name.

服务器在内存中维护已注册动态权限的集合。注销发生在服务器关闭时。The server maintains the set of registered dynamic privileges internally in memory. Unregistration occurs at server shutdown.

通常，定义动态权限的组件会在安装时，在其初始化序列中注册这些权限。当组件卸载时，它不会注销其已注册的动态权限。（这是当前做法，而非要求。也就是说，组件可以在任何时候注销它们注册的权限，但实际上通常不这样做。）Normally, a component that defines dynamic privileges registers them when it is installed, during its initialization sequence. When uninstalled, a component does not unregister its registered dynamic privileges. (This is current practice, not a requirement. That is, components could, but do not, unregister at any time privileges they register.)

尝试注册一个已注册的动态权限不会产生警告或错误。考虑以下语句序列：No warning or error occurs for attempts to register an already registered dynamic privilege. Consider the following sequence of statements:

INSTALL COMPONENT 'my_component';
UNINSTALL COMPONENT 'my_component';
INSTALL COMPONENT 'my_component';

第一个 INSTALL COMPONENT 语句会注册组件 my_component 定义的任何权限，但 UNINSTALL COMPONENT 不会注销这些权限。对于第二个 INSTALL COMPONENT 语句，它注册的组件权限已发现已被注册，但不会产生任何警告或错误。The first INSTALL COMPONENT statement registers any privileges defined by component my_component, but UNINSTALL COMPONENT does not unregister them. For the second INSTALL COMPONENT statement, the component privileges it registers are found to be already registered, but no warnings or errors occur.

动态权限仅在全局级别适用。服务器将在 mysql.global_grants 系统表中存储已分配给用户账户的当前动态权限信息：Dynamic privileges apply only at the global level. The server stores information about current assignments of dynamic privileges to user accounts in the mysql.global_grants system table:

  • 服务器在启动期间会自动注册 global_grants 中列出的权限（除非提供了 --skip-grant-tables 选项）。The server automatically registers privileges named in global_grants during server startup (unless the --skip-grant-tables option is given).

  • GRANT 和 REVOKE 语句会修改 global_grants 的内容。The GRANT and REVOKE statements modify the contents of global_grants.

  • 列在 global_grants 中的动态权限分配是持久的。它们在服务器关闭时不会被移除。Dynamic privilege assignments listed in global_grants are persistent. They are not removed at server shutdown.

示例：以下语句授予用户 u1 控制副本（包括组复制）所需的权限，并可修改系统变量：Example: The following statement grants to user u1 the privileges required to control replication (including Group Replication) on a replica, and to modify system variables:

GRANT REPLICATION_SLAVE_ADMIN, GROUP_REPLICATION_ADMIN, BINLOG_ADMIN
ON *.* TO 'u1'@'localhost';

授予的动态权限会出现在 SHOW GRANTS 语句的输出以及 INFORMATION_SCHEMA USER_PRIVILEGES 表中。Granted dynamic privileges appear in the output from the SHOW GRANTS statement and the INFORMATION_SCHEMA USER_PRIVILEGES table.

对于全局级别的 GRANT 和 REVOKE，任何未被识别为静态的命名权限都会与当前注册的动态权限集进行检查，如果找到则授予。否则，将发生错误以指示未知的权限标识符。For GRANT and REVOKE at the global level, any named privileges not recognized as static are checked against the current set of registered dynamic privileges and granted if found. Otherwise, an error occurs to indicate an unknown privilege identifier.

对于 GRANT 和 REVOKE，在全局级别上，ALL [PRIVILEGES] 的含义包括所有静态全局权限以及所有当前已注册的动态权限：For GRANT and REVOKE the meaning of ALL [PRIVILEGES] at the global level includes all static global privileges, as well as all currently registered dynamic privileges:

  • GRANT ALL 在全局级别授予所有静态全局权限和所有当前已注册的动态权限。在执行 GRANT 语句之后注册的动态权限不会追溯授予任何账户。GRANT ALL at the global level grants all static global privileges and all currently registered dynamic privileges. A dynamic privilege registered subsequent to execution of the GRANT statement is not granted retroactively to any account.

  • REVOKE ALL 在全局级别撤销所有已授予的静态全局权限和所有已授予的动态权限。REVOKE ALL at the global level revokes all granted static global privileges and all granted dynamic privileges.

FLUSH PRIVILEGES 语句读取 global_grants 表以进行动态权限分配，并注册其中发现的任何未注册权限。The FLUSH PRIVILEGES statement reads the global_grants table for dynamic privilege assignments and registers any unregistered privileges found there.

有关 MySQL Server 提供的动态权限以及包含在 MySQL 发行版中的组件提供的动态权限的描述，请参见 第 8.2.2 节，“MySQL 提供的权限”。For descriptions of the dynamic privileges provided by MySQL Server and components included in MySQL distributions, see Section 8.2.2, “Privileges Provided by MySQL”.

将账户从 SUPER 迁移到动态权限Migrating Accounts from SUPER to Dynamic Privileges

在 MySQL 8.4 中，许多以前需要 SUPER 权限的操作，现在也与范围更有限的动态权限相关联。（有关这些权限的描述，请参见 第 8.2.2 节，“MySQL 提供的权限”。）可以通过授予相关的动态权限而不是 SUPER 来允许账户执行每个此类操作。此更改通过使 DBA 能够避免授予 SUPER 并更精确地为用户分配操作权限，从而提高了安全性。SUPER 现已弃用；预计将在未来的 MySQL 版本中移除。In MySQL 8.4, many operations that previously required the SUPER privilege are also associated with a dynamic privilege of more limited scope. (For descriptions of these privileges, see Section 8.2.2, “Privileges Provided by MySQL”.) Each such operation can be permitted to an account by granting the associated dynamic privilege rather than SUPER. This change improves security by enabling DBAs to avoid granting SUPER and tailor user privileges more closely to the operations permitted. SUPER is now deprecated; expect it to be removed in a future version of MySQL.

当移除 SUPER 时，以前需要 SUPER 的操作将失败，除非将授予 SUPER 的账户迁移到适当的动态权限。请使用以下说明来完成此目标，以便在移除 SUPER 之前准备好账户：When removal of SUPER occurs, operations that formerly required SUPER fail unless accounts granted SUPER are migrated to the appropriate dynamic privileges. Use the following instructions to accomplish that goal so that accounts are ready prior to SUPER removal:

  1. 执行此查询以识别已授予 SUPER 的账户：Execute this query to identify accounts that are granted SUPER:

    SELECT GRANTEE FROM INFORMATION_SCHEMA.USER_PRIVILEGES
WHERE PRIVILEGE_TYPE = 'SUPER';

  2. 对于前一个查询识别的每个账户，确定其需要 SUPER 的操作。然后授予对应于这些操作的动态权限，并撤销 SUPER。For each account identified by the preceding query, determine the operations for which it needs SUPER. Then grant the dynamic privileges corresponding to those operations, and revoke SUPER.

    例如，如果 'u1'@'localhost' 在二进制日志清理和系统变量修改中需要 SUPER 权限，这些语句会对账户进行所需更改：For example, if 'u1'@'localhost' requires SUPER for binary log purging and system variable modification, these statements make the required changes to the account:

    GRANT BINLOG_ADMIN, SYSTEM_VARIABLES_ADMIN ON *.* TO 'u1'@'localhost';
REVOKE SUPER ON *.* FROM 'u1'@'localhost';

    在您修改了所有适用账户后，第一步中的 INFORMATION_SCHEMA 查询应产生空结果集。After you have modified all applicable accounts, the INFORMATION_SCHEMA query in the first step should produce an empty result set.

首页   上一级   下一节
Related Documentation
MySQL 8.4 Release Notes
Download this Manual
PDF (US Ltr) - 40.2Mb
PDF (A4) - 40.3Mb
Man Pages (TGZ) - 261.9Kb
Man Pages (Zip) - 367.4Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb