MySQL的mysql系统库提供了user、db、tables_priv、columns_priv、procs_priv、proxies_priv几个表,用于存放不同权限范围的用户账号相关数据,这些表共同组成了MySQL的访问权限控制系统。
MySQL访问权限控制系统的主要功能是对从给定主机连接到MySQL服务器的用户进行身份验证,并校验该用户在该服务器中的数据库对象访问权限(如SELECT、 INSERT、UPDATE和DELETE)。另外,还包括管理匿名用户访问和授予特定的MySQL 权限的功能(如执行 LOAD DATA INFILE 语句和管理操作权限等)。
MySQL访问权限控制系统的用户界面由几条SQL语句组成,如CREATE USER、 GRANT和REVOKE。
在服务器内部,MySQL将权限信息存储在mysql系统库的权限表中。MySQL服务器在启动时将这些表的内容读入内存,后续针对用户的访问控制决策基于权限表的内存副本来实现。MySQL访问权限控制系统可以确保只有被允许的(与用户权限匹配的)操作才能够在服务器中执行。当一个用户连接到MySQL服务器时,其认证身份由“请求连接的主机名和用户名”确定,MySQL使用主机名+用户名的方式来识别和区分“相同主机不同用户”和“不同主机相同用户”发出的请求(例如:从office.example.com连接的用户joe和从 home.example.com连接的用户joe在MySQL服务器中实际上是被当作两个不同的连接者来 处理的,所以可以设置不同的密码、不同的权限)。例如:
@localhost:[mysql]>show grants for root@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
@localhost:[mysql]>show grants for root@'%';
ERROR 1141 (42000): There is no such grant defined for user 'root' on host '%'
当用户使用客户端程序连接到MySQL服务器时,MySQL的访问控制分为如下两个阶段。
阶段1:服务器根据身份标识(主机名+用户名组成的账号名称)在MySQL 的访问权限控制表中查询相关信息,以确定需要接受或拒绝该用户的连接(没有查询到就拒绝连接)。如果查询到了用户记录,则校验用户提供的账号密码是否正确,如果密码不正确则 拒绝连接。这个阶段的报错信息类似于:ERROR 1045 (28000): Access denied for user ‘test’@‘localhost’(using password: YES)。
阶段2:用户连接成功之后,服务器会检查用户访问请求中的每个声明,确定是否有足够的权限来执行。例如:如果尝试从数据库的表中查询数据行或从数据库中删除表,服务器将验证该用户否具有该表的SELECT权限或数据库的DROP权限,如果无对应权 限,则这个阶段的报错信息类似于:ERROR 1142 (42000) at line 1: UPDATE command denied to user’test’@‘localhost’ for table ‘sbtest1’。
如果某用户在连接期间发生了权限变更(自己或者其他用户修改了权限),那么该用户执行下一条语句时,该权限变更不一定会立即生效。如果未生效,则需要执行 FLUSH PRIVILEGES; 语句。
MySQL提供了哪些权限 MySQL提供的权限列表如下所示(其中,All或者All privileges代表权限列表中除 Grant option权限之外的所有权限)。
@localhost:[mysql]>show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege | Context | Comment |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter | Tables | To alter the table |
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |
| Create | Databases,Tables,Indexes | To create new databases and tables |
| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE |
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
| Create view | Tables | To create new views |
| Create user | Server Admin | To create new users |
| Delete | Tables | To delete existing rows |
| Drop | Databases,Tables | To drop databases, tables, and views |
| Event | Server Admin | To create, alter, drop and execute events |
| Execute | Functions,Procedures | To execute stored routines |
| File | File access on server | To read and write files on the server |
| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |
| Index | Tables | To create or drop indexes |
| Insert | Tables | To insert data into tables |
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |
| Process | Server Admin | To view the plain text of currently executing queries |
| Proxy | Server Admin | To make proxy user possible |
| References | Databases,Tables | To have references on tables |
| Reload | Server Admin | To reload or refresh tables, logs and privileges |
| Replication client | Server Admin | To ask where the slave or master servers are |
| Replication slave | Server Admin | To read binary log events from the master |
| Select | Tables | To retrieve rows from table |
| Show databases | Server Admin | To see all databases with SHOW DATABASES |
| Show view | Tables | To see views with SHOW CREATE VIEW |
| Shutdown | Server Admin | To shut down the server |
| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |
| Trigger | Tables | To use triggers |
| Create tablespace | Server Admin | To create/alter/drop tablespaces |
| Update | Tables | To update existing rows |
| Usage | Server Admin | No privileges - allow connect only |
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.00 sec)
在上表所示的权限列表中,Context字段显示了该权限的使用环境(或者叫权限的作用域)。根据Context字段内容的不同,权限分为如下三类。
通常,还可以根据使用经验按照如下方式划分。
下面我们逐一解释每个权限的作用。
All或All privileges:除Grant option之外,代表其他所有权限。
Alter:该权限用于使用ALTER TABLE语句来更改表的结构(除该权限之外,使用ALTER TABLE语句还需要有Create和Insert权限,使用ALTER TABLE RENAME语句需 要有旧表上的Alter和Drop权限,新表上的Create和Insert权限)。
Alter routine:该权限用于修改或删除存储过程或存储函数。 * Create:该权限用于创建库和表。
Create routine:该权限用于创建存储过程或存储函数。
Create tablespace:该权限用于创建、修改、删除表空间文件和日志组文件。
Create temporary tables:该权限用于创建临时表。使用CREATE TEMPORARY TABLE语句创建临时表,一旦某会话创建临时表成功后,服务器不会在该表上执行权限 检查(因为其他会话看不见此表,创建此表的会话一旦断开,临时表就会自动删除)。 即,创建临时表的会话可以对该临时表执行任何操作,例如DROP TABLE、INSERT、 UPDATE、SELECT等操作。
Create user:该权限用于使用ALTER USER、CREATE USER、DROP USER、 RENAME USER、REVOKE ALL PRIVILEGES语句。
Create view:该权限用于使用CREATE VIEW语句。
Delete:该权限用于从数据库表中删除数据记录。
Drop:该权限用于删除现有库、表、视图等对象。另外,如果在分区表上使用 ALTER TABLE … DROP PARTITION语句,则必须要有表的Drop权限,执行TRUNCATE TABLE也需要有Drop权限(但要注意,如果将MySQL数据库的Drop权限授予用户,则该 用户可以删除存储MySQL访问权限记录的数据库mysql)。
Event:该权限用于创建、更改、删除或查看Event Scheduler事件。
Execute:该权限用于执行存储过程或存储函数。
File:该权限用于执行LOAD DATA INFILE和SELECT … INTO OUTFILE语句以及 LOAD_FILE()函数来读写服务器主机上的文件。具有File权限的用户可以读取服务器主机 上的任何可读文件或MySQL服务器可读文件。(即,用户可读取datadir目录中的任何文 件),File权限还使用户能够在MySQL服务器有写入权限的任何目录下创建新文件。所以,作为安全保护措施,服务器不会覆盖现有文件(即,在执行导出数据到文本时,如果文件名重复,则导出语句无法成功执行)。在MySQL 5.7版本中,可以使用 secure_file_priv系统变量限制File权限的读写目录。
Grant option:该权限用于授予或回收其他用户或自己拥有的权限。
Index:该权限用于创建或删除索引。Index权限适用于在已存在的表上使用 CREATE INDEX语句,如果用户具有Create权限,则可以在CREATE TABLE语句中包含 索引定义语句。
Insert:该权限用于向表中插入数据记录行。对于ANALYZE TABLE、OPTIMIZE TABLE和REPAIR TABLE表维护语句也需要Insert权限。
Lock tables:该权限用于使用LOCK TABLES语句对表显式加锁,持有表锁的用户 对该表有读写权限,未持有表锁的用户对该表的读写访问会被阻塞。
Process:该权限用于显示有关在服务器上执行的线程信息(即,关于会话正在执 行的语句相关状态信息)。拥有该权限的用户在使用SHOW PROCESSLIST语句或 mysqladmin processlist命令查看有关线程信息时,除可以看到自己的线程信息之外还可以 查看到属于其他账号的线程信息。另外,使用SHOW ENGINE语句以及查看 information_schema系统库中的相当一部分表也需要该权限。
Proxy:该权限使用户能够模仿(伪装、代理)另一个用户。
References:在创建外键约束时,该权限需要用户具有父表的References权限。
Reload:该权限允许用户使用FLUSH语句。拥有该权限的用户还可以使用与 FLUSH操作等效的mysqladmin子命令——flush-hosts、flush-logs、flush-privileges、flush- status、flush-tables、flush-threads、refresh和reload。其中,reload子命令会通知服务器将权 限表重新加载到内存中;flush-privileges子命令的作用与reload相同;refresh子命令会通知服务器关闭并重新打开日志文件且刷新所有表。其他flush-xxx子命令也会执行类似于刷新的功能,这些子命令刷新的对象更具体。例如,只想刷新日志文件,则使用flush-logs子命 令。
Replication client:该权限用于使用SHOW MASTER STATUS、SHOW SLAVE STATUS和SHOW BINARY LOGS语句。
Replication slave:该权限用于从从库服务器连接到主库服务器并请求主库的binlog 日志。如果没有此权限,从库将无法请求主库数据库变更的binlog日志。
Select:该权限用于从数据库表中查询数据行记录。使用SELECT语句只有实际从 表中检索行记录时才需要Select权限。但某些SELECT语句不需要访问表,并且可以在没 有任何数据库权限的情况下执行。例如,使用SELECT语句拼接的常量表达式:SELECT 1 + 1; SELECT PI()* 2;。另外,使用UPDATE或DELETE语句,当使用WHERE子句指定了 某字段的条件值时,也需要该字段的SELECT权限;否则,你会发现可以使用UPDATE不 带WHERE子句更新全表,却不能使用WHERE语句指定更新某些行记录。对基表或视图 使用EXPLAIN语句,也需要用户对表或视图具有该权限。
Show databases:该权限用于执行SHOW DATABASE语句,对于没有此权限的用户,则只能看到其具有对应访问权限的数据库列表。如果服务器使用了–skip-show- database选项启动,则没有该权限的用户即使对某库有其他访问权限,也不能使用SHOW DATABASES语句查看任何数据库列表(会报错:ERROR 1227 (42000): Access denied; you need (at least one of) the SHOW DATABASES privilege(s) for this operation)。
Show view:该权限用于执行SHOW CREATE VIEW语句。对视图使用EXPLAIN 语句也需要此权限。
Shutdown:该权限用于执行SHUTDOWN语句、mysqladmin shutdown命令和 mysql_shutdown() C API函数。
Super:该权限用于进行如下操作和服务器行为。
■ 修改全局系统配置变量需要此权限。对于某些系统变量,修改会话级别的系统配 置变量也需要Super权限(如果修改会话级别的系统配置变量需要Super权限,在变量的解 释文档中会进行说明,例如binlog_format、sql_log_bin和sql_log_off)。
■ 对全局事务特征的更改(START TRANSACTION语句)。
■ 从库服务器用于执行启动和停止复制的语句,包括组复制。
■ 从库服务器用于执行CHANGE MASTER TO和CHANGE REPLICATION FILTER 语句。
■ 执行PURGE BINARY LOGS和BINLOG语句。
■ 如果视图或存储程序定义了DEFINER属性,则拥有Super权限的用户就算不是该视图或存储程序的创建者,也仍然可以执行该视图或存储程序。
■ 执行CREATE SERVER、ALTER SERVER和DROP SERVER语句。
■ 执行mysqladmin debug命令。
■ 用于InnoDB key自旋。
■ 通过执行DES_ENCRYPT()函数启用读取DES密钥文件。
■ 执行用户自定义函数时启用版本令牌。
■ 超过了最大连接数之后,具有Super权限的账户还可以执行的操作有: 使用KILL语句或mysqladmin kill命令来终止属于其他账户的线程(注意:无论是否拥有Super权限,用户总是可以kill自己的线程)。
即使服务器总连接数达到max_connections系统变量定义的值,服务器也会接受来自具有Super权限的用户的一个额外连接。
即使服务器启用了read_only系统变量,具有Super权限的用户也仍然可以执行数据更新,包括显式的操作更新和隐式的操作更新(账户管理语句GRANT和REVOKE等触发的表更新)。
具有Super权限的用户连接服务器时,服务器不执行init_connect系统变量指定的内容。
处于脱机模式(已启用offline_mode系统变量)的服务器不会中断具有Super权限的 用户的连接,且仍然接受具有Super权限的用户的新连接请求。
■ 如果启用了二进制日志记录功能,则用户可能还需要Super权限才能创建或更改存储的功能。
提示:只向用户授予其需要的权限,不要授予额外的多余的权限,特别是管理权限,例如File、Grant option、Alter、Shutdown、Process、Super等。