MySQL权限系统(二). MySQL提供的特权 Privileges Provided by MySQL

MySQL provides privileges that apply in different contexts 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).

MySQL提供适用于不同环境和不同操作级别的权限：

  • 管理权限使用户能够管理MySQL服务器的操作。 这些权限是全局的，因为它们不是具体到一个特定的数据库。

  • 数据库权限适用于数据库及其中的所有对象。 可以为特定数据库或全局授予这些权限，以便它们适用于所有数据库。

  • 可以为数据库内的特定对象，数据库中给定类型的所有对象（例如，数据库中的所有表）授予针对数据库对象（例如表，索引，视图和存储例程）的特权， 在所有数据库中给定类型的对象）。

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

有关帐户权限的信息存储在mysql数据库中的 user,db，tables_priv，columns_priv和procs_priv表中（请参见第7.2.2节“授权表”）。 MySQL服务器在启动时会将这些表的内容读入内存，并在第7.2.6节“当权限 更改生效时”中指示的情况下重新加载它们。 访问控制策略是基于授权表中的内存副本。

Some releases of MySQL 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 update to a new version of MySQL. See Section 5.4.7, “mysql_upgrade — Check and Upgrade MySQL Tables”.

MySQL的一些版本引入对权限表结构的更改，以添加新的权限或功能。 为了确保您可以利用任何新功能，请在更新到新版本的MySQL时更新授权表以具有当前结构。 请参见第5.4.7节“mysql_upgrade - 检查和升级MySQL表”。

The following table shows the privilege names used at the SQL level in the 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.

下表显示了在GRANT和REVOKE语句中的SQL级别使用的特权名称，以及与授予表中的每个特权以及应用特权的上下文相关联的列名称。

Table 7.2 Permissible Privileges for GRANT and REVOKE

PrivilegeColumnContext
CREATE Create_priv databases, tables, or indexes
DROP Drop_priv databases, tables, or views
GRANT OPTION Grant_priv databases, tables, or stored routines
LOCK TABLES Lock_tables_priv databases
REFERENCES References_priv databases or tables
EVENT Event_priv databases
ALTER Alter_priv tables
DELETE Delete_priv tables
INDEX Index_priv tables
INSERT Insert_priv tables or columns
SELECT Select_priv tables or columns
UPDATE Update_priv tables or columns
CREATE TEMPORARY TABLES Create_tmp_table_priv tables
TRIGGER Trigger_priv tables
CREATE VIEW Create_view_priv views
SHOW VIEW Show_view_priv views
ALTER ROUTINE Alter_routine_priv stored routines
CREATE ROUTINE Create_routine_priv stored routines
EXECUTE Execute_priv stored routines
FILE File_priv file access on server host
CREATE TABLESPACE Create_tablespace_priv server administration
CREATE USER Create_user_priv server administration
PROCESS Process_priv server administration
PROXY see proxies_priv table server administration
RELOAD Reload_priv server administration
REPLICATION CLIENT Repl_client_priv server administration
REPLICATION SLAVE Repl_slave_priv server administration
SHOW DATABASES Show_db_priv server administration
SHUTDOWN Shutdown_priv server administration
SUPER Super_priv server administration
ALL [PRIVILEGES]   server administration
USAGE   server administration
 

The following list provides a general description of each 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.

以下列表提供了MySQL中可用的每个权限的一般说明。 特定的SQL语句可能具有比此处指示的更具体的特权要求。 如果是，对所述陈述的描述提供了细节。

    • 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.ALL或ALL PRIVILEGES权限说明符是速记。 它代表“在给定权限级别下可用的所有权限”（GRANT OPTION除外）。 例如，在全局或表级授予ALL授予所有全局特权或所有表级特权。

    • The ALTER privilege enables use of ALTER TABLE to change the structure of tables. ALTER TABLE also requires the CREATE and INSERT privileges. Renaming a table requires ALTER andDROP on the old table, CREATE, and INSERT on the new table.  ALTER权限允许使用ALTER TABLE来更改表的结构。 ALTER TABLE还需要CREATE和INSERT权限。 重命名表需要对旧表执行ALTER和DROP，对新表执行CREATE和INSERT。

    • The ALTER ROUTINE privilege is needed to alter or drop stored routines (procedures and functions).ALTER ROUTINE权限需要去更改或删除存储的例程（存储过程和函数）。

    • The CREATE privilege enables creation of new databases and tables.CREATE权限允许创建新的数据库和表。

    • The CREATE ROUTINE privilege is needed to create stored routines (procedures and functions).需要CREATE ROUTINE特权来创建存储的例程（过程和函数）。

    • The CREATE TABLESPACE privilege is needed to create, alter, or drop tablespaces and log file groups.需要CREATE TABLESPACE权限才能创建，更改或删除表空间和日志文件组

    • The CREATE TEMPORARY TABLES privilege enables the creation of temporary tables using the CREATE TEMPORARY TABLE statement.CREATE TEMPORARY TABLES权限允许使用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.在会话创建了临时表之后，服务器不对表执行进一步的权限检查。 创建会话可以对表执行任何操作，例如DROP TABLE，INSERT，UPDATE或SELECT。

      One implication of this behavior is that a session can manipulate its temporary tables even if the current user has no privilege to create them. Suppose that the current user does not have the CREATE TEMPORARY TABLES privilege but is able to execute a DEFINER-context stored procedure that executes with the privileges of a user who does have CREATE TEMPORARY TABLES and that creates a temporary table. While the procedure executes, the session uses the privileges of the defining user. After the procedure returns, the effective privileges revert to those of the current user, which can still see the temporary table and perform any operation on it.这种行为的一个影响是，即使当前用户没有创建它们的权限，会话也可以操纵它的临时表。 假设当前用户没有CREATE TEMPORARY TABLES权限，但能够执行定义-上下文存储过程，该存储过程使用具有CREATE TEMPORARY TABLES并创建临时表的用户的权限执行。 当过程执行时，会话使用定义用户的特权。 在过程返回后，有效权限将恢复为当前用户的权限，它仍然可以查看临时表并对其执行任何操作。

    • To keep privileges for temporary and nontemporary tables separate, a common workaround for this situation is to create a database dedicated to the use of temporary tables. Then for that database, a user can be granted the CREATE TEMPORARY TABLES privilege, along with any other privileges required for temporary table operations done by that user.为了保持临时表和非临时表的权限分开，这种情况的常见解决方法是创建专用于临时表的数据库。 然后对于该数据库，可以向用户授予CREATE TEMPORARY TABLES权限以及该用户执行的临时表操作所需的任何其他权限。

    • The CREATE USER privilege enables use of ALTER USER, CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.CREATE USER权限允许使用ALTER USER，CREATE USER，DROP USER，RENAME USER和REVOKE ALL PRIVILEGES。

    • The CREATE VIEW privilege enables use of CREATE VIEW. CREATE VIEW允许使用CREATE VIEW权限

    • 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.   DROP权限允许您删除（移除）现有的数据库，表和视图。 需要DROP权限才能在分区表上使用语句ALTER TABLE ... DROP PARTITION。 TRUNCATE TABLE也需要DROP权限。 如果您向用户授予mysql数据库的DROP权限，该用户可以删除存储MySQL访问权限的数据库。

    • The EVENT privilege is required to create, alter, drop, or see events for the Event Scheduler.需要EVENT权限才能创建，更改，删除或查看事件计划程序的事件

    • The EXECUTE privilege is required to execute stored routines (procedures and functions).执行存储的例程（过程和函数）需要EXECUTE特权。

    • 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, theFILE privilege is required to use the DATA DIRECTORY or INDEX DIRECTORY table option for the CREATE TABLE statement.FILE权限允许您使用LOAD DATA INFILE和SELECT ... INTO OUTFILE语句和LOAD_FILE（）函数读取和写入服务器主机上的文件。 具有FILE权限的用户可以读取服务器主机上的全局可读或可由MySQL服务器读取的文件。 （这意味着用户可以读取任何数据库目录中的任何文件，因为服务器可以访问任何这些文件。）FILE权限还使用户能够在MySQL服务器具有写访问权限的任何目录中创建新文件。 这包括服务器的数据目录，其中包含实现特权表的文件。 作为安全措施，服务器不会覆盖现有文件。 从MySQL 5.7.17起，需要FILE特权才能对CREATE TABLE语句使用DATA DIRECTORY或INDEX DIRECTORY表选项。

      To limit the location in which files can be read and written, set the secure_file_priv system to a specific directory. See Section 6.1.5, “Server System Variables”.要限制文件可读写的位置，请将secure_file_priv系统设置为特定目录。 请参见第6.1.5节“服务器系统变量”。

    • The GRANT OPTION privilege enables you to give to other users or remove from other users those privileges that you yourself possess.GRANT OPTION权限允许您授予其他用户或从其他用户中删除您自己拥有的那些权限。

    • 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.INDEX权限允许您创建或删除（删除）索引。 INDEX适用于现有表。 如果具有表的CREATE特权，则可以在CREATE TABLE语句中包括索引定义。

    • 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.INSERT权限允许将行插入到数据库中的表中。 ANALYZE TABLE，OPTIMIZE TABLE和REPAIR TABLE表 - 维护语句还需要INSERT。

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

    • 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.  PROCESS权限适用于显示有关在服务器中执行的线程的信息（即有关由会话执行的语句的信息）。 该特权允许使用SHOW PROCESSLIST或mysqladmin processlist查看属于其他帐户的线程; 你总是可以看到自己的线程。 PROCESS权限还允许使用SHOW ENGINE。

    • The PROXY privilege enables a user to impersonate or become known as another user. See Section 7.3.9, “Proxy Users”.PROXY权限使用户能够模拟或称为另一个用户。 请参见第7.3.9节“代理用户”。

    • 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.REFERENCES特权在MySQL 5.7.6之前未使用。 从5.7.6开始，创建外键约束需要父表的REFERENCES特权。

    • 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.RELOAD权限允许使用FLUSH语句。 它还启用等效于FLUSH操作的mysqladmin命令：flush-hosts，flush-logs，flush-privileges，flush-status，flush-tables，flush-threads，refresh和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.reload命令指示服务器将授权表重新加载到内存中。 flush-privileges是重新加载的同义词。 刷新命令关闭并重新打开日志文件并刷新所有表。 其他flush-xxx命令执行类似于刷新的功能，但是更具体，并且在某些情况下可能是优选的。 例如，如果要仅刷新日志文件，则刷新日志是比刷新更好的选择。

    • The REPLICATION CLIENT privilege enables the use of SHOW MASTER STATUS, SHOW SLAVE STATUS, and SHOW BINARY LOGS.REPLICATION CLIENT权限允许使用SHOW MASTER STATUS，SHOW SLAVE STATUS和SHOW BINARY LOGS。

    • 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.应该将REPLICATION SLAVE权限授予由从服务器用于连接到当前服务器作为其主服务器的帐户。 没有此权限，从属设备无法请求对主服务器上的数据库进行的更新。

    • 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 SELECTstatements 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特权使您能够从数据库中的表中选择行。 SELECT语句只有在实际从表中检索行时才需要SELECT特权。 一些SELECT语句不访问表，并且可以在没有任何数据库的权限的情况下执行。 例如，您可以使用SELECT作为一个简单的计算器来计算不参考表的表达式：

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=exprassignment in UPDATE statements or for columns named in the WHERE clause of DELETE or UPDATE statements.读取列值的其他语句也需要SELECT特权。 例如，对于在UPDATE语句中col_name = exprassignment右侧引用的列或在DELETE或UPDATE语句的WHERE子句中指定的列，需要SELECT。

  • 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.SHOW DATABASES权限使帐户能够通过发出SHOW DATABASE语句来查看数据库名称。 没有此权限的帐户仅查看具有某些权限的数据库，如果服务器以--skip-show-database选项启动，则根本不能使用该语句。 请注意，任何全局权限都是数据库的权限。

  • The SHOW VIEW privilege enables use of SHOW CREATE VIEW. SHOW VIEW权限允许使用SHOW CREATE VIES

  • The SHUTDOWN privilege enables use of the SHUTDOWN statement, the mysqladmin shutdown command, and the mysql_shutdown() C API function.SHUTDOWN权限允许使用SHUTDOWN语句，mysqladmin shutdown命令和mysql_shutdown（）C API函数。

  • The SUPER privilege enables these operations and server behaviors:SUPER权限允许这些操作和服务器行为：

    • Enable use of the CHANGE MASTER TO and CHANGE REPLICATION FILTER statements.允许使用CHANGE MASTER TO 和 CHANGE REPLICATION FILTER 语句

    • Enable use of the KILL statement or mysqladmin kill command to kill threads belonging to other accounts. (You can always kill your own threads.) 允许使用kill或者mysqladmin kill指令杀掉属于其他用户的线程(你也可以杀掉你自己的线程)

    • Enable use of the PURGE BINARY LOGS statement. 允许使用PURGE BINARY LOGS语句

    • Enable use of the BINLOG statement.  允许使用BINLOG语句

    • Enable use of the CREATE SERVER, ALTER SERVER, and DROP SERVER statements. 允许使用CREATE SERVER,ALTER SERVER和DROP SERVER语句

    • Enable use of the mysqladmin debug command. 允许使用mysqladmin debug指令

    • Enable starting and stopping replication on slave servers, including Group Replication. 允许启动和停止从服务器及群组复制的复制

    • Enable 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.通过修改全局系统变量启用配置更改。 对于某些系统变量，设置会话值还需要SUPER权限; 如果是，则在变量描述中指示。 示例包括binlog_format，sql_log_bin和sql_log_off。

    • Enable performing updates even when the read_only system variable is enabled. This applies to table updates and use of account-management statements such as GRANT andREVOKE.即使在启用read_only系统变量时也启用执行更新。 这适用于表更新和使用帐户管理语句，如GRANT和REVOKE。

    • Enable specifying any account in the DEFINER attribute of stored programs and views.启用在存储的程序和视图的DEFINER属性中指定任何帐户。

    • Enable execution of Version Tokens user-defined functions.启用版本令牌的用户定义函数的执行。

    • Enable InnoDB key rotation. 启用InnoDB密钥轮换

    • Enable reading the DES key file by the DES_ENCRYPT() function.允许通过DES_ENCRYPT()函数来读取DES秘钥文件

    • The server accepts one connection from a SUPER client even if the connection limit controlled by the max_connections system variable is reached.服务器接受来自客户端的SUPER一个连接即使达到了max_connections的系统变量控制的连接限制。

    • The server does not execute init_connect system variable content when SUPER clients connect.当SUPER客户端连接时，服务器不执行init_connect系统变量内容。

    • A server in offline mode does not terminate SUPER client connections at the next client request, and accepts new connections from SUPER clients.处于离线模式的服务器不会在下一个客户端请求时终止SUPER客户端连接，并接受来自SUPER客户端的新连接。

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

  • The TRIGGER privilege enables trigger operations. You must have this privilege for a table to create, drop, execute, or display triggers for that table.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.当激活触发器时（由具有为与触发器相关联的表执行INSERT，UPDATE或DELETE语句的权限的用户），触发器执行要求定义触发器的用户仍然具有TRIGGER权限。

  • 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 affecting existing account privileges.USAGE权限说明符代表“无权限”。它在全局级别与GRANT一起用于修改帐户属性，如资源限制或SSL特性，而不影响现有帐户特权。

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和管理权限时，应特别小心：

  • 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.MySQL服务能读取到主机服务器通过滥用FILE权限将任何文件读取到数据库中。 这包括服务器数据目录中文件和全局可读的文件。 然后可以使用SELECT访问该表以将其内容传输到客户端主机。

  • 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.GRANT OPTION特权允许用户将其特权授予其他用户。 具有不同权限和GRANT OPTION权限的两个用户可以合并权限。

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

  • The SHUTDOWN privilege can be abused to deny service to other users entirely by terminating the server.可以通过终止服务器来完全拒绝SHUTDOWN权限拒绝对其他用户的服务。

  • The PROCESS privilege can be used to view the plain text of currently executing statements, including statements that set or change passwords.PROCESS权限可用于查看当前执行的语句的纯文本，包括设置或更改密码的语句。

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

  • 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.授予mysql数据库本身的权限可用于更改密码和其他访问权限信息。 密码以加密形式存储，因此恶意用户不能简单地读取密码以了解纯文本密码。 但是，对用户表authentication_string列具有写访问权限的用户可以更改帐户的密码，然后使用该帐户连接到MySQL服务器。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/olinux/p/6409107.html