5.2 权限授予与回收

当前MySQL就剩system一个系统管理员帐户了，完全不符合业务需求啊，怎么办呢，本节就来着重演示MySQL数据库中如何创建用户、分配权限以及回收权限。

在MySQL数据库里对于用户权限的授予和解除比较灵活，即可以通过专用命令，也可以通过直接操作字典表来实现，正所谓条条道路通目标。不过话说回来，修的马路多不叫奇迹，何况在中国这片神奇的土地，奇迹这个词本身就是奇迹，因此三思真是不好意思用奇迹这样的词来形容：这样想像不到的不平凡的事(注，该段描述为现代汉语词典中关于奇迹一词的解释)，因此，我决定用一种加强的语气来描述我的感受：

比奇迹更神奇的是，这条条大路居然都修成了高速路；

比神奇的奇迹更神奇的是，这些高速居然都是免费的；

比神奇的神奇奇迹更神奇，那就是神迹啊，额地神哪，免费的高速居然也不堵车，这肯定不是二环三环和四环，当然跟G6/G8线应该也没啥关系，至少也是十八环外了，弟兄们，走吧，跟着三思去溜达溜达~~~

再次提示：

很多Linux/Unix下管理MySQL数据库服务的DBA，初看到数据库的管理帐户root就发蒙了，以为这是什么重要的徵兆，其实是大可不必的，此root非彼root，MySQL数据库里的root帐户跟操作系统中的root没有丝毫的关联，只是数据库初始化时自动创建的这个名称而已。在本书第三章初始化数据库时，三思已经手动将该用户更名为了system，我们的操作能够成功，并且未对后续数据库的正常管理带来任何异常，也说明root这个帐户名不具备什么特殊的含义，完全可以随意处理。

基于合适的用户做符合其权限的事的目地，执行与权限相关操作的用户当然也得有权限，默认我们使用的是系统管理员帐户，就是system用户了，本例中所做的用户管理操作，如非特别注明，均是使用mysql中的system用户执行。

 

5.2.1 创建用户

在创建用户之前，首先说明两点：

l 用户名的长度不能超过16个字符；

l 用户名和密码对大小写敏感，也就是说用户Jss和jss是两个不同的用户，密码也是如此；

5.2.1.1 传统方式创建

MySQL中专用的创建用户的命令是CREATE USER，该命令语法如下：

CREATE USER user_specification

    [, user_specification] ...

 

user_specification:

    user

    [

        IDENTIFIED BY [PASSWORD] 'password'

      | IDENTIFIED WITH auth_plugin [AS 'auth_string']

    ]

CREATE USER命令是最传统的创建用户方式，语法看起来还是挺简单的，不过事实上用户权限相关的细节非常有讲究，因为简单，所以灵活，因为灵活，所以可配置性强，因为可配性强，所以细节很重要。

不过，刚开始接触时，大家倒是不用关注太多，从易到难嘛，咱们先按照最简单的方式创建一个名为jss的用户吧，执行操作如下：

(system@localhost) [mysql]> create user jss;

Query OK, 0 rows affected (0.01 sec)

你猜怎么着，成功了！不要担心"0 rows affected"那个提示，对于操作用户这类SQL语句，它的返回就是这样样子，只要不是返回什么ERROR之类提示，就是成功了，如果想看到明确的结果，可以通过查询mysql.user字典表中的记录验证一下：

(system@localhost) [mysql]> select user,host,password from mysql.user where user='jss';

+------+------+----------+

| user | host | password |

+------+------+----------+

| jss  | %    |          |

+------+------+----------+

1 row in set (0.00 sec)

 

当然啦，最好的验证方式仍然是登录测试，我们刚刚创建的用户，即没有设置登录的密码，也没有指定来源主机，因此该用户可以从任意安装了mysql客户端，并能够访问目标服务器的机器上创建连接。

换台装有MySQL客户端的服务器登录试试，例如：

[mysql@mysqldb02 ~]$ mysql -ujss -h 192.168.30.243

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.6.12-log JSS for mysqltest

 

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

(jss@192.168.30.243) [(none)]> 

可以看到当前就是以jss身份连接到30.243服务器。由于前面创建用户时并没有指定任何密码，因此连接时无须指定密码即可顺利登录数据库。

5.2.1.2 修改用户密码

想必读者朋友也都看出来了，这样登录很不安全，密码这个可以有。那么怎么给用户设置密码呢？ALTER USER？NONONO，我们一般都不会这样干，甚至在MySQL 5.6.6版本之前，根本就没有提供ALTER USER这样的语法。“怎么会这样”，您是否在心里暗自问自己这个问题，其实若对MySQL的用户与权限体系有全面的认识，就会明白这种设计，对于MySQL数据库来说是合乎逻辑的。

MySQL数据库中的用户没有太多属性，从前面的CREATE USER语法就能看的出来，与用户相关的选项，除了必须指定的用户名外，就是一个密码选项(唯一一个选项居然还不是必选项)。至于用户权限的授予，则是由单独的SQL命令操作(后面会介绍这些命令)。因此对于用户来说，可能变更的就是用户的密码，针对这一点需求，MySQL没必要整出一个ALTER USER语法，它只需要单独针对修改密码的操作，提供一条命令即可，于是就有了SET PASSWORD命令，该命令语法如下：

SET PASSWORD [FOR user] =

    {

        PASSWORD('some password')

      | OLD_PASSWORD('some password')

      | 'encrypted password'

    }

比如，修改jss用户的密码为5ienet.com，执行命令如下：

(jss@192.168.30.243) [(none)]> set password for jss=password('5ienet.com');

Query OK, 0 rows affected (0.00 sec)

SET PASSWORD命令会自动更新系统授权表，之后再使用jss用户连接MySQL数据库，就必须输入密码才行，否则就会抛出：

ERROR 1045 (28000): Access denied for user 'jss'@'192.168.30.203' (using password: NO)

 

说一下SET PASSWORD命令中各选项的功能：

l SET PASSWORD：固定的语法格式，照着抄即可；

l [FOR user]：FOR选项用于指定要修改密码的用户，如果是修改当前用户的密码，可以不用指定这个选项，如果要修改其它用户(前提是操作者确实有权限)，那么必须通过FOR选项指定要修改的目标用户，格式为user@host；

l PASSWORD/OLD_PASSWORD：这是两个密码专用函数。MySQL数据库中用户密码当然不会是以明文的形式保存，它可不像国内某些专业IT社区那样，打着专业旗号却干出很不专业的事情。MySQL中能够查询到的用户密码是按照它自己的加密逻辑处理后的字符串形式。在修改密码时，也必须指定加密后的字符形式保存，否则登录验证就会碰到异常。可是，都说了是加密后的形式，那我们又怎么能知道字符被加密后是什么形式呢，这里就要分两点来看：

n 第一种是用户确实知道，甭管它是通过什么方式获得的(确实有多种方式)，那么在指定密码时就可以直接指定其加密后的形式；

n 第二种是用户不知道加密后的字符是什么，那么就可以由MySQL来帮助我们生成，MySQL数据库提供了相应的函数PASSWORD()，直接调用该函数即可，这种方式是最常见的调用方式，我们前面的示例中也是采用这种方式。

 

提示：关于OLD_PASSWORD()函数。

这个函数的命名容易产生误解，看起来仿佛是跟用户的旧密码有什么关系，其实不是这样，它只是为了应对MySQL的版本兼容性才出现的。在4.1之前的版本中，PASSWORD()函数生成16位长度的加密字符串，而在之后的版本中，为了提高安全性，MySQL改进了密码的生成算法，现在生成的加密串为41位长度的字符串，那么这就会出现一个兼容性方面的问题，就是将用户使用4.1之前的客户端连接MySQL服务时，就会出现由于加密格式不统一造成的登录失败，为了提高兼容性，MySQL新增加了OLD_PASSWORD()函数，仍然采用原始的加密策略生成16位长度的字符串，管理员在设置用户口令时，就可以使用这个函数生成密码，使其能够兼容4.1之前版本的MySQL客户端。

两个函数处理相同字符串的输出如下：

(jss@192.168.30.243) [(none)]> select password('123456'),old_password('123456');

+-------------------------------------------+------------------------+

| password('123456')                        | old_password('123456') |

+-------------------------------------------+------------------------+

| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 565491d704013245       |

+-------------------------------------------+------------------------+

前面提到，在5.6.6版本之前，MySQL数据库都没有ALTER USER语法，那么为什么后来又增加了ALTER USER，这个语法又能用来做什么呢？为什么增加这个语句我也没想明白，不过这个语句的功能可能要让很多人打死都猜不到。新增的ALTER USER语句的功能，与其它数据库软件中的ALTER USER功能差异巨大，一言以蔽之，就是让用户的密码过期。注意一定要正确理解，是密码过期，而不是用户过期哟。用户仍然可以用(登录)，只是密码过期后，无法做任何操作。

比如说，我们先将jss用户密码设置为过期，执行操作如下：

(system@localhost) [mysql]> alter user jss password expire;

Query OK, 0 rows affected (0.00 sec)

而后再以jss用户登录，用原始密码仍然能够登录成功，但是做操作就不行喽：

(jss@192.168.30.243) [(none)]> show databases;

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

实践过之后，您是否回忆起了什么，或者说您现在应该知道，第二章RPM包方式安装后连接数据库，必须先修改用户密码才能执行操作，是如何实现的了吧。

5.1.2.3 通过登录主机验证用户

话说MySQL数据库中，用户登录除了验证用户名和密码外，不是号称还要检查来源主机呢嘛，怎么前面的登录操作，似乎并未感到有对主机层的验证呢。这个嘛，因为创建用户时就没有指定登录主机啊，没指定，默认就是不限制。不过这个“不限制”指的是不做限制，实际上字典表中还是会有对应的标识，查询一下mysql.user字典表中的信息：

(system@localhost) [(mysql)]> select user,host from mysql.user where user='jss';

+------+------+

| user | host |

+------+------+

| jss  | %    |

+------+------+

1 rows in set (0.00 sec)

注意到这条记录中host列的值了没，显示一个% 百分号。熟悉SQL语法的朋友都知道，%在SQL语法中是做为通配符，代表任意字符串，在这里出现则代表任意主机，这个才是前面所说的不限制登录主机的真正原则。

没错，主机名可以指定通配符，规则与标准的SQL语法中定义完全相同：

l %：对应任意长度的任意字符；

l _：对应一位长度的任意字符；

如果user字典表中的Host列值为空或%，均代表任意主机。因此，如果希望创建的用户只能从某个主机，或某个IP段访问，那么在创建用户时，就必须明确指定host，指定的host即可以是IP，也可以是主机名，或者是可正确解析至IP地址的其它自定义名称。

接下来我们尝试创建一个名为jss_ip的用户，并且该用户仅允许从192.168.30.203的主机连接至MySQL服务端，执行命令如下：

(system@localhost) [(mysql)]> create user jss_ip@'192.168.30.203' identified by 'jss';

Query OK, 0 rows affected (0.00 sec)

这样使用jss_ip用户登录时，只有从192.168.30.203主机发出登录请求才能成功，从非192.168.30.203的主机上，使用jss_ip的用户连接时，不管密码是否正确，都会抛出ERROR 1045 (28000): Access denied错误信息：

$ mysql -ujss_ip -pjss -h 192.168.30.243

ERROR 1045 (28000): Access denied for user 'jss_ip'@'192.168.10.113' (using password: YES)

如果希望192.168.30.%网段的主机均能够使用jss_ip用户连接，又该如何设置呢，这种情况下就该通配符出马了：

(system@localhost) [(none)]> create user jss_ip@'192.168.30.%' identified by 'jss';

Query OK, 0 rows affected (0.00 sec)

而后从192.168.30.%网段的任意主机上尝试连接MySQL服务器，都能够顺利登录：

$ mysql -ujss_ip -pjss -h 192.168.30.243

Welcome to the MySQL monitor.  Commands end with ; or \g.

 

其它大型数据库软件，直接指定用户即可登录数据库，但在MySQL数据库中，则额外还需要有主机这一维度，用户和主机('user'@'host')组成一个唯一帐户，登录MySQL数据库时，实际上是通过帐户进行验证。

由于Host能够支持通配符，使得登录验证时来源主机的部分更加灵活，下表列举了一些User和Host的常见组合，希望能够有助于大家理解。

User列	Host列	对应连接情况
'jss'	'192.168.1.2'	使用jss用户登录时，只有从192.168.1.2主机发出登录请求才能成功创建连接
'jss'	'www.5ienet.%'	使用jss用户登录时，可以从主机名为www.5ienet.(net/com/cn....)的任意主机创建连接
'jss'	'www.5ienet.com'	使用jss用户登录时，只能从主机名为www.5ienet.com的主机发出请求才能成功创建连接
'jss'	'%'	可以从任意主机使用jss用户连接
''	'10.0.0.%'	可以从10.0.0.%网段内的任意主机创建连接，并且无须输入任何用户信息
''	'%'	任意主机均可以创建连接，并且连接过程中无须用户信息

表5-1 用户与主机组合示例

大家是否注意到上表中前几行记录中的用户名都叫jss，不过实际上它们不仅不是同一条记录，甚至不是一个用户，因为MySQL数据库是根据'user'@'host'来唯一一条记录，user表中每一条记录都是一个独立的帐户，每一个独立的帐户都可以拥有各自的权限设置。

这种设计对于初接触MySQL数据库的朋友的确可能带来困扰，因为大家一般都只听过有user，谁能想到这中间还夹着一层host，不过我举个例子大家应该就明白了。比如说您有两位同事，都叫杨伟(user)，一个从山东(host)来，另一个从山西(host)来，您就知道他们肯定不是一个人，这种情况搁现实生活中叫重名，两个确实是各自独立的个体。

“重名”说尽管能够帮助大家理解user+host的组合，不过朋友们可能还是会有疑问，就是重名所带来的现实尴尬，比方说有可能碰到你喊一声“美女”，结果一堆人答应的场景，那MySQL数据库中会不会出现这种情况呢，它又怎么保证一定是那个你想搭讪的姑娘回应呢。按照我的理解，拿这个问题拷问MySQL的智商实在太难为它了，别说MySQL搞不清楚，就是换个活生生的人也搞不定啊，因此，肯定的答复就是，MySQL保证不了。

不过放心啦，MySQL不会返回一堆记录让人无所适从的，因为规矩是限定死的嘛，只能有一条回应，当然啦，它也不会随随便便挑一个给你，做为一款数据库软件，“严谨”是烙印在它的基因中的，MySQL遇到这种情况，会按照既定的规则来处理，处理的规则归根结底就两个字：排序，而后从排好序的结果中取第一条记录。

MySQL在排序时会将最明确的Host值放在前面，比如说某个具体的主机名或IP地址就非常明确，而像通配符"%"就是最不明确的代表(它代表任意主机)，排序时会放在后面，空字符串''尽管也表示任意主机，但排序的优先级比'%'更低，它会放在最后。对于Host相同的记录，MySQL会再按照User列中的值排序，规则与Host完全相同，都是最明确的值放在最前面。

举例来说，user字典表中有下列的记录：

+-----------+----------+-

| Host      | User     | ...

+-----------+----------+-

| %         | system   | ...

| %         | jss      | ...

| localhost | system   | ...

| localhost |          | ...

+-----------+----------+-

按照MySQL数据库的规则，排序好之后的结果类似这样：

+-----------+----------+-

| Host      | User     | ...

+-----------+----------+-

| localhost | system   | ...

| localhost |          | ...

| %         | jss      | ...

| %         | system   | ...

+-----------+----------+-

 

提示：

排序是什么时候做的呢？要知道，MySQL在服务启动时就会将user表读取到内存中，在读取的过程中就会排序。MySQL服务运行过程中，修改用户权限触发权限更新时，会刷新内存中的字典表，这期间又会进行排序，也就是内存中的字典表永远都是排好序的。

客户端创建连接时使用的用户名和主机，有可能同时匹配user表中的多条记录，在上面给出的例子中，使用system用户登录就有可能即匹配system@'localhost'，又匹配system@'%'两条记录，按照前面介绍的规则，如果是在localhost本地执行登录，那么一定会匹配为system@'localhost'这个用户，否则的话，则会是system@'%'这个用户了。

再给一个例子，user表中有如下两条记录：

+----------------+----------+-

| Host           | User     | ...

+----------------+----------+-

| www.5ienet.com |          | ...

| %              | jss      | ...

+----------------+----------+-

当用户使用jss用户并且从"www.5ienet.com"主机登录MySQL数据库时，会匹配第一条记录，如果是从其它主机登录的话则是匹配第二条记录。实际上，从www.5ienet.com主机登录MySQL的话，是否指定用户根本就没有区别，因为，"www.5ienet.com"这个已经非常明确，并且user列值为空字串，也就代表着只要是从www.5ienet.com主机发出的登录请求，不管指定的用户是什么(甚至可以是user表中不存在的用户)，均会匹配为这条记录。 

5.1.2.4 GRANT方式创建用户

CREATE USER只是创建用户的高速路之一，如果你觉着这条道路实在太过平坦，路边风景太过平淡，行程太过平常，不妨在抵达目的地之前，拐弯开上GRANT大道，饱览不一样的风景。

GRANT命令并非本小节重点，这里仅简要描述一下其语句中与用户相关的部分：

GRANT priv_clause TO user [IDENTIFIED BY [PASSWORD] 'password'] ...

与创建用户相关的语法，看起来跟CREATE USER是差不多的嘛，事实上当然不是差不多，根本就是一模一样嘛，下面举个例子，操作如下：

(system@localhost) [(mysql)]> grant select on jssdb.* to jss_grant@192.168.30.203 identified by 'jss';

Query OK, 0 rows affected (0.00 sec)

 

(system@localhost) [(mysql)]> select user,host,password from mysql.user where user ='jss_grant';

+-----------+----------------+-------------------------------------------+

| user      | host           | password                                  |

+-----------+----------------+-------------------------------------------+

| jss_grant | 192.168.30.203 | *284578888014774CC4EF4C5C292F694CEDBB5457 |

+-----------+----------------+-------------------------------------------+

1 row in set (0.00 sec)

上述语句在实现了前面第3个例子(创建用户jss_ip)的功能外，还额外授予了jss_grant用户查询mysql.user表的权限。MySQL的开发团队靠着永不屈服、永不放弃、永不退缩、永不言败的奋争精神，用智慧和巧妙的构思完美复制了ORACLE GRANT语句的功能，这是全世界默默无闻的MySQL开发人员长期以来内生品格的自然流露，是全世界默默无闻的MySQL开发人员开拓前进的不竭动力，这就是传说中的，瑞典梦。

 

5.1.2.5 另类方式创建用户

如果说上述方式觉着都不顺手，或者，大脑短路导致短暂忘记了命令的语法，那也没关系，mysql.user表还记得吧，直接向该字典表中插入记录(一般insert语法想忘不容易)，也是靠谱的，例如：

(system@localhost) [(none)]> insert into mysql.user (host,user,password,ssl_cipher,x509_issuer,x509_subject) values ('192.168.30.203','jss_insert',password('jss'),'','','');

Query OK, 1 row affected (0.00 sec)

 

 

(system@localhost) [(none)]> select user,host,password from mysql.user where user ='jss_insert';

+------------+----------------+-------------------------------------------+

| user       | host           | password                                  |

+------------+----------------+-------------------------------------------+

| jss_insert | 192.168.30.203 | *284578888014774CC4EF4C5C292F694CEDBB5457 |

+------------+----------------+-------------------------------------------+

1 row in set (0.00 sec)

手动修改权限字典表后，需要执行FLUSH PRIVILEGES语句，重新加载授权信息到内存中，否则手动修改的权限不会生效，执行操作如下：

(system@localhost) [none]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

接下来可以尝试从192.168.30.203主机，分别使用jss_insert用户和jss_ip登录，对比看看效果，不仅看起来相同，实际表现也是一模一样。

这点跟ORACLE数据库就截然不同了，ORACLE这类数据库是绝对不建议用户修改数据字典表的，而且一般情况下也不知道都应该改哪些地方(没错，完全可能不止一处需要修改)，因此对于ORACLE数据库，最安全最稳妥也最快捷的方式，还是老老实实按照ORACLE提供的命令进行操作，而MySQL则完全不同，官方不仅完全不介意用户通过操作字典表的方式进行功能修改(想想也是，连软件都是开源的，在这种地方设什么障碍也没有意义)，甚至鼓励通过这种方式，话说回来，截止到MySQL5.6.12版本，都没还没有提供修改"用户属性"的ALTER USER的语法，因此如果想对用户属性做修改，直接update mysql.user表就算是比较便捷的方式了。

当然啦，MySQL中的用户其实也没什么属性可供修改，大多都是权限，唯一称的上属性又有修改可能的，就是用户的密码信息了，前面介绍过SET PASSWORD语句，专用于修改用户密码非常专业，但是它并不是唯一的方法，在MySQL数据库中，我们可以使用更加直接的方式。实际上之前我们就这么干过，还记的第三章中修改过root用户密码时所做的操作吗，没做，用户的信息保存在mysql.user字典表中，我们直接修改该表也是一样的。

例如，直接修改字典表，将jss用户的密码变更为123456，执行操作如下：

(system@localhost) [(none)]> update mysql.user set password=password('123456') where user='jss' and host='%';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0