mysql8.0出现了哪些关于用户管理的新特性？-优快云博客

本文链接：https://blog.youkuaiyun.com/wdwangye/article/details/107452739

整理几个mysql8.0关于用户权限的新特性。

一. 创建用户和用户授权的命令分开执行

mysql5.7上创建用户，创建和授权可以一步进行

grant all privileges on *.* to "test"@"%" identified by "WWW13@me";
创建一个用户test，并授予权限。

mysql8.0开始创建密码与授予权限分开操作。

mysql> grant all privileges on *.* to "test"@"%" identified by "WWW13@me";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by "WWW13@me"' at line 1                  #不可以这样执行，会报语法错误
mysql> create user "test"@"%" identified by "WWW13@me";           #创建用户
Query OK, 0 rows affected (0.24 sec)

mysql> grant all privileges on *.* to "test"@"%";                #给予权限
Query OK, 0 rows affected (0.10 sec)

mysql> select host,user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | copy             |
| %         | test             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
6 rows in set (0.00 sec)

二.身份认证插件做出了更新

mysql8.0中默认的身份认证插件是caching_sha2_password,替代了之前的mysql_navtive_password,
这种新的认证会更加安全。

mysql8上

mysql> select User,Host,Plugin from mysql.user;
+------------------+-----------+-----------------------+
| User             | Host      | Plugin                |
+------------------+-----------+-----------------------+
| copy             | %         | caching_sha2_password |
| test             | %         | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
6 rows in set (0.00 sec)

更新后带来的问题：

如果连接的客户端比较老旧没有升级，在连接mysql8.0时候可能会认证错误。

可以修改认证方式

针对某一个用户修改

mysql> alter user "test"@"%" identified with mysql_native_password by "WWW13@me";
Query OK, 0 rows affected (0.35 sec)

mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| copy             | %         | caching_sha2_password |
| test             | %         | mysql_native_password |    #修改成功
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
6 rows in set (0.00 sec)

针对全局修改

配置文件里将default-authentication-plugin这段开启,然后重启数据库

三.密码管理策略增强

修改密码时，限制使用之前使用过的策略。

变量	解释
password_history	该参数数值用于设置历史密码可以再次使用之前需要进行密码修改的次数。比如password_history = 3 就是代表新密码不能和最近使用过3次的密码相同。设置为0则不会对历史密码是否可以重用进行限制
password_reuser_interval	按照日期指定来限制，password_reuse_interval = 60 表示新密码不能和60天之内的密码相同，默认值为0，设置为0则不会对历史密码重用进行时间间隔设置。
password_require_current	默认值是OFF，当值为ON时候用户修改密码时候是否需要提供当前密码.

查看当前变量

mysql> show variables like "password%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| password_history         | 0     |
| password_require_current | OFF   |
| password_reuse_interval  | 0     |
+--------------------------+-------+
3 rows in set (0.58 sec)

全局修改变量password_history

mysql> set persist password_history=6
    -> ;
Query OK, 0 rows affected (0.10 sec)

mysql> show variables like "password%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| password_history         | 6     |
| password_require_current | OFF   |
| password_reuse_interval  | 0     |
+--------------------------+-------+
3 rows in set (0.00 sec)

修改完之后可以在/usr/local/mysql/data/mysqld-auto.cnf 下查看。

由于将修改的内容写入磁盘，所以重启后依然有效。

[root@fei-7 ~]# cd /usr/local/mysql/data/
[root@fei-7 data]# ls
auto.cnf       ca.pem           ibdata1      #innodb_temp      mysqld-auto.cnf     private_key.pem  sys
binlog.000001  client-cert.pem  ib_logfile0  master-bin.index  mysqld.pid          public_key.pem   test01
binlog.index   client-key.pem   ib_logfile1  mysql             mysql.ibd           server-cert.pem  undo_001
ca-key.pem     ib_buffer_pool   ibtmp1       mysql-bin.000001  performance_schema  server-key.pem   undo_002
  
[root@fei-7 data]# cat mysqld-auto.cnf 
{ "Version" : 1 , "mysql_server" : { "password_history" : { "Value" : "6" , "Metadata" : { "Timestamp" : 1595153563573906 , "User" : "root" , "Host" : "localhost" } } } }

针对单个用户修改变量password_reuse_history

#查看当前密码设置状态，拒绝历史密码均未设置。
mysql> select user,host,password_reuse_history from mysql.user;
+------------------+-----------+------------------------+
| user             | host      | password_reuse_history |
+------------------+-----------+------------------------+
| copy             | %         |                   NULL |
| test             | %         |                   NULL |
| mysql.infoschema | localhost |                   NULL |
| mysql.session    | localhost |                   NULL |
| mysql.sys        | localhost |                   NULL |
| root             | localhost |                   NULL |
+------------------+-----------+------------------------+
6 rows in set (0.00 sec)

mysql> alter user "test"@"%" password history 10;   #给test设置为10天,修改的新密码不能和十天内的重复。
Query OK, 0 rows affected (0.09 sec)

mysql> select user,host,password_reuse_history from mysql.user;
+------------------+-----------+------------------------+
| user             | host      | password_reuse_history |
+------------------+-----------+------------------------+
| copy             | %         |                   NULL |
| test             | %         |                     10 |
| mysql.infoschema | localhost |                   NULL |
| mysql.session    | localhost |                   NULL |
| mysql.sys        | localhost |                   NULL |
| root             | localhost |                   NULL |
+------------------+-----------+------------------------+
6 rows in set (0.00 sec)

四.Role规则

可以认为是一个权限的集合，这个集合有一个统一的名字，就是Role名，你可以为多个账户赋予统一的
某个Role的权限，而权限的修改可以直接通过修改Role来实现，而无需每个账户逐一GRANT权限，大
大方便了运维和管理。
Role可以被创建，修改和删除，并作用到其所属于的账户上。

Role的本质实际上是一个普通用户。

步骤	解释
create database roleDB ;	创建一个库
create table roleDB.table_auth(id int);	创建一个表
create role ‘write_role’;	创建一个角色

创建

mysql> create database roleDB;
Query OK, 1 row affected (0.15 sec)

mysql> create table roleDB.table_auth(id int);
Query OK, 0 rows affected (1.18 sec)

mysql> create role "write_role";
Query OK, 0 rows affected (0.14 sec)

#查看用户名和密码，发现 write_role没有密码
#可以看出mysql里的角色其实是一个用户。是用用户来模拟角色的效果
mysql> select host,user,authentication_string from mysql.user; 
+-----------+------------------+------------------------------------------------------------------------+
| host      | user             | authentication_string                                                  |
+-----------+------------------+------------------------------------------------------------------------+
E4aj9xMqvw/zIU27YIxa5XF3K7Q5H60wGVDhpbwecXaO0h5MHb52 |
| %         | test             | *1B1581E405E8A5F1A98CEC7F9C49B17809781250                              |
| %         | write_role       |                                                                        |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             | $A$005$K}[X
                                            #omWr
                                                 M4q0sP6DZM.FWfnBAqyTu/EmUL3yBIV5JJ9nYQIOiS.PN75H7 |
+-----------+------------------+------------------------------------------------------------------------+
7 rows in set (0.00 sec)

向roleDB库授权

mysql> grant select,insert,update,delete on roleDB.* to "write_role";
Query OK, 0 rows affected (0.14 sec)

mysql> show grants for "write_role"  #查看write_role的权限
    -> ;
+------------------------------------------------------------------------+
| Grants for write_role@%                                                |
+------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `write_role`@`%`                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `roleDB`.* TO `write_role`@`%` |
+------------------------------------------------------------------------+
2 rows in set (0.00 sec)

创建一个新用户，将roleDB授权给该用户。

mysql> create user "role1" identified by "WWW13@me";
Query OK, 0 rows affected (0.09 sec)

mysql> grant "write_role" to "role1";
Query OK, 0 rows affected (0.10 sec)

mysql> show grants for "role1";                  #查看用户权限
+---------------------------------------+
| Grants for role1@%                    |
+---------------------------------------+
| GRANT USAGE ON *.* TO `role1`@`%`     |
| GRANT `write_role`@`%` TO `role1`@`%` |       #拥有`write_role`@`%`的所有权限
+---------------------------------------+
2 rows in set (0.07 sec)

用新用户登录，查询table_auth表，验证是否有查询权限

[root@fei-7 ~]# mysql -urole1 -pWWW13@me;

mysql> select * from roleDB.table_auth;   #没有权限，为什么呢。因为此功能不仅需要root设定，还需要用户自身激活。
ERROR 1142 (42000): SELECT command denied to user 'role1'@'localhost' for table 'table_auth'

#查看当前角色状态，确认没有激活。
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.39 sec)

#自己激活write_role
mysql> set role "write_role";
Query OK, 0 rows affected (0.10 sec)

#此时有了查询权限
mysql> select * from roleDB.table_auth;
Empty set (0.14 sec)

补充：如何撤销role规则中的权限。

#移除write_role的insert,update,delete权限。
mysql> revoke insert,update,delete on roleDB.* from "write_role";
Query OK, 0 rows affected (3.73 sec)

mysql> show grants for "write_role";
+------------------------------------------------+
| Grants for write_role@%                        |
+------------------------------------------------+
| GRANT USAGE ON *.* TO `write_role`@`%`         |
| GRANT SELECT ON `roleDB`.* TO `write_role`@`%` |         #查看移除成功。
+------------------------------------------------+
2 rows in set (0.00 sec)