sql_firewall_config|V4.3.3|OceanBase Database Proxy |docs|OceanBase Documentations

sql_firewall_config specifies the blocking rules of the firewall.

Note

This parameter is introduced in OceanBase Database Proxy (ODP) V4.3.2.

Attribute	Description
Parameter type	String
Default value	Empty string
Value range	A JSON string that conforms to specific rules. For more information about rules, see the Rules section of this topic.
Effective upon ODP restart	No

Rules

A JSON string can contain the following fields.

Field	Description
limitName	The name of the blocking rule to apply, which is a string. Names of blocking rules can be duplicate. The only impact is the display of information when throttling is triggered.
qps	The maximum queries per second (QPS) allowed by the rule for SQL statements, which is an integer string. A QPS equal to or less than 0 specifies to block SQL statements.
rule	A JSON object of attributes. Different attributes indicate different trigger conditions. Supported attributes are `sqlType`, `keyWords`, `tableName`, and `scene`. If multiple trigger conditions are specified, the blocking rule is triggered only when all trigger conditions are met.
rule.sqlType	An array of SQL types. If this field is left empty, all SQL statements are blocked. If an SQL statement matches a specified SQL type, the blocking rule is triggered. Valid values are `"ALL"` (specifies to block all statements), `"SELECT"`, `"UPDATE"`, `"INSERT"`, `"REPLACE"`, `"DELETE"`, and `"MERGE"`.
rule.keyWords	The value is a string and can be a regular expression. If an SQL statement matches the expression rule, the blocking rule is triggered.
rule.tableName	The value is a string and can be a regular expression. If the table name in an SQL statement matches the specified value, the blocking rule is triggered. If this field is not specified, the blocking rule is triggered regardless of the table name in the statement.
rule.scene	The value is a string. Valid values: `"Nowhere"`: The SQL statement does not contain a WHERE condition. `"Uselike"`: The SQL statement contains a LIKE condition.
status	The value is a string. Valid values: `"OBSERVER"`: The blocking rule is being observed. An SQL statement that triggers the rule will be logged in the `obproxy_limit.log` file but will not be blocked. `"RUNNING"`: The blocking rule is in effect. An SQL statement that triggers the rule will be logged in the `obproxy_limit.log` file and blocked. `"SHUTDOWN"`: The blocking rule does not take effect.
username	The target users of the blocking rule, which is a string array. If this field is not specified or does not exist, the target users are all users. If this field is specified, the target users are matched by username.
inUse	Specifies whether to enable the blocking rule. The value is of the Boolean type. The default value is `true`. If the value is `false`, the blocking rule is disabled.

Here are some configuration examples:

Block SELECT, INSERT, and DELETE statements, with the blocking rule disabled.

{"limiters":
    [
        {"limitName":"limit_sql_types_1","qps":"0","rule":{"sqlType":["SELECT", "INSERT", "DELETE"]}, "status":"RUNNING","inUse":false}
    ]
}

Block SELECT, UPDATE, and DELETE statements without the where keyword, with the blocking rule enabled.

{"limiters":
    [
        {"limitName":"limit_no_where_1","qps":"0","rule":{"scene":"Nowhere", "sqlType":["SELECT", "UPDATE", "DELETE"]}, "status":"RUNNING", "inUse":true}
    ]
}

Monitor SELECT, UPDATE, and DELETE statements with the like keyword, with the blocking rule enabled. Such SQL statements are recorded in the obproxy_limit.log file and not actually blocked.

{"limiters":
    [
        {"limitName":"limit_use_like_1","qps":"0","rule":{"scene":"Uselike", "sqlType":["SELECT", "UPDATE", "DELETE"]}, "status":"OBSERVER", "inUse":true}
    ]
}

Block SQL statements with the ORDER BY or GROUP BY clause that are initiated by user1 or user2, with the blocking rule enabled.

{"limiters":
    [
        {"limitName":"limit_key_word_no_order_by_or_group_by","qps":"0","rule":{"keyWords":"ORDER\s+BY|GROUP\s+BY"}, "status":"RUNNING", "username":["user1","user2"],"inUse":true},
        {"limitName":"limit_use_like_1","qps":"0","rule":{"scene":"Nowhere", "sqlType":["SELECT", "UPDATE"]}, "status":"OBSERVER", "inUse":true},
        {"limitName":"limit_no_where_1","qps":"0","rule":{"scene":"Nowhere", "sqlType":["UPDATE", "DELETE"]}, "status":"RUNNING", "inUse":true}
    ]
}

Examples

Update firewall settings

Here is an example of updating tenant-level firewall settings. For information about how to update multi-level parameters, see the Modify parameters section in the View and modify parameters topic.

replace into proxy_config(cluster_name, tenant_name, name, value, config_level) values ('test_cluster', 'test_tenant', 'sql_firewall_config', '{"limiters":
    [
        {"limitName":"limit_sql_types_1","qps":"0","rule":{"sqlType":["SELECT", "INSERT", "DELETE"]}, "status":"RUNNING","inUse":false}
    ]
}', 'LEVEL_TENANT');

Query firewall settings

Here is an example of querying VIP-level firewall settings. For information about how to query multi-level parameters, see the View parameters section in the View and modify parameters topic.
```
select * from proxy_config where vid = 0 and vip = '10.10.10.1' and vport = 2 and name = 'sql_firewall_config';
```

Delete firewall settings

Delete VIP-level firewall settings

delete from proxy_config where vid = 0 and vip = '10.10.10.1' and vport = 2 and name = 'sql_firewall_config';

Delete global firewall settings

replace into proxy_config(name, value, config_level) values ('sql_firewall_config', '', 'LEVEL_GLOBAL');