MySQL8.0 新特性 账户与安全
最近总结一下 MySQL8.0 的新特性，公司项目上 开始正式使用 MySQL8.0 特意花时间看了看MySQL的新特性，学习一下文档，这里进行记录一下吧。
大概 更新如下的内容吧，内容还挺多的，我可能要分几篇文章总结一下。
1 mysql 8.0用户创建和授权
mysql5.7 用户创建 和授权 可以在 一条语句中完成. 通过 grant 授权语句，可以对一个用户创建并且授权。 mysql 8.0 后 用户的创建与授权 是分开 进行的。
select user,host from mysql.user;
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
-- mysql5.7 授权并且创建用户
grant all privileges on *.* to 'frank'@'%' identified by 'frank#2022' ;
mysql8.0 创建用户 与 授权要分开 否则会报错
-- 创建一个数据库
CREATE DATABASE us_demo_dev DEFAULT CHARACTER SET utf8mb4 ;
-- 创建一个user
create user 'frank'@'%' IDENTIFIED BY '123456';
-- 授权 那么多的权限
GRANT SELECT,INSERT ,UPDATE ,DELETE ,DROP,ALTER ,INDEX ON us_demo_dev.* TO 'frank'@'%';
-- 刷新权限
flush privileges;
-- 查看权限
SHOW GRANTS FOR 'frank'@'%';
-- 回收权限
REVOKE SELECT, INSERT, UPDATE, DELETE, DROP, INDEX, ALTER ON `us_demo_dev`.* from `frank`@`%` ;
2 默认插件更新
mysql 8.0 后 默认的认证插件 发生了变化，使用了 caching_sha2_password
为啥要换呢？ caching_sha2_password vs. mysql_native_password
官方的答案是 新的认证插件 提供了 更加安全的密码加密， 而且 提供了更好的性能， 所以 MySQL8.0 默认使用 新的认证插件功能。
通过环境变量 查看
mysql> show variables like 'default_authentication%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.01 sec)
通过用户表也可以查看到
mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| root | % | 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 |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)
Mysql 8.0
- 密码插件 默认使用
caching_sha2_password原来的插件是mysql_native_password不建议使用了， 新的方式更加安全，并且效率更高，推荐使用
如何更改用户的认证方式呢？
修改 user 表 来更新 为原来的方式
alter user 'frank'@'%' identified with mysql_native_password by 'frank#2022';
对于兼容性问题以及解决方案
caching_sha2_password Compatibility Issues and Solutions
如果你的MySQL安装必须为8.0之前的客户端提供服务，并且在升级到MySQL 8.0或更高版本后遇到了兼容性问题，解决这些问题并恢复8.0之前的兼容性的最简单方法是重新配置服务器以恢复到以前的默认认证插件（mysql_native_password）。例如，在服务器选项文件中使用这些行。
[mysqld]
default_authentication_plugin=mysql_native_password
参考文档
caching_sha2_password 的链接
https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password
3 密码管理
密码过期策略
MySQL 8.0 开始允许限制重复使用以前的密码 ，以及限制多长时间，修改密码的时候 是否要提供当前的密码。
有几个参数
密码历史中 最近三个不能相同
password_history = 3
密码最近90天不能相同
password_resuse_interval = 90
修改用户密码的时候需要提供当前的用户名密码 on/off
password_require_current =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.01 sec)
修改配置文件
/etc/my.cnf# 这样可以把 配置保存到文件中 ，下次打开session 依然有效 set persist password_history = 6;上面的命令会做持久化到文件 ，
/var/lib/mysqld/mysqld-auto.cnf会写到这个配置文件中修改用户的 密码策略
alter user 'frank'@'%' password history 5; -- 查看表的字段 desc mysql.user; mysql> desc mysql.user; +--------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Host | char(255) | NO | PRI | | | | User | char(32) | NO | PRI | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_user_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | | plugin | char(64) | NO | | caching_sha2_password | | | authentication_string | text | YES | | NULL | | | password_expired | enum('N','Y') | NO | | N | | | password_last_changed | timestamp | YES | | NULL | | | password_lifetime | smallint(5) unsigned | YES | | NULL | | | account_locked | enum('N','Y') | NO | | N | | | Create_role_priv | enum('N','Y') | NO | | N | | | Drop_role_priv | enum('N','Y') | NO | | N | | | Password_reuse_history | smallint(5) unsigned | YES | | NULL | | | Password_reuse_time | smallint(5) unsigned | YES | | NULL | | | Password_require_current | enum('N','Y') | YES | | NULL | | | User_attributes | json | YES | | NULL | | +--------------------------+-----------------------------------+------+-----+-----------------------+-------+ 51 rows in set (0.00 sec) -- 此时已经改变成5啦 select user,host, Password_reuse_history from mysql.user; -- 修改用户密码 发现会报错 alter user 'frank'@'%' identified by 'frank#2022';Password_reuse_history 这个字段就是用户的 密码历史的次数，默认为0
mysql> select user,host, Password_reuse_history from mysql.user;
+------------------+-----------+------------------------+
| user | host | Password_reuse_history |
+------------------+-----------+------------------------+
| frank | % | 5 |
| root | % | 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 'frank'@'%' identified by 'frank#2022';
ERROR 3638 (HY000): Cannot use these credentials for 'frank@%' because they contradict the password history policy
mysql>
密码的修改历史 会在这张表里
mysql> select * from mysql.password_history;
+------+-------+----------------------------+-------------------------------------------+
| Host | User | Password_timestamp | Password |
+------+-------+----------------------------+-------------------------------------------+
| % | frank | 2022-08-28 13:56:56.934844 | *CD941C1D8FA9F842134A985A112DF6F18B0AD910 |
+------+-------+----------------------------+-------------------------------------------+
-- 设置是否 需要使用当前的密码，在修改密码的时候
set persist password_require_current=on;
alter user 'frank'@'%' identified by 'frank#2020';
普通用户登录的时候
使用frank 登录账户，进行修改密码的时候，就要提供原来的密码进行验证。
如果是root 用户还是可以直接修改的，不用提供当前的密码。
修改语句如下：
REPLACE 后面是当前用户的密码
alter user user() identified by 'frank#2020' REPLACE 'frank#2022';
mysql> select user();
+-----------------+
| user() |
+-----------------+
| frank@localhost |
+-----------------+
1 row in set (0.00 sec)
mysql> alter user user() identified by 'frank#2020';
ERROR 3892 (HY000): Current password needs to be specified in the REPLACE clause in order to change it.
mysql> alter user user() identified by 'frank#2020' REPLACE 'frank#2022';
Query OK, 0 rows affected (0.00 sec)
双密码支持
从MySQL 8.0.14开始，用户账户被允许拥有双密码，指定为主密码和副密码。双密码功能使其有可能在这样的情况下无缝地执行凭证变更。
一个系统有大量的MySQL服务器，可能涉及复制。
多个应用程序连接到不同的MySQL服务器。
必须对应用程序用于连接服务器的一个或多个账户进行定期的凭证变更。
考虑一下在前一种类型的情况下，当一个账户只允许有一个密码时，必须如何进行凭证变更。在这种情况下，在账户密码更改和传播到所有服务器的时间上，以及所有使用该账户的应用程序更新到使用新密码的时间上，都必须密切配合。这个过程可能涉及到服务器或应用程序不可用的停机时间。
有了双密码，就可以更容易、分阶段地进行凭证变更，而不需要密切合作，也不需要停机。
假设在之前描述的凭证变更场景中，一个名为’appuser1’@‘host1.example.com’的账户被应用程序用来连接到服务器，账户密码要从’password_a’改为’password_b’
要执行这种凭证的改变，请使用ALTER USER，如下所示。
1.在每个不是副本的服务器上，建立’password_b’作为新的appuser1的主密码，保留当前密码作为辅助密码。
ALTER USER 'appuser1'@'host1.example.com'
IDENTIFIED BY 'password_b'
RETAIN CURRENT PASSWORD;
2.等待密码修改在整个系统中复制到所有副本。
3.修改每个使用appuser1账户的应用程序，使其使用’password_b’而不是’password_a’的密码连接到服务器。
4.在这一点上，不再需要辅助密码了。在每个不是副本的服务器上，丢弃辅助密码。
ALTER USER 'appuser1'@'host1.example.com'
DISCARD OLD PASSWORD;
5.在丢弃密码的改变复制到所有副本后，凭证改变就完成了。
双密码的应用场景，主要是涉及到 复制 以及多个应用连接不同MySQL服务器等。
参考文档
https://dev.mysql.com/blog-archive/password-reuse-policy-in-mysql-8-0/
https://dev.mysql.com/doc/refman/8.0/en/password-management.html
https://dev.mysql.com/doc/refman/8.0/en/password-management.html#password-reuse-policy
4 角色管理
什么是角色呢？
MySQL8.0 提供了角色管理的新功能， 角色是一组权限的集合
创建角色
CREATE ROLE 'app_developer', 'app_read', 'app_write';
给角色授权
GRANT ALL ON app_db.* TO 'app_developer';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';
创建用户
CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass';
CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';
CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass';
CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';
角色赋予 用户
GRANT 'app_developer' TO 'dev1'@'localhost';
GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';
-- 创建测试数据库
create database testdb;
use testdb;
create table testdb.t1(id int);
--创建角色 授权
create role 'write_role';
select host,user,authentication_string from mysql.user;
-- 授权 角色
grant insert,update ,delete on testdb.* to 'write_role';
grant select on testdb.* to 'write_role';
create user 'user1' identified by 'user1#2022';
# 授权 角色权限 赋值给 用户
grant 'write_role' to 'user1';
-- 查看 权限
show grants for 'user1';
-- 查看具体的角色
show grants for 'user1' using 'write_role';
使用 user1 进行登录 测试
mysql> select * from testdb.t1;
ERROR 1142 (42000): SELECT command denied to user 'user1'@'localhost' for table 't1'
mysql> select current_role;
ERROR 1054 (42S22): Unknown column 'current_role' in 'field list'
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| NONE |
+----------------+
1 row in set (0.00 sec)
mysql> select user();
+-----------------+
| user() |
+-----------------+
| user1@localhost |
+-----------------+
1 row in set (0.00 sec)
查看失败了，需要手动设置用户角色
set role 'write_role';
mysql> select user();
+-----------------+
| user() |
+-----------------+
| user1@localhost |
+-----------------+
1 row in set (0.00 sec)
mysql> set role 'write_role';
Query OK, 0 rows affected (0.00 sec)
mysql> select current_role();
+------------------+
| current_role() |
+------------------+
| `write_role`@`%` |
+------------------+
1 row in set (0.00 sec)
这样每次登陆 要设置这个有点麻烦，我们可以给每个用户单独设置 role
切换到root 登录,给用户设置一个默认的role
mysql 的角色表信息有两张表， mysql.default_roles , mysql.role_edges
mysql> set default role 'write_role' to 'user1' ;
Query OK, 0 rows affected (0.01 sec)
mysql> set default role all to 'user1' ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.default_roles;
+------+-------+-------------------+-------------------+
| HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+------+-------+-------------------+-------------------+
| % | user1 | % | write_role |
+------+-------+-------------------+-------------------+
1 row in set (0.00 sec)
mysql> select * from mysql.role_edges;
+-----------+------------+---------+---------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+------------+---------+---------+-------------------+
| % | write_role | % | user1 | N |
+-----------+------------+---------+---------+-------------------+
1 row in set (0.00 sec)
回收角色权限
mysql> revoke insert , update ,delete on testdb.* from 'write_role';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for 'write_role';
+------------------------------------------------+
| Grants for write_role@% |
+------------------------------------------------+
| GRANT USAGE ON *.* TO `write_role`@`%` |
| GRANT SELECT ON `testdb`.* TO `write_role`@`%` |
+------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for user1 using 'write_role';
+-------------------------------------------+
| Grants for user1@% |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `user1`@`%` |
| GRANT SELECT ON `testdb`.* TO `user1`@`%` |
| GRANT `write_role`@`%` TO `user1`@`%` |
+-------------------------------------------+
3 rows in set (0.00 sec)
角色方便 我们来管理一组权限相同的用户，只需要创建一个用户， 将用户赋予这个角色即可。
参考文档
本文含有隐藏内容，请 开通VIP 后查看