MySQL权限控制部分一些细节
//
MySQL权限控制部分回顾
//
关于权限部分的内容，之前3月11号的文章中有写过一些，今天的内容，我们使用一个一个的细节知识点来撰写（本文中所使用的MySQL版本是5.7.16），在写这些知识点之前，我们首先介绍一下MySQL的权限控制粒度、然后了解一下MySQL中客户端发起请求的时候，服务端所做的核实工作，先来看权限控制粒度：
1、全局层级
全局权限使用于给一个给定服务器中的所有数据库，这些权限存储在mysql.user表中，使用grant all on *.*的方法和revoke all on *.*的方法授予或者回收权限。
2、数据库层级
数据库权限适用于一个给定数据库中的所有目标，包含表对象和存储过程，这些权限存储在mysql.db表中，使用grant all on db_name.*或者对应的revoke方法可以授予和回收数据库权限
3、表层级
表权限适用于一个给定表中的所有列，这些权限存储在mysql的tables_priv表中，一般使用grant all on db_name.tbl_name和对应的revoke语句来授予或者撤销权限。
4、列层级的权限
列层级的权限适用于一个给定表中的指定列，这些权限存储在mysql.columns_priv的表中，由于这个权限不常用，这里给出它的授权方法示例，如下：
首先我们创建一个用户，拥有yeyztest.test1这个表的select权限：
mysql:mysql 19:35:38>>show grants for dba_yeyz@'192.168.18.%' ;
+------------------------------------------------------------------------------+
| Grants for dba_yeyz@192.168.18.% |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dba_yeyz'@'192.168.18.%' |
| GRANT SELECT ON `yeyztest`.`test1` TO 'dba_yeyz'@'192.168.18.%' |
+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
然后我们多test1中的一个字段id进行update操作，结果如下：
mysql> select * from test1;
+---------------+
| id |
+---------------+
| 22 |
| 3333333333333 |
+---------------+
2 rows in set (0.00 sec)
mysql> update test1 set id=2 where id=22;
ERROR 1142 (42000): UPDATE command denied to user 'dba_yeyz'@'192.168.18.**' for table 'test1'
当然，我们是不能进行update的，这个时候，我们使用root账号给这个dba_yeyz的账号一个id列的权限，然后再看它的结果：
mysql:mysql 19:38:38>>show grants for dba_yeyz@'192.168.18.%' ;
+------------------------------------------------------------------------------+
| Grants for dba_yeyz@192.168.18.% |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dba_yeyz'@'192.168.18.%' |
| GRANT SELECT ON `yeyztest`.`test1` TO 'dba_yeyz'@'192.168.18.%' |
| GRANT SELECT, UPDATE (id) ON `yeyztest`.`test1` TO 'dba_yeyz'@'192.168.18.%' |
+------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
这里需要注意给字段添加权限的语句，也就是：
grant update (id) on yeyztest.test1 to XXXXXX
也就是在权限后面跟上字段的名称。
这个时候，我们查询一下columns_priv的表，可以看到里面的记录是：
mysql:mysql 19:39:46>>select * from columns_priv;
+--------------+----------+----------+------------+-------------+---------------------+-------------+
| Host | Db | User | Table_name | Column_name | Timestamp | Column_priv |
+--------------+----------+----------+------------+-------------+---------------------+-------------+
| 192.168.18.% | yeyztest | dba_yeyz | test1 | id | 0000-00-00 00:00:00 | Update |
+--------------+----------+----------+------------+-------------+---------------------+-------------+
1 row in set (0.00 sec)
再次用dba_yeyz进行update操作，可以看到结果：
mysql> update test1 set id=2 where id=22;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select *from test1;
+---------------+
| id |
+---------------+
| 2 |
| 3333333333333 |
+---------------+
2 rows in set (0.00 sec)
将id=22的列成功改成了id=2。
5、子程序层级
create routine、alter routine、execute和grant权限适用于已经存储的子程序，这些权限可以被授予为全局层级和数据库层级，可以被存储在mysql.procs_priv中。
客户端发起请求的时候，MySQL服务器核实请求时候的流程图如下：
我将今天看到的一些细节的知识点一个一个罗列出来，希望对大家有点儿帮助：
1、在MySQL5.7.16版本中，mysql系统库中已经没有host表了，跟权限控制相关的表只有5个，分别是user、db、table_priv、proc_priv、column_priv。
2、mysql.user表的主键是用user和host联合起来组成的，且看表结构：
mysql--dba_admin@127.0.0.1:mysql 19:44:56>>show create table mysql.user\G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
---------------权限字段（29个）--------------
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
......此处省略
---------------安全字段（4个）---------------
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
---------------资源控制字段（4个）--------------
`max_questions` int(11) unsigned NOT NULL DEFAULT '0',
`max_updates` int(11) unsigned NOT NULL DEFAULT '0',
`max_connections` int(11) unsigned NOT NULL DEFAULT '0',
`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
--------------插件字段（1个）---------------
`plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',
--------------密码字段（5个）--------------
`authentication_string` text COLLATE utf8_bin,
`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`password_last_changed` timestamp NULL DEFAULT NULL,
`password_lifetime` smallint(5) unsigned DEFAULT NULL,
`account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`User`) -------------联合主键，host在前-----------
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)
3、tables_priv表中只有以下权限，也就是关于表的权限：
select、insert、update、delete、drop、create、alter、grant、references、index、create view、show view、trigger；
columns_priv表中的权限只有下面四个：select、insert、update，references
4、修改一个用户的密码，一般有以下几种方式：
set password for user@host = password('newpassword');
update mysql.user set authentication_string=password('pwd') where user='username' and host='hostname';
alter user user@host identified by 'newpassword';
mysqladmin -u username -h hostname -p password "new password";
最好的方式是alter user的方法，事实上，在新的版本8.0中，set password的方法已经不能使用了，所以建议使用alter user的方法设置新的密码。
除此之外，还有一种方法，不太常用，就是使用grant的方法覆盖掉之前的密码，这里我们简单实验一般，看看效果：
mysql:mysql 20:01:05>>GRANT SELECT ON `yeyztest`.`test` TO 'dba_yeyz'@'192.168.18.%' identified by '111111';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql:mysql 20:01:29>>select user,host,concat(user,'@','\'',host,'\''),authentication_string from mysql.user;
+------------------+----------------+-----------------------------------+-------------------------------------------+
| user | host | concat(user,'@','\'',host,'\'') | authentication_string |
+------------------+----------------+-----------------------------------+-------------------------------------------+
| dba_yeyz | 192.168.18.% | dba_yeyz@'192.168.18.%' | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
+------------------+----------------+-----------------------------------+-------------------------------------------+
11 rows in set (0.00 sec)
mysql:mysql 20:01:31>>GRANT SELECT ON `yeyztest`.`test` TO 'dba_yeyz'@'192.168.18.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql:mysql 20:01:57>>select user,host,concat(user,'@','\'',host,'\''),authentication_string from mysql.user;
+------------------+----------------+-----------------------------------+-------------------------------------------+
| user | host | concat(user,'@','\'',host,'\'') | authentication_string |
+------------------+----------------+-----------------------------------+-------------------------------------------+
| dba_yeyz | 192.168.18.% | dba_yeyz@'192.168.18.%' | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------------------+----------------+-----------------------------------+-------------------------------------------+
1 rows in set (0.00 sec)
上面的测试可以看到，当我们使用grant的方法给一个指定的用户重新设置密码的时候，之前的老密码会被覆盖，所以这个操作在线上应该慎用，在每次grant的时候看看有没有已经存在的账号，确认没有之后，再进行grant操作
5、如果我们不慎忘记了mysql的root密码，可以重新启动mysql服务，加上--skip-grant-tables这个参数来启动mysql服务，这样就可以直接免除了在权限表里面的匹配工作，直接登陆进mysql服务中，从而修改root账号的密码。
6、如果使用update或者insert记录到mysql.user表中的方法创建账户或者修改密码，在执行完语句之后，必须使用flush privileges的操作刷新权限表，否则该操作无法产生效果。
7、有几个权限会影响mysqladmin工具的执行，分别是
reload权限：影响flush操作
shutdown权限：影响shutdown操作
process权限：影响processlist操作
super权限：影响kill操作
8、之前提到了mysql.user表中的资源控制的字段，分别是
max_questions每小时最大请求数、max_updates每小时最大更新数、max_connections每小时最大连接数、max_user_connections单个用户可同时建立的最大连接数。
如果我们想给一个用户设置这个参数，可以使用如下的SQL来进行设置：
mysql:mysql 20:01:58>>GRANT SELECT ON `yeyztest`.`test` TO 'dba_yeyz'@'192.168.18.%' with max_queries_per_hour 1000;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql:mysql 20:13:13>>select user,host,max_questions from mysql.user where user='dba_yeyz';
+----------+--------------+---------------+
| user | host | max_questions |
+----------+--------------+---------------+
| dba_yeyz | 192.168.18.% | 1000 |
+----------+--------------+---------------+
1 row in set (0.00 sec)
注意到，这里的grant语句中使用了with这个选项，with后面可以跟的选项有5个，分别是：
grant option：被授权的用户可以将这些权限赋予别的用户
max_queries_per_hour count:每个小时可以执行count次查询；
max_updates_per_hour count:每个小时可以执行count次更新；
max_connections_per_hour count:每个小时可以建立count个连接；
max_user_connections count：设置单个用户可以同时建立count个连接
9、设置全局变量：
SET GLOBAL default_password_lifetime = 180;
SET GLOBAL default_password_lifetime = 0;
可以设置密码的生命周期为6个月，6个月之后失效，如果设置为0，则一直有效。
当然，还可以在创建用户的时候就指定密码的修改周期或者禁用密码的修改周期：
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
9、有时候我们似乎已经删除了账号密码，但是却还可以通过账号密码进行访问，这个时候，需要检查一个设置，就是看看user表中是否有空记录：
select user,host from mysql.user where user='';
很有可能是你设置了user为空的记录，这样导致所有的用户都可以直接登陆。如果有，最好直接干掉它，因为它违背了安全的宗旨。