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表中只有以下权限，也就是关于表的权限：

阅读剩余部分