MySQL安全之权限管理

权限表的存取：

对于身份的认证，MySQL是通过IP地址和用户名联合进行确认的，例如MySQL安装后默认创建的用户root@localhost表示用户root只能从本地（localhost）进行连接才可以通过认证，在其他任何主机进行连接都将被拒绝，也就是说同一个用户，来自不同的IP地址，MySQL将其视为不同的用户。

MySQL的权限表在数据库启动的时候就载入了内存，当用户通过身份认证后，就在内存中进行相应权限的存取。在权限存取的两个过程中，系统会用到 “mysql” 数据库中的user、host、db这3个最重要的权限表，而host表在MySQL:5.6.7之后就删除了。

mysql库user表中的列：

user表中的列主要分为4个部分：用户列、权限列、安全列、资源控制列。用的比较多的是用户列和权限列，权限列又分为普通权限和管理权限。普通权限主要用于数据库的操作，比如 select_priv、create_priv等；管理权限主要用来对数据库进行管理的操作，比如 process_priv、super_priv等。

当用户进行连接时，权限表的存取过程有以下两个阶段：

1. 先从user表中的host、user和password这3个字段中判断连接的IP、用户名和密码是否存在于表中，如果存在，则通过身份验证，否则拒绝连接。
2. 通过身份验证，按照以下权限表的顺序得到数据库权限：user -->db -->tables_priv -->columns_priv。

说明：MySQL:8.0以后user表中去掉了password，取而代之的是authentication_string，修改密码的方式：

ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘密码’；

下面举个例子解释一下权限表存取过程的第二个阶段：

（1）创建用户dongsq@localhost，并赋予所有数据库上的所有表的select权限。

说明：MySQL:8.0之后不支持 授权的时候就进行用户创建，所以创建 之后才能授权;

（2）再看db表：

可以发现，user表中的select_priv列是 "Y" ,而db表中却没有，也就是说，对所有数据库都具有相同权限的用户记录并不需要记入db表，而仅仅需要将user表中的select_priv改为"Y"即可。换句话说，user表中的每个权限都代表了对所有数据库都有的权限。

（3）创建 数据库dongsq，将dongsq@localhost上的权限改为只针对dongsq 数据库上所有表的select权限。

这时候发现user表中的select_priv变为了"N",而db表中增加了db为dongsq的一条记录。也就是说：当只授予部分数据库某些权限时，user表中的相应权限列保持"N"，而将具体的数据库权限写入了db表，table和column的权限机制和db相似。

账号管理：

创建账号

上面提到过：MySQL:8.0之后不支持 授权的时候就进行用户创建，所以创建 之后才能授权;

示例一：现在给dongsq@localhost增加grant权限：

mysql> grant all privileges on *.* to dongsq@localhost with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> select * from user where user = 'dongsq' and host='localhost' \G;
*************************** 1. row ***************************
                    Host: localhost
                    User: dongsq
             Select_priv: Y
             Insert_priv: Y
             Update_priv: Y
             Delete_priv: Y
             Create_priv: Y
               Drop_priv: Y
             Reload_priv: Y
           Shutdown_priv: Y
            Process_priv: Y
               File_priv: Y
              Grant_priv: Y

示例二：创建新用户zhangsan，可以从任何IP进行连接，权限为对dongsq 数据库里所有表进行SELECT、UPDATE、INSERT和DELETE操作，初始化密码为 ‘123456’

本例中的IP限制为所有IP都可以连接，因此设置为%

说明：Host值可以时主机名或IP号，或"localhost"指出本地主机，可以在Host列值使用通配符字符"%"和"_"。"%"匹配任何主机名，空Host值等价于"%"，他们的含义与LIKE操作符的模式匹配操作相同。

说明：

如果权限表中host既有%又有localhost，而此时，连接从主机localhost过来，显然user表中有两条记录都符合匹配条件，那系统会选择哪一个呢？如果有多个匹配，按照下述原则来解决：

1、服务器在启动时读入user表后进行排序，它首先以最具体的Host值排序，主机名和IP号时最具体的；

2、然后当用户试图连接时，以排序的顺序浏览条目；

3、服务器使用与客户端和用户名匹配的第一行；

很显然，当user表出现localhost和%时，从主机localhost过来，匹配的host为"localhost"对应的权限。

示例三：查看账号权限

示例四：更改账号权限

权限的变更有两种方法：使用GRANT(新增)和REVOKE(回收)语句，或者变更权限表。

第二种方法直接对user、db、tables_priv和columns_priv中的权限列进行更新即可，介绍一下第一种方法中revoke

示例三中zhangsan账号有增删改查的四个权限，现在回收删除权限：

示例五：删除账号

账号资源限制：

账号资源限制这类选项的作用是限制每个账号实际具有的资源限制，这里的"资源"主要包括：

1、单个账号每小时执行的查询次数

2、单个账号每小时执行的更新次数

3、单个账号每小时连接服务器的次数

4、单个账号并发连接服务器的次数

在实际应用中可能会由于程序bug或者系统遭到攻击，使得应用短时间内发生了大量的点击，对数据库造成了严重的并发访问，为防止这种问题，我们可以通过对连接账号进行资源限制的方式解决，比如按照日常访问量加上一定冗余设置每小时查询1w次，那么1小时内超过1w次查询数据库就会给出资源不足的提示，而不会再分配资源进行实际查询。

设置资源限制的语法为：

Alter ...with option

option的选项可以是：

MAX_QUERIES_PER_HOUR count: 每小时最大查询次数

MAX_UPDATES_PER_HOUR count: 每小时最大更新次数

MAX_COUNNECTIONS_PER_HOUR count:每小时最大连接数

MAX_USER_CONNECTIONS count: 最大用户连接数

其中，MAX_CONNECTIOINS_PER_HOUR count和MAX_USER_CONNECTIONS count的区别在于前者是每小时累计的最大连接数，而后者是瞬间的并发连接数。系统还有一个全局参数MAX_USER_CONNECTIONS，它和用户MAX_USER_CONECTIONS count区别在于如果后者是0，则此用户的实际值应该是全局参数值，否则就按用户MAX_USER_CONNECTIONS count的值来设置。

下面举例说明资源限制的使用方法。

创建用户lisi，要求具有dongsq库上的select权限，并且每小时查询次数小于5：

用lisi登录：

可以发现登录后执行到第3个查询的时候提示已经查过最大查询的资源限制，为什么设置了5应该是执行到第6个查询的时候查询出错才对，为什么第三个查询就出错了呢，其实是MySQL里面很多非"select"语句都会归类到"查询"，比如"show"、"desc"语句等，还有一些隐式的查询也包含在内，

印证一下：

mysql操作日志分为文件记录日志和数据库记录日志两种方式，开启日志记录

SET GLOBAL general_log=ON;

默认是FILE的方式，执行命令，修改成TABLE方式：

可以看到用户lisi对于dongsq库的查询有6条日志，而正好是执行到第三次select count(*) from t1 的查询的时候报错的，因为登录后show databases后面有三条隐式的查询。

** 附：**

mysql数据库中3个权限表部分权限定义
表名userdbhost(5.6.7后去掉)用户列hosthosthostuserdbdbpassword(8.0后去掉)user权限列select_privselect_privselect_privinsert_privinsert_privinsert_privupdate_privupdate_privupdate_privdelete_privdelete_privdelete_privindex_privindex_privindex_privalter_privalter_privalter_privcreate_privcreate_privcreate_privdrop_privdrop_privdrop_privgrant_privgrant_privgrant_privcreate_view_privcreate_view_privcreate_view_privshow_view_privshow_view_privshow_view_privcreate_routine_privcreate_routine_privalter_routine_privalter_routine_privreferences_privreferences_privreferences_privreload_privshutdown_priv安全列ssl_typessl_sipherx509_issuerx509_subject资源控制列max_questionsmax_updatesmax_connectionsmax_user_connections