数据库的安全机制

1. mysql8的密码恢复

mysql8.0以上后，忘记密码后的修改密码方式略微有点变动
（1）停止服务

[root@mysql01 ~]# systemctl stop mysqld

（2）修改配置文件

[root@mysql01 ~]# vim /etc/my.cnf
skip-grant-tables   # 插入此项

（3）重新启动服务

[root@mysql01 ~]# systemctl start mysqld

（4）登入系统（此时免密登录）

[root@mysql01 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

（5）修改密码（注，此处改变）

mysql> alter user 'root'@'localhost' identified by 'ABC123.com';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

此处报错。应该这样操作
1） 先置空密码

mysql> update mysql.user set authentication_string='' where user='root';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

2）. 修改配置文件，注掉刚才插入的项

[root@mysql01 ~]# vim /etc/my.cnf
#skip-grant-tables

3）. 重启服务

[root@mysql01 ~]# systemctl restart mysqld

4）. 登录，密码为空，

[root@mysql01 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

5）. 修改密码

mysql> alter user root@'localhost' identified by 'ABC123.com';
Query OK, 0 rows affected (0.00 sec)

6）. 刷新权限

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

7） 验证

[root@mysql01 ~]# mysql -uroot -pABC123.com
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

2.mysql8.0新特性

2.1 Role规则

权限的集合，可以为多个账户赋予某个role的权限，而权限的修改可以直接修改role来实现。
role可以被创建，修改和删除，并作用到起所属的账户上
示例

# 创建库
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
# 创建表
mysql> create table test2.test2(id int);
Query OK, 0 rows affected (0.16 sec)
# 创建角色
mysql> create role 'test3';
Query OK, 0 rows affected (0.11 sec)
# 查看，此时发现test3是一个没有密码的用户，用来模拟角色的效果
mysql>  select user,host,authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| test1            | %         | $A$005$%U/G0)"
                                               u@}=vC :mAqLWo/JnqYDbwUZarVawSOrgy9IJxLt9Ike9OZ2lGo5 |
McArA\ZLK p/n2b^S5bP13KRS2Oqunf9643lnOgwHU48BM17S6EqXT2g//g/ |
| test3            | %         |                                                                        |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
^;Gw}z2X,Q30on4gdLCZmzvcmqpHB5S0UEVBDuB0j/qH8me.rT1sYNB |
+------------------+-----------+------------------------------------------------------------------------+
7 rows in set (0.00 sec)

# 授权（和普通授权一样）
mysql> grant select,insert,update,delete on test2.* to 'test3';
Query OK, 0 rows affected (0.00 sec)
# 创建用户并授予密码
mysql> create user 'test.1' identified by 'ABC123.com';
Query OK, 0 rows affected (0.01 sec)
# 将角色授予用户
mysql> grant  'test.1' to 'test3';
Query OK, 0 rows affected (0.00 sec)
# 查看新用户的权限
mysql> show grants for 'test.1';
+------------------------------------+
| Grants for test.1@%                |
+------------------------------------+
| GRANT USAGE ON *.* TO `test.1`@`%` |
| GRANT `test3`@`%` TO `test.1`@`%`  |
+------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for 'test.1' using 'test3';
+-------------------------------------------------------------------+
| Grants for test.1@%                                               |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test.1