十二、MySQL用户管理

　　MySQL是一个多用户数据库，具有功能强大的访问控制系统，可以为不同用户指定允许的权限。MySQL权限可以分为普通用户和root用户。root用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户的密码等管理权限；普通用户只拥有被授予的各种权限。用户管理包括管理用户账户、权限等。

一、权限表

　　MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在MySQL数据库中，由MySQL_install_db脚本初始化。存储账户权限表主要有：user、db、host、tables_priv、columns_priv和procs_priv。

1.1 user表

　　user表是MySQL中最重要的一个权限表，记录允许连接到服务器的账号信息，里面的权限是全局级的。MySQL 5.7中user表有40多个字段，这些字段可以分为4类，分别是用户列、权限列、安全列和资源控制列。

 

1.1.1 用户列

　　user表的用户列包括Host、User、Password，分别表示主机名、用户名和密码。其中User和Host为user表的联合主键。当用户与服务器之间建立连接时，输入的账户信息中的用户名称、主机名和密码必须匹配user表中的对应字段，只有3个值都匹配的时候，才允许连接建立。这3个字段的值就是创建账户时保存的账户信息。修改密码时，实际就是修改user表的password字段的值。

1.1.2 权限列

　　权限列的字段决定了用户的权限，描述了在全局范围内允许对数据和数据库进行的操作。包括查询权限、修改权限等普通权限，还包括了关闭服务器、超级权限和加载用户等高级权限。普通权限用于操作数据库，高级权限用于数据库管理。

　　user表中对应的权限是针对所有用户数据库的。这些字段值的类型为ENUM，可以取的值只能为Y和N，Y表示该用户有对应的权限，N表示该用户没有对应的权限。查看user表的结构可以看到，这些字段的值默认都是N。如果要修改权限，可以使用GRANT语句或UPDATE语句更改user表的这些字段来修改用户对应的权限。

1.1.3 安全列

　　安全列只有6个字段，其中两个是ssl相关的两个是x509相关的，另外两个是授权插件相关的。ssl用于加密；x509标准可用于标识用户；Plugin字段标识可以用于验证用户身份的插件，如果该字段为空，服务器使用内建授权验证机制验证用户身份。读者可以通过SHOW VARIABLES LIKE 'have_openssl'语句来查询服务器是否支持ssl功能。

1.1.4 资源控制列

　　资源控制列的字段用于限制用户使用的资源，包含4个字段，分别为：

　　（1）max_questions——用户每小时允许执行的查询操作次数。

　　（2）max_updates——用户每小时允许执行的更新操作次数。

　　（3）max_connections——用户每小时允许执行的连接操作次数。

　　（4）max_user_connections——用户允许同时建立的连接次数。

　　一个小时内用户查询或者连接数量超过资源控制限制，用户将被锁定，直到下一个小时才可以在执行对应的操作。可以使用GRANT语句更新这些字段的值。

1.2 db表

　　db表是MySQL数据库中非常重要的权限表。db表中存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库。db表比较常用，字段大致可以分为两类：用户列和权限列。

 1.2.1 用户列

　　db表用户列有3个字段，分别是Host、User、Db，标识从某个主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键。

1.2.2 权限列

　　db表中create_routine_priv和alter_routine_priv这两个字段表明用户是否有创建和修改存储过程的权限。

　　user表中的权限是针对所有数据库的，如果希望用户只对某个数据库有操作权限，那么需要将user表中对应的权限设置为N，然后在db表中设置对应数据库的操作权限。

1.3 tables_priv表和columns_priv表

　　tables_priv表用来对表设置操作权限，columns_priv表用来对表的某一列设置权限。

　　tables_priv表有8个字段，各个字段的说明如下：

　　（1）Host、Db、User和Table_name4个字段分别表示主机名、数据库名、用户名和表名。

　　（2）Grantor表示修改该记录的用户。

　　（3）Timestamp字段表示修改该记录的时间。

　　（4）Table_priv表示对表的操作权限，包括Select、Insert、Update、Delete、Create、Drop、Grant、References、Index、Alter、Create View、Show view、Trigger等。

　　（5）Column_priv字段表示对表中的列的操作权限，包括Select、Insert、Update和References。

　　columns_priv表只有7个字段，其中Column_priv用来指定对哪些数据列具有操作权限。

1.4 procs_priv表

　　procs_priv表可以对存储过程和存储函数设置操作权限。

　　procs_priv表包含8个字段，各个字段的说明如下：

　　（1）Host、Db和User字段分别表示主机名、数据库名和用户名。Routine_name表示存储过程或函数的名称。

　　（2）Routine_type表示存储过程或函数的类型。Routine_type字段有两个值，分别是FUNCTION和PROCEDURE。FUNCTION表示这是一个函数；PROCEDURE表示这是一个存储过程。

　　（3）Grantor是插入或修改该记录的用户。

　　（4）Proc_priv表示拥有的权限，包括Execute、Alter Routine、Grant3种。

　　（5）Timestamp表示记录更新时间。

二、账户管理

　　MySQL提供许多语句用来管理用户账号，这些语句可以用来管理包括登录和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。MySQL数据库的安全性，需要通过账户管理来保证。

2.1 登录和退出MySQL服务器

　　通过MySQL --help命令可以查看MySQL命令帮助信息。MySQL命令的常用参数如下：

　　（1）-h主机名，可以使用该参数指定主机名或ip，如果不指定，默认是localhost。

　　（2）-u用户名，可以使用该参数指定用户名。

　　（3）-p密码，可以使用该参数指定登录密码。如果该参数后面有一段字符串，则该段字符串将作为用户的密码直接登录。如果后面没有内容，则登录的时候会提示输入密码。注意：该参数后面的字符串与-p之间不能有空格。

　　（4）-P端口号，该参数后面接MySQL服务器的端口号，默认为3306.

　　（5）数据库名，可以在命令的最后面指定数据库名。

　　（6）-e执行SQL语句。如果指定了该参数，将在登录后执行-e后面的命令或SQL语句并推出。

2.2 新建普通用户

　　创建新用户，必须有相应的权限来执行创建操作。在MySQL数据库中，有两种方式创建新用户：一种是使用CREATE USER或GRANT语句；另一种是直接操作MySQL授权表。最好的方法是使用GRANT语句，因为这样更精确，错误少。

2.2.1 使用CREATE USER语句创建新用户

　　执行CREATE USER或GRANT语句时，服务器会修改相应的用户授权表，添加或者修改其权限。CREATE USER语句的基本语法格式如下：

CREATE USER user_specification
    [,user_specification] ...


user_specifition:
    user@localhost
    [
        IDENTIFIED BY [PASSWORD] 'password'
        |IDENTIFIED WITH auth_plugin [AS 'auth_string']
    ]

　　user表示闯进的用户的名称；host表示允许登录的用户主机名称；IDENTIFIED BY表示用来设置用户的密码；[PASSWORD]表示使用哈希值设置密码，该参数可选；‘password’表示用户登录时使用的普通明文密码；IDENTIFIED WITH语句为用户指定一个身份验证插件；auth_plugin是插件的名称，插件的名称可以是一个带引号的字符串；auth_string是可选的字符串参数，该参数将传递给身份验证插件，由该插件解释该参数的意义。

　　CREATE USER语句会添加一个新的MySQL账户。使用CREATE USER语句的用户，必须有全局的CREATE USER权限或MySQL数据库的INSERT权限。每添加一个用户，CREATE USER语句会在MySQL.user表中添加一条新纪录，但是新创建的账户没有任何权限。如果添加的账户已经存在，CREATE USER语句会返回一个错误。

　　IDENTIFIED WITH只能在MySQL 5.5.7及以上版本中使用。IDENTIFIED BY和IDENTIFIED WITH是互斥的，所以对于一个账户来说只能使用一个验证方法。CREATE USER语句的操作会被记录到服务器日志文件和操作历史文件中，如~/.MySQL_history。这意味着对这些文件有读取权限的人，都可以读取新添加用户的明文密码。

　　MySQL的某些版本中会引入授权表的结构变化，添加新的特权或功能。每当更新MySQL到一个新的版本时，应该更新授权表，以确保它们有最新的结构，确认可以使用任何新功能。

2.2.2 使用GRANT语句创建新用户

　　CREATE USER语句可以用来创建账户，通过该语句可以在user表中添加一条新的记录，但是CREATE USER语句创建的新用户没有任何权限，还需要使用GRANT语句赋予用户权限。而GRANT语句不仅可以创建新用户，还可以在创建的同时对用户授权。GRANT还可以指定账户的其他特点，如使用安全连接、限制使用服务器资源等。使用GRANT语句创建新用户时必须有GRANT权限。GRANT语句是添加新用户并授权他们访问MySQL对象的首选方法，GRANT语句的基本语法格式如下：

GRANT privileges ON db.table
TO user@host [IDENTIFIED BY 'password'] [, user [IDENTIFIED BY 'password']] [WITH GRANT OPTION];

　　其中，privileges表示赋予用户的权限类型；db.table表示用户的权限所作用的数据库中的表；IDENTIFIED BY关键字用来设置密码；'password'表示用户密码；WITH GRANT OPTION为可选参数，表示对新建立的用户赋予GRANT权限，即该用户可以对其他用户赋予权限。

2.2.3 直接操作MySQL用户表

　　不管是CREATE USER或者GRANT，在创建新用户时，实际上都是在user表中添加一条新的记录。因此，可以使用INSERT语句向user直接插入一条记录来创建一个新的用户。使用INSERT语句，必须拥有对MySQL.user表的INSERT权限。使用INSERT语句创建新用户的基本语法格式如下：

INSERT INTO MySQL.user(Host,User,Password,[privilegelist]) VALUES('host','username',PASSWORD('password'),privilegevaluelist);

　　Host、User、Password分别为user表中的主机、用户名称和密码字段；privilegelist表示用户的权限，可以有多个权限；PASSWORD()函数为密码加密函数；privilegevaluelist为对应的权限的值，只能取‘Y’或者‘N’。

2.3 删除普通用户

　　在MySQL数据库中，可以使用DROP USER语句删除用户，也可以直接通过DELETE语句从MySQL.user表中删除对应的记录来删除用户。

2.3.1 使用DROP USER语句来删除用户

DROP USER user [,user]

　　DROP USER语句用于删除一个或多个MySQL账户。要使用DROP USER，必须拥有MySQL数据库的全局CREATE USER权限或DELETE权限。使用与GRANT或REVOKE相同的格式为每个账户命名：'user'@'localhost'。

　　DROP USER不能自动关闭任何打开的用户对话。而且，如果用户有打开的对话，此时取消用户，命令则不会生效，直到用户对话关闭后才能生效。一旦对话被关闭，用户也被取消，此用户再次试图登录时将会失败。

2.3.2 使用DELETE语句删除用户

DELETE FROM MySQL.user WHERE host='hostname' and user='username';

　　host和user为user表中的两个字段，两个字段的组合确定所要删除的用户记录。

2.4 root用户修改自己的密码

　　root用户的安全对于保证MySQL的安全非常重要，因为root用户拥有很高的权限。修改root用户密码的方式有多种。

2.4.1 使用MySQLadmin命令在命令行指定新密码

mysqladmin -u username -h localhost -p password 'newpwd'

　　user为要修改密码的用户名称。在这里指定为root用户；参数-h指需要修改的、对应哪个主机用户的密码，该参数可以不写，默认是localhost；-p表示输入当前密码；password为关键字，后面双引号内的内容“newpwd”为新设置的密码。

2.4.2 修改MySQL数据库的user库

　　因为所有账户信息都保存在user表中，因此可以通过直接修改user表来改变用户的密码。root用户登录到MySQL服务器后，使用UPDATE语句修改MySQL数据库的user表的password字段，从而修改用户的密码。

UPDATE mysql.user SET password=PASSWORD('rootpwd') WHERE user='root' AND host='localhost';

　　PASSWORD()函数用来加密用户密码。执行UPDATE语句后，需要执行FLUSHPRIVILEGES语句重新加载用户权限。

2.4.3 使用SET语句修改root用户的密码

　　SET PASSWORD语句可以用来重新设置其他用户的登录密码或者自己使用的账户的密码。

SET PASSWORD=PASSWORD('rootpwd')

　　新密码必须使用PASSWORD()函数加密。

2.5 root用户修改普通用户的密码

　　root用户拥有很高的权限，不仅可以修改自己的密码，还可以修改其他用户的密码。root用户登录MySQL服务器后，可以通过SET语句修改MySQL.user表，以及GRANT语句修改用户的密码。

2.5.1 使用SET语句修改普通用户的密码

SET PASSWORD FOR 'user'@'host' = PASSWORD('somepassword');

　　只有root可以通过更新MySQL数据库的用户来更改其他用户的密码。如果使用普通用户修改，可省略FOR子句更改自己的密码。

2.5.2 使用UPDATE语句修改普通用户的密码

　　使用root用户登录到MySQL服务器后，可以使用UPDATE语句修改MySQL数据库的user表的password字段，从而修改普通用户的密码。

UPDATE MySQL.user SET password=PASSWORD("newpwd2") WHERE user="testuser" AND host="localhost";

2.5.3 使用GRANT语句修改普通用户密码

　　使用GRANT语句修改密码，必须拥有GRANT权限。一般情况下最好使用这种方法来指定或修改密码。

GRANT USAGE ON *.* TO 'someuser'@'%' IDENTIFIED BY 'somepassword';

　　如果使用GRANT......IDENTIFIED BY语句或MySQLadmin password命令设置密码，它们均会加密密码。在这种情况下，不需要使用PASSWORD()函数。

2.6 普通用户修改密码

　　普通用户登录MySQL服务器后，通过SET语句设置自己的密码。

　　SET语句修改自己密码的基本语法如下：

SET PASSWORD=PASSWORD('newpassword');

　　其中，PASSWORD()函数对密码加密，'newpassword'是设置的新密码。

2.7 root用户密码丢失的解决办法

　　对于root用户丢失密码这种特殊情况，MySQL实现了对应的处理机制。可以通过特殊方法登录到MySQL服务器，然后再root用户下重新设置密码。执行步骤如下：

2.7.1 使用--skip-grant-tables选项启动MySQL服务

　　以skip-grant-tables选项启动时，MySQL服务器将不加载权限判断，任何用户都能访问数据库。在windows操作系统中，可以使用MySQLd或者MySQLd-nt来启动MySQL服务进程。

mysqld --skip-grant-tables

　　或者：

mysqld-nt --skip-grant-tables

　　在Linux操作系统中，使用MySQL_safe来启动MySQL服务。也可以使用/etc/init.d/MySQL命令来启动MySQL服务。

mysql_safe --skip-grant-tables user=mysql

　　或者：

/etc/init.d/mysql start-mysqld --skip-grant-tables

　　启动MySQL服务后，就可以使用root用户登录了。

2.7.2 使用root用户登录，重新设置密码

　　在这里使用的平台为Windows 7，操作步骤如下：

　　（1）使用net stop MySQL命令停止MySQL服务进程。

net stop mysql

　　（2）在命令行输入MySQLd --skip-grant-tables选项启动MySQL服务。

mysqld --skip-grant-tables

　　（3）打开另外一个命令行窗口，输入不加密码的登录命令。

mysql -u root

　　登录成功后，可以使用UPDATE语句或者使用MySQLadmin命令重新设置root密码，设置密码语句如下：

UPDATE mysql.user SET password=PASSWORD('newpwd') WHERE User='root' AND Host='localhost';

2.7.3 加载权限表

　　修改密码完成后，必须使用FLUSH PRIVILEGES语句加载权限表。加载权限表后，新的密码才会生效，同时MySQL服务器开始权限验证。输入语句如下：

FLUSH PRIVILEGES;

　　修改密码完成后，将输入MySQLd --skip-grant-tables命令的命令行窗口关闭，接下来就可以使用新设置的密码登录MySQL了。

三、权限管理

　　权限管理主要是对登录到MySQL的用户进行权限验证。所有用户的权限都存储在MySQL的权限表中，不合理的权限规划会给MySQL服务器带来安全隐患。数据库管理员要对所有用户权限进行合理规划管理。MySQL权限系统的主要功能是证实连接到一台给定主机的用户，并且赋予该用户在数据库上的SELECT、INSERT、UPDATE和DELETE权限。

3.1 MySQL的各种权限

　　账户权限信息被存储在MySQL数据库的user、db、host、tables_priv、columns_priv和procs_priv表中。在MySQL启动时，服务器会将这些数据库表中的权限信息的内容读入内存。

　　GRANT和REVOKE语句所涉及的权限的名称如下表所示：

　　总的来说，只授予权限给需要他们的那些用户。

3.2 授权

　　授权就是为某个用户授予权限。合理的授权可以保证数据库的安全。MySQL中可以使用GRANT语句为用户授予权限。

　　授予的权限可以分为多个层次：

3.2.1 全局层级

　　全局权限适用于一个给定服务器中的所有数据库。这些权限存储在MySQL.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销数据库权限。

3.2.2 数据库层级

　　数据库权限适用于一个给定数据库中的所有目标。这些权限存储在MySQL.db和MySQL.host表中。GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限。

3.2.3 表层级

　　表权限适用于一个给定表中的所有列。这些权限存储在MySQL.tables_priv表中。GRANT ALL ON db_name.tb1_name和REVOKE ALL ON db_name.tb1_name只授予和撤销表权限。

3.2.4 列层级

　　列权限适用于一个给定表中的单一列。这些权限存储在MySQL.column_priv表中。当使用REVOKE时，必须指定与被授权列相同的列。

3.2.5 子程序层级

　　CREATE ROUTINE、ALTER ROUTINE、EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予子程序层级，并存储在MySQL.procs_priv表中。

3.3 收回权限

　　收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限在一定程度上保证系统的安全性。MySQL中使用REVOKE语句取消用户的某些权限。使用REVOKE收回权限之后，用户账户的记录将从db、host、tables_priv和column_priv表中删除，但是用户账号记录仍然在user表中保存。

　　在将用户记录从user表删除之前，应该收回相应用户的所有权限，REVOKE语句有两种语法格式，第一种语法是收回所有用户的所有权限，此语法用于取消对于已命名的用户的所有全局层级、数据库层级、表层级和列层级的权限。

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host' [, 'user'@'host',...]

　　REVOKE语句必须和FROM语句一起使用，FROM语句指明需要收回权限的用户。

　　另一种为长格式的REVOKE语句，基本语法如下：

REVOKE priv_type [(columns)] [,priv_type [(columns)]] ...
ON table1,table2,..., tablen
FROM 'user'@'host' [, 'user'@'host' ,...]

　　该语法收回指定的权限。其中，priv_type参数表示权限类型；columns参数表示权限作用于哪些列上，如果不指定该参数，表示作用于整个表；table1,table2,...,tablen表示从哪个表中收回权限；'user'@'host'参数表示用户账户，由用户名和主机名构成。

　　要使用REVOKE语句，必须拥有MySQL数据库的全局CREATE USER权限或UPDATE权限。

3.4 查看权限

　　SHOW GRANTS语句可以显示指定用户的权限信息，使用SHOW GRANTS查看账户信息的基本语法格式为：

SHOW GRANTS FOR 'user'@'host';

　　其中，user表示登录用户的名称，host表示登录的主机名称或ip地址。

　　在这里，只是定义了个别的用户权限，GRANT可以显示更加详细的权限信息，包括全局级的和非全局级的权限，如果表层级和列层级的权限被授予用户的话，它们也能在结果中显示出来。

　　在前面创建用户时，查看新建的账户时使用SELECT语句，也可以通过SELECT语句查看user表中的各个权限字段以确定用户的权限信息，其基本语法格式如下：

SELECT privileges_list FROM user WHERE user='username' ,host='hostname';

　　其中，privileges_list为想要查看的权限字段，可以为Select_priv、Insert_priv等。

四、控制访问

　　正常情况下，并不希望每个用户都可以执行所有的数据库操作。当MySQL允许一个用户执行各种操作时，它将首先核实该用户向MySQL服务器发送的连接请求，然后确认用户的操作请求是否被允许。

4.1 连接核实阶段

　　当连接MySQL服务器时，服务器基于用户的身份以及用户是否通过正确的密码身份验证来接受或拒绝连接。然后进入阶段2等待用户请求。

4.2 请求核实阶段

　　建立连接之后，服务器进入访问控制的阶段2。对再此连接上的每个请求，服务器检查用户要执行的操作，然后检查是否有足够的权限来执行它。这正是授权表中的权限列发挥作用的地方。

　　确认权限后，MySQL首先检查user表，如果指定的权限没有在user表中被授权；MySQL将检查db表，db表是下一安全层级，其中的权限限定于数据库层级，在该层级的SELECT权限允许用户查看指定数据库的所有表中的数据；如果在该层级没有找到限定的权限，则MySQL继续检查tables_priv和columns_priv表，如果所有权限表都检查完毕，但还是没有找到允许的权限操作，MySQL将返回错误信息，用户请求的操作不能执行，操作失败。

　　MySQL通过向下层级的顺序检查权限表，但并不是所有的权限都要执行此过程。例如：一个用户登录到MySQL服务器之后只执行对MySQL的管理操作，此时，只涉及管理权限，因此MySQL只检查user表。另外，如果请求的权限操作不被允许，MySQL也不会继续检查下一层级的表。