7.2.1 MySQL提供的权限

MySQL提供的权限应用于不同的上下文和不同的操作级别：
- 管理权限使用户可以管理MySQL服务器的操作。这些权限是全局性的，因为它们不是局限于某个特定的数据库
- 数据库权限应用于数据库和数据库的组成对象。这些权限可以被赋权时可以是针对某个数据库的，或者针对所有数据库的。
- 数据库对象（比如表，索引，视图，存储过程，存储方法）权限赋权时可以是针对某个数据库内的特定对象，或者这个数据库内的一个类型的所有对象（比如一个数据库的所有表），或者全局数据库的一个类型的所有对象.
账号权限的信息被保存在mysql数据库中的user, db, tables_priv, columns_priv, and procs_priv表中。
下面的表格展示了在sql级别的grant和revoke语句中的权限名称，以及每个权限在grant表和其应用的上下文中的列名。
Table 7.2 Permissible Privileges for GRANT and REVOKE

Privilege	Column	Context
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_prdv	atabases 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

下面的清单大致描述了MySQL中的每个权限。特定的SQL语句可能会有多个权限需。如果是这样的语句，语句的描述中会给出细节说明。

• ALL or ALL PRIVILEGES权限符合是缩写，表示一个给定级别的所有权限（除了grant选项）。比如，granting ALL at the global or table level 会把赋予所有的全局权限或者所有的表级权限。
• Alter权限允许使用alter table来改变表结构.alter table同时还需要create和insert权限。重命名一个表需要对旧表有alter和drop权限，同时对新表有create和insert权限。
• alter或drop存储方法和过程时需要有Alter Routine权限。
• Create权限允许新建数据库和表。
• 新建存储过程和方法时需要有Create Routine权限。
• 新建/修改/丢弃tablespace和日志文件组时需要有Create Tablespace权限。
• CREATE TEMPORARY TABLES 权限允许使用CREATE TEMPORARY TABLE语句来新建临时表。
  一个会话创建一个临时表后，服务器不再对这个表进行权限检查。The creating session can perform any operation on the table, such as DROP TABLE, INSERT, UPDATE, or SELECT.
  这就意味着即使一个会话的当前用户没有权限创建临时表，该会话也能操作临时.假设当前当前用户没有CREATE TEMPORARY TABLES权限，但是他能够像拥有CREATE TEMPORARY TABLES权限的人一样执行DEFINER-context（定义者上下文）的存储过程来建立一个临时表。当存储执行时，会话使用的是定义者的权限。当存储返回结束后，实际的权限还原到当前用户，这时该用户依然能够看到临时表，并对它进行任何操作。
  为了使临时表和非临时表的权限分开，一个常见的变通方法是为临时表专门建一个数据库。然后一个用户可以被赋予针对这个数据库的CREATE TEMPORARY TABLES权限，也包括这个用户需要的针对临时表的其他权限。
• CREATE USER权限允许使用ALTER USER, CREATE USER, DROP USER, RENAME USER, 和REVOKE ALL PRIVILEGES.
• CREATE VIEW权限允许使用CREATE VIEW.
• DELETE权限允许在一个数据库中将行从表中删除。
• DROP权限允许你清除存在的数据库/表和试图。DROP权限用来对一个分区表使用ALTER TABLE … DROP PARTITION语句。TRUNCATE TABLE也需要DROP权限。如果你把mysql数据库的DROP权限赋给一个用户，这个用户可以清除掉存储MySQL权限的数据库。
• EVENT权限用来在事件调度中create, alter, drop, or see events。
• EXECUTE权限用来执行存储过程和存储函数。
• FILE权限用来在服务器主机上使用LOAD DATA INFILE and SELECT … INTO OUTFILE 语句和LOAD_FILE()函数读写文件 . 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 6.1.5, “Server System Varia”.
• 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.
• LOCK TABLES权限允许使用明确的LOCK TABLES语句来锁住你有SELECT权限的表。这包括写锁，以防止其他会话读取这个表。
• PROCESS权限是关于展示在服务器中执行的线程的信息（即会话执行的语句的信息）。这个权限允许使用SHOW PROCESSLIST或mysqladmin processlist来查看属于其他账号的线程；你也可以查看你自己的线程。PROCESS权限也能使用SHOW ENGINE.
• PROXY权限允许一个用户模仿或者被视为另一个用户。详情见7.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 SHOW MASTER STATUS, SHOW SLAVE STATUS, and 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.
• SELECT权限使你能够从一个数据库的表中选择行。SELECT语句只有在真正从表中检索行时才需要。有一些SELECT语句不访问表，可以不需要Select权限就可以执行。比如，你可以使用select作为一个简单的计算器来计算那些不引用表的表达式：
  SELECT 1+1;
SELECT PI()*2;
  对于一些读取列内容的语句时，SELECT权限也是必须的，比如在update语句右边col_name=expr表达式中引用的列，或在delete/update语句where分句中提到的列。
• SHOW DATABASES权限使用户能够通过发送SHOW DATABASES语句来查看数据库名称。 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 VIEW权限允许使用SHOW CREATE VIEW语句。
• 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

  • Enable use of the PURGE BINARY LOGS statement.
  • Enable use of the CHANGE MASTER TO and CHANGE REPLICATION FILTER s
  • Enable use of the KILL statement or mysqladmin kill command to kill threads belonging to other accounts. (You can always kill your own threads.)
  • Enable use of the BINLOG statement.
  • Enable use of the CREATE SERVER, ALTER SERVER, and DROP SERVER statements.
  • Enable use of the mysqladmin debug command.
  • 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.
  • 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 and REVOKE.
  • Enable specifying any account in the DEFINER attribute of stored programs and views.
  • Enable execution of Version Tokens user-defined functions.
  • Enable InnoDB key rotation.
  • Enable reading the DES key file by the DES_ENCRYPT() function.
  • The server accepts one connection from a SUPER client even if the connection limit controlled by the max_connections system variable is reached.
  • The server does not execute init_connect system variable content when SUPER clients connect.
  • A server in offline mode does not terminate SUPER client connections at the next client request, and accepts new connections from SUPER clients.
    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”.
    The TRIGGER pr

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

• 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.
  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.
• GRANT OPTION权限允许用户将他们拥有的权限付给其他用户。两个拥有不同权限且都有GRANT OPTION权限的用户可以组合权限。
• ALTER权限可以通过重新命名表来暗中破坏权限系统。
• SHUTDOWN权限可以被滥用于通过关闭服务器来完全拒绝服务其他人。
• The PROCESS privilege can be used to view the plain text of currently executing statements, including statements that set or change passwords.
• 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.