事中审计 #

SphereEx-DBPlusEnigine 在审计方面既支持事中审计（拦截），同时也支持事后审计（记录）。本章节用来介绍 SphereEx-DBPlusEnigine 事中审计的能力，并指导用户正确地配置使用。

概述 #

在生产环境中，开发及运维规范是一道防线，为了进一步防范高危 SQL，如 truncate、drop 或不带分片键的查询等操作，SphereEx-DBPlusEnigine 可通过配置规则进行拦截，减少不必要的资源占用，降低业务风险。

适用场景 #

对业务

通过拦截耗资源 SQL，如不带分片键的查询及更新等，避免资源过度占用。

对运维

通过拦截高风险的 DDL、DML，如 truncate、drop 及 alter 等操作，避免误操作，降低业务系统风险。

审计算法 #

SphereEx-DBPlusEnigine 在审计方面支持多种审计算法，通过 SQL 匹配的方式来实现识别并拦截。以下内容为支持的审计算法介绍，关于具体的可配置属性及说明，请参考分片审计算法。

风险 DDL 匹配算法

拦截 truncate、drop 及 alter 风险语句。

风险 DML 匹配算法

拦截全表更新语句和全表删除语句。全表更新、全表删除指不包含 WHERE 条件的 UPDATE 和 DELETE 语句。

关联查询匹配算法

拦截超过指定关联表数量的语句。此处关联不区分 JON 类型，如 LEFT JOIN、RIGHT JOIN 等。

子查询匹配算法

拦截超过指定子查询嵌套层数的语句。

分片条件匹配算法

拦截不带有分片键的 SELECT、UPDATE 及 DELETE 语句。INSERT 语句是强制需要分片键的，无需通过审计规则检查，因此不包含在此算法内。

HOST 地址匹配算法

通过设置黑白名单，可拦截或放行指定地址。黑名单和白名单至少要定义一种，不能同时为空。

用户名匹配算法

通过设置黑白名单，可拦截或放行指定用户。黑名单和白名单至少要定义一种，不能同时为空。

角色匹配算法

通过设置黑白名单，可拦截或放行指定角色。黑名单和白名单至少要定义一种，不能同时为空。

使用指南 #

事中审计的配置可通过 DistSQL 的方式进行在线配置操作，相关 DistSQL 使用说明，请参考 DistSQL: 审计。使用参考指南如下。

结合系统特点，明确需要事中拦截的 SQL 类型

如在分布式架构中，是否需要拦截不带有分片键的查询语句。对于人工维护的场景，是否需要拦截不带有 where 条件的 detele 语句。

在 SphereEx-DBPlusEnigine 中创建审计规则

通过 DistSQL，在线完成审计规则配置。

确认规则生效

通过程序或人工方式触发拦截规则，确认规则生效。

操作指南 #

下面通过三个场景来了解下 SphereEx-DBPlusEnigine 的操作，分别是拦截高危 DML、拦截高危 DDL 和拦截多表 JOIN。

拦截高危 DML #

拓扑结构：DBPlusEnigine* 1，MySQL* 1

创建逻辑库，完成数据源注册

通过 MySQL 客户端访问 SphereEx-DBPlusEngine，创建名为 testdb 的逻辑库。

# mysql -uroot -p -P3307

mysql> CREATE DATABASE testdb;
Query OK, 0 rows affected (0.20 sec)

mysql> SHOW DATABASES;
+--------------------+
| schema_name        |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shardingsphere     |
| sys                |
| testdb             |
+--------------------+
6 rows in set (0.05 sec)

注册数据源，将 MySQL 数据库注册到逻辑库中。

mysql> USE testdb;
Database changed
mysql> REGISTER STORAGE UNIT ds_0 (
     URL="jdbc:mysql://192.168.xx.103:3306/mysql_0?serverTimezone=UTC&useSSL=false",
     USER="test",
     PASSWORD="Test@123",
     PROPERTIES("maximumPoolSize"=10)
     );
Query OK, 0 rows affected (0.66 sec)

创建测试表，插入若干条测试数据

mysql> DROP TABLE IF EXISTS t_order;
Query OK, 0 rows affected (0.71 sec)

mysql> CREATE TABLE IF NOT EXISTS `t_order` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `status` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`order_id`)
);

Query OK, 0 rows affected (2.16 sec)

mysql> INSERT INTO t_order (order_id, user_id, status) VALUES 
(1,1,'OK'),(2,2,'OK'),(3,3,'OK'),(4,4,'OK');
Query OK, 4 rows affected (0.11 sec)

mysql> SELECT * FROM t_order;
+----------+---------+--------+
| order_id | user_id | status |
+----------+---------+--------+
|        1 |       1 | OK     |
|        2 |       2 | OK     |
|        3 |       3 | OK     |
|        4 |       4 | OK     |
+----------+---------+--------+
4 rows in set (0.05 sec)

目前已构建完成测试环境，下面将创建拦截规则，并通过 SQL 进行验证。

创建 RISK_DML 策略

mysql> SHOW AUDIT STRATEGIES;
Empty set (0.45 sec)

mysql> CREATE AUDIT STRATEGY risk_dml (
MATCHING_ALGORITHMS(
    TYPE(NAME="RISK_DML")
),
ACTION_ALGORITHMS(
    TYPE(NAME="BLOCK")
));
Query OK, 0 rows affected (0.17 sec)

mysql> SHOW AUDIT STRATEGIES;
+---------------+----------------+---------------------+-----------------+
| strategy_name | algorithm_type | algorithm_type_name | algorithm_props |
+---------------+----------------+---------------------+-----------------+
| risk_dml      | matching       | risk_dml            |                 |
| risk_dml      | action         | block               |                 |
+---------------+----------------+---------------------+-----------------+
2 rows in set (0.05 sec)

已完成拦截规则的创建，对于 risk_dml 类型的语句，将会执行 block 行为，即拦截。

验证策略

首先执行一条不带有 where 条件的 update 语句，该操作将会被拦截。

mysql> UPDATE t_order SET STATUS = 'UPDATE';
ERROR 16000 (44000): SQL audit failed, error message: Not allowed by audit strategy `risk_dml`.
mysql>

同预期一致，操作已被拦截。下面执行一条不带有 where 条件的 delete 语句，该操作仍然将会被拦截。

mysql> DELETE FROM t_order;
ERROR 16000 (44000): SQL audit failed, error message: Not allowed by audit strategy `risk_dml`.
mysql>

同预期一致，操作已被拦截。高危 DML 拦截场景演示完毕。

拦截高危 DDL #

拓扑结构：DBPlusEnigine* 1，MySQL* 1

创建逻辑库，完成数据源注册

通过 MySQL 客户端访问 SphereEx-DBPlusEngine，创建名为 testdb 的逻辑库。

# mysql -uroot -p -P3307

mysql> CREATE DATABASE testdb;
Query OK, 0 rows affected (0.20 sec)

mysql> SHOW DATABASES;
+--------------------+
| schema_name        |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shardingsphere     |
| sys                |
| testdb             |
+--------------------+
6 rows in set (0.05 sec)

DROP TABLE IF EXISTS t_order;

注册数据源，将 MySQL 数据库注册到逻辑库中。

mysql> USE testdb;
Database changed
mysql> REGISTER STORAGE UNIT ds_0 (
     URL="jdbc:mysql://192.168.xx.103:3306/mysql_0?serverTimezone=UTC&useSSL=false",
     USER="test",
     PASSWORD="Test@123",
     PROPERTIES("maximumPoolSize"=10)
     );
Query OK, 0 rows affected (0.66 sec)

创建测试表，插入若干条测试数据

mysql> DROP TABLE IF EXISTS t_order;
Query OK, 0 rows affected (0.71 sec)

mysql> CREATE TABLE IF NOT EXISTS `t_order` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `status` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`order_id`)
);

Query OK, 0 rows affected (2.16 sec)

mysql> INSERT INTO t_order (order_id, user_id, status) VALUES 
(1,1,'OK'),(2,2,'OK'),(3,3,'OK'),(4,4,'OK');
Query OK, 4 rows affected (0.11 sec)

mysql> SELECT * FROM t_order;
+----------+---------+--------+
| order_id | user_id | status |
+----------+---------+--------+
|        1 |       1 | OK     |
|        2 |       2 | OK     |
|        3 |       3 | OK     |
|        4 |       4 | OK     |
+----------+---------+--------+
4 rows in set (0.05 sec)

目前已构建完成测试环境，下面将创建拦截规则，并通过 SQL 进行验证。

创建 RISK_DDL 策略

mysql> CREATE AUDIT STRATEGY risk_ddl (
MATCHING_ALGORITHMS(
    TYPE(NAME="RISK_DDL")
),
ACTION_ALGORITHMS(
    TYPE(NAME="BLOCK")
));
Query OK, 0 rows affected (0.16 sec)

mysql> SHOW AUDIT STRATEGIES;
+---------------+----------------+---------------------+-----------------+
| strategy_name | algorithm_type | algorithm_type_name | algorithm_props |
+---------------+----------------+---------------------+-----------------+
| risk_ddl      | matching       | risk_ddl            |                 |
| risk_ddl      | action         | block               |                 |
+---------------+----------------+---------------------+-----------------+

2 rows in set (0.05 sec)

已完拦截规则的创建，对于 risk_ddl 类型的语句，将会执行 block 行为，即拦截。

验证策略

验证 alter table 的操作，该操作将会被拦截。

mysql> ALTER TABLE t_order
    -> ADD COLUMN create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;
ERROR 16000 (44000): SQL audit failed, error message: Not allowed by audit strategy `risk_ddl`.
mysql>

验证 truncate table 的操作，该操作将会被拦截。

mysql> TRUNCATE TABLE t_order;
ERROR 16000 (44000): SQL audit failed, error message: Not allowed by audit strategy `risk_ddl`.
mysql>

验证 drop table 的操作，该操作将会被拦截。

mysql> DROP TABLE t_order;
ERROR 16000 (44000): SQL audit failed, error message: Not allowed by audit strategy `risk_ddl`.
mysql>

通过输出结果确认，alter、truncate 及 drop 操作均被拦截，与预期一致。高危 DDL 拦截场景演示完毕。

拦截多表 JOIN #

拓扑结构：DBPlusEnigine* 1，MySQL* 1

创建逻辑库，完成数据源注册

通过 MySQL 客户端访问 SphereEx-DBPlusEngine，创建名为 testdb 的逻辑库。

# mysql -uroot -p -P3307

mysql> CREATE DATABASE testdb;
Query OK, 0 rows affected (0.20 sec)

mysql> SHOW DATABASES;
+--------------------+
| schema_name        |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shardingsphere     |
| sys                |
| testdb             |
+--------------------+
6 rows in set (0.05 sec)

DROP TABLE IF EXISTS t_order;

注册数据源，将 MySQL 数据库注册到逻辑库中。

mysql> USE testdb;
Database changed
mysql> REGISTER STORAGE UNIT ds_0 (
     URL="jdbc:mysql://192.168.xx.103:3306/mysql_0?serverTimezone=UTC&useSSL=false",
     USER="test",
     PASSWORD="Test@123",
     PROPERTIES("maximumPoolSize"=10)
     );
Query OK, 0 rows affected (0.66 sec)

创建四张测试表

创建四张表，用来做 join 测试使用。

mysql> CREATE TABLE IF NOT EXISTS t_order_a (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `status` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`order_id`)
);
Query OK, 0 rows affected (0.65 sec)

mysql> CREATE TABLE IF NOT EXISTS t_order_b (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `status` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`order_id`)
);
Query OK, 0 rows affected (0.50 sec)

mysql> CREATE TABLE IF NOT EXISTS t_order_c (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `status` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`order_id`)
);
Query OK, 0 rows affected (0.30 sec)

mysql> CREATE TABLE IF NOT EXISTS t_order_d (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `status` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`order_id`)
);
Query OK, 0 rows affected (0.35 sec)

mysql> SHOW TABLES;
+------------------+------------+
| Tables_in_testdb | Table_type |
+------------------+------------+
| t_order_b        | BASE TABLE |
| t_order_c        | BASE TABLE |
| t_order          | BASE TABLE |
| t_order_a        | BASE TABLE |
| t_order_d        | BASE TABLE |
+------------------+------------+
5 rows in set (0.00 sec)

执行 Join 查询

mysql> select * from t_order_a a 
join t_order_a b on (b.order_id = a.order_id)
join t_order_b c on (c.order_id = a.order_id)
join t_order_c d on (d.order_id = a.order_id);
Empty set (0.11 sec)

通过以上执行结果，可确认四张表 join 可执行。

创建拦截规则

mysql> CREATE AUDIT STRATEGY join_query (
MATCHING_ALGORITHMS(
    TYPE(NAME="JOIN_QUERY",PROPERTIES("max-allowed-join-table-count"=3))
),
ACTION_ALGORITHMS(
    TYPE(NAME="BLOCK")
));
Query OK, 0 rows affected (0.44 sec)

以上规则限制了 join 的数量最多只能关联 3 张表，如果大于 3 张表做 join，操作将会被拦截。

验证结果

mysql> select * from t_order_a a
    -> join t_order_a b on (b.order_id = a.order_id)
    -> join t_order_b c on (c.order_id = a.order_id)
    -> join t_order_c d on (d.order_id = a.order_id);
ERROR 16000 (44000): SQL audit failed, error message: Not allowed by audit strategy `join_query`.
mysql>

通过输出结果可确认，四张表 jion 的操作已经被拦截，与预期结果一致，拦截多表 JOIN 的场景演示已完成。