Logo
事中审计

事中审计 #

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: 审计。使用参考指南如下。

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

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

  1. 在 SphereEx-DBPlusEnigine 中创建审计规则

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

  1. 确认规则生效

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

操作指南 #

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

拦截高危 DML #

拓扑结构:DBPlusEnigine* 1,MySQL* 1

  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)
  1. 创建测试表,插入若干条测试数据
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 进行验证。

  1. 创建 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 行为,即拦截。

  1. 验证策略

首先执行一条不带有 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

  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)
  1. 创建测试表,插入若干条测试数据
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 进行验证。

  1. 创建 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 行为,即拦截。

  1. 验证策略

验证 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

  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)
  1. 创建四张测试表

创建四张表,用来做 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)
  1. 执行 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 可执行。

  1. 创建拦截规则
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,操作将会被拦截。

  1. 验证结果
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 的场景演示已完成。