Logo
Database Firewall

Database Firewall #

SphereEx-DBPlusEngine provides a database firewall capability to intercept high-risk or inefficient SQL statements.

Overview #

In a production environment, development and operations standards are a defense line. To further prevent high-risk SQL operations such as truncate, drop, or queries without shard keys, SphereEx-DBPlusEngine can intercept them through rule configuration, reducing unnecessary resource consumption and lowering business risks.

Use Cases #

  • For Business

Prevent resource-intensive SQL, such as queries and updates without shard keys, to avoid excessive resource usage.

  • For Operations

Intercept high-risk DDL and DML operations, such as truncate, drop, and alter, to prevent accidental operations and reduce business system risks.

Database Firewall Algorithms #

SphereEx-DBPlusEngine supports multiple firewall algorithms that identify and intercept SQL through SQL matching. The following content introduces supported firewall algorithms. For specific configurable properties and descriptions, please refer to Database Firewall Algorithms.

  • Risky DDL Matching Algorithm

Intercepts truncate, drop, and alter risk statements.

  • Risky DML Matching Algorithm

Intercepts full-table update and delete statements. Full-table update and delete refer to UPDATE and DELETE statements without WHERE conditions.

  • Join Query Matching Algorithm

Intercepts statements with more than a specified number of associated tables. The association here does not differentiate JOIN types, such as LEFT JOIN, RIGHT JOIN, etc.

  • Subquery Matching Algorithm

Intercepts statements with more than a specified number of nested subqueries.

  • Shard Condition Matching Algorithm

Intercepts SELECT, UPDATE, and DELETE statements without shard keys. INSERT statements are required to have shard keys and do not need to be checked through the firewall rules.

  • HOST Address Matching Algorithm

Intercepts or allows specified addresses by setting blacklists and whitelists. At least one of the blacklists and whitelists must be defined and cannot be empty simultaneously.

  • Username Matching Algorithm

Intercepts or allows specified users by setting blacklists and whitelists. At least one of the blacklists and whitelists must be defined and cannot be empty simultaneously.

  • Role Matching Algorithm

Intercepts or allows specified roles by setting blacklists and whitelists. At least one of the blacklists and whitelists must be defined and cannot be empty simultaneously.

User Guide #

Database firewall configurations can be done through DistSQL for online configuration operations. For instructions on using DistSQL, please refer to DistSQL: Database Firewall.

When creating rules, you can create them in various combinations, such as specifying databases and tables, or they will apply to all databases and tables by default.

  • Database: CREATE DATABASE FIREWALL STRATEGY risk_dml ON sharding_db.* (……);

  • Multiple Databases: CREATE DATABASE FIREWALL STRATEGY risk_dml ON sharding_db., encrypt_db. (……);

  • Table: CREATE DATABASE FIREWALL STRATEGY risk_dml ON sharding_db.t_order (……);

  • Multiple Tables: CREATE DATABASE FIREWALL STRATEGY risk_dml ON sharding_db.t_order, sharding_db.t_order_item (……);

  • Database and Table: CREATE DATABASE FIREWALL STRATEGY risk_dml ON encrypt_db.*, sharding_db.t_order (……);

The usage reference guide is as follows:

  1. Based on system characteristics, identify the types of SQL that need to be intercepted in real-time.

    For example, in a distributed architecture, decide whether to intercept queries without shard keys. For manually maintained scenarios, decide whether to intercept delete statements without WHERE conditions.

  2. Create firewall rules in SphereEx-DBPlusEngine.

    Use DistSQL to complete the configuration of database firewall rules online.

  3. Confirm the rules are in effect.

    Trigger interception rules through programs or manually to confirm their effectiveness.

Operations Guide #

Here are three scenarios to understand the operations of SphereEx-DBPlusEngine: Intercepting High-Risk DML, Intercepting High-Risk DDL, and Intercepting Multi-Table JOIN.

Intercepting High-Risk DML #

Topology: DBPlusEngine* 1, MySQL* 1

  1. Create a logical database and complete data source registration.

    Access SphereEx-DBPlusEngine through the MySQL client and create a logical database named 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)

Register the data source by adding the MySQL database to the logical database. sqlCopy code 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 rowsaffected (0.66 sec)

  1. Create a test table and insert several test data. sqlCopy code mysql> DROP TABLE IF EXISTS t_order; Query OK, 0 rows affected (0.71 sec) mysql> CREATETABLE 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)
  2. The test environment is set up. Next, we will create interception rules and verify them using SQL.
  3. Create the RISK_DML strategy. sqlCopy code mysql> SHOW DATABASE FIREWALL STRATEGIES; Empty set (0.45 sec) mysql> CREATE DATABASE FIREWALL STRATEGY risk_dml ( MATCHING_ALGORITHMS( TYPE(NAME=“RISK_DML”) ), ACTION_ALGORITHMS( TYPE(NAME=“BLOCK”) )); Query OK, 0 rows affected (0.17 sec) mysql>SHOW DATABASE FIREWALL 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)
  4. The interception rules for risk_dml type statements have been created. They will execute the “block” action, meaning they will be intercepted.
  5. Verify the strategy.
  6. First, execute an update statement without a WHERE condition. This operation will be intercepted. sqlCopy code mysql> UPDATE t_order SET STATUS = ‘UPDATE’; ERROR 16000 (44000): Database firewall failed, error message: Not allowed by database firewall strategy risk_dml. mysql>
  7. As expected, the operation has been intercepted. Next, execute a delete statement without a WHERE condition. This operation will also be intercepted. sqlCopy code mysql> DELETE FROM t_order; ERROR 16000 (44000): Database firewall failed, error message: Not allowed by database firewall strategy risk_dml. mysql>
  8. As expected, the operation has been intercepted. The demonstration of intercepting high-risk DML is complete. Intercepting High-Risk DDL Topology: DBPlusEngine* 1, MySQL* 1
  9. Create a logical database and complete data source registration.
  10. Access SphereEx-DBPlusEngine through the MySQL client and create a logical database named testdb. sqlCopy code

mysql -uroot -p -P3307 mysql> CREATE DATABASE testdb; Query OK, 0 rows affected (0.20sec) 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; #

Intercepting Multi-Table JOIN

Topology: DBPlusEngine* 1, MySQL* 1

  1. Create a logical database and complete data source registration.

    Access SphereEx-DBPlusEngine through the MySQL client and create a logical database named 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;

    Register the data source by adding the MySQL database to the logical database.

    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)

  2. Create four test tables

    Create four tables for use in join tests.

    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)

  3. Execute a JOIN query

    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)

    Based on the above result, it can be confirmed that the join operation involving four tables can be executed.

  4. Create interception rules

    mysql> CREATE DATABASE FIREWALL 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)

    The above rule limits the maximum number of tables that can be joined to 3. If more than 3 tables are joined, the operation will be intercepted.

  5. Verify the results

    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): Database firewall failed, error message: Not allowed by database firewall strategy join_query.
mysql>

    Based on the output result, it can be confirmed that the join operation involving four tables has been intercepted, as expected. The demonstration of intercepting multi-table JOIN is complete.