Maxscale db 防火墙

数据库防火墙过滤器用于阻止与一组规则匹配的查询。与传统的基于GRANT的特权系统相比，它可以用于防止有害的查询到达后端数据库实例，或者限制对数据库的访问。目前，该过滤器不支持多语句。

https://mariadb.com/kb/en/mariadb-enterprise/5954/

先看个配置样例

[DatabaseFirewall]type=filtermodule=dbfwfilterrules=/home/user/rules.txt[Firewalled Routing Service]type=servicerouter=readconnrouterservers=server1user=myuserpasswd=mypasswdfilters=DatabaseFirewall

Rule 语法

rule NAME deny { wildcard | columns VALUE... | regex REGEX | limit_queries COUNT TIMEPERIOD HOLDOFF | no_where_clause} [at_times VALUE...] [on_queries[select|update|insert|delete|grant|revoke|drop|create|alter|use|load]]

COUNT：查询次数限定

TIMEPERIOD ：多长时间内：秒

HOLDOFF：冻结阻塞多久：秒

强制参数：

The database firewall filter's rules expect a single mandatory parameter for a rule. You can define multiple rules to cover situations where you would like to apply multiple mandatory rules to a query.

wildcard

This rule blocks all queries that use the wildcard character *.

columns

This rule expects a list of values after the columns keyword. These values are interpreted as column names and if a query targets any of these, it is blocked.

regex

This rule blocks all queries matching a regex enclosed in single or double quotes. The regex string expects a PCRE2 syntax regular expression. For more information about the PCRE2 syntax, read the PCRE2 documentation.

limit_queries

The limit_queries rule expects three parameters. The first parameter is the number of allowed queries during the time period. The second is the time period in seconds and the third is the amount of time for which the rule is considered active and blocking.

no_where_clause

This rule inspects the query and blocks it if it has no WHERE clause. For example, this would disallow a DELETE FROM ... query without a WHERE clause. This does not prevent wrongful usage of the WHERE clause e.g. DELETE FROM ... WHERE 1=1.

可选参数

Each mandatory rule accepts one or more optional parameters. These are to be defined after the mandatory part of the rule.

at_times

This rule expects a list of time ranges that define the times when the rule in question is active. The time formats are expected to be ISO-8601 compliant and to be separated by a single dash (the - character). For example, to define the active period of a rule to be 5pm to 7pm, you would include at times 17:00:00-19:00:00 in the rule definition. The rule uses local time to check if the rule is active and has a precision of one second.

on_queries

This limits the rule to be active only on certain types of queries. The possible values are:

Keyword

Matching operations

select

SELECT statements

insert

INSERT statements

update

UPDATE statements

delete

DELETE statements

grant

All grant operations

revoke

All revoke operations

create

All create operations

alter

All alter operations

drop

All drop operations

use

USE operations

load

LOAD DATA operations

Db防火墙参数

rules

         强制参数必须指定。指定规则文件的位置。

Action

         此参数是可选的，并确定查询匹配规则时采取的操作

        Allow :which allows all matching queries to proceed but blocks those that don't match,

        Block :which blocks all matching queries,

        Ignore : which allows all queries to proceed.

当action=allow，下面的语句将被允许：

COM_QUIT: Client closes connection

COM_PING: Server is pinged

COM_CHANGE_USER: The user is changed for an active connection

COM_SET_OPTION: Client multi-statements are being configured

COM_FIELD_LIST: Alias for the SHOW TABLES; query

COM_PROCESS_KILL: Alias for KILL <id>; query

COM_PROCESS_INFO: Alias for SHOW PROCESSLIST;

可以有黑名单和白名单功能，通过配置一个过滤器动作action=allow或者action=block。然后你可以用不同的规则文件，每个过滤器，一个黑名单和白名单的另一。在此之后，您只需要将这两个过滤器添加到一个服务

[my-firewall-service]type=serviceservers=server1router=readconnrouteuser=maxuserpasswd=maxpwdfilters=dbfw-whitelist|dbfw-blacklist[dbfw-whitelist]type=filtermodule=dbfwfilteraction=allowrules=/home/user/whitelist-rules.txt[dbfw-blacklist]type=filtermodule=dbfwfilteraction=blockrules=/home/user/blacklist-rules.txt

 

应用规则使其生效

users指令定义了应用规则的用户。

users NAME... match { any | all | strict_all } rules RULE...

name: 格式： user@0.0.0.0 ，可以使用 %

After this either the keyword any all or strict_all is expected. This defined how the rules are matched. If any is used when the first rule is matched the query is considered blocked and the rest of the rules are skipped. If instead the all keyword is used all rules must match for the query to be blocked. The strict_all is the same as all but it checks the rules from left to right in the order they were listed. If one of these does not match, the rest of the rules are not checked. This could be useful in situations where you would for example combine limit_queries and regexrules. By using strict_all you can have the regex rule first and the limit_queries rule second. This way the rule only matches if the regex rule matches enough times for the limit_queries rule to match.

案例

案例1 防止特定查询的快速执行

为了防止数据库的过度使用，我们希望对查询率设置一个限制。我们只想将此限制应用于某些导致不必要行为的查询。为了实现这一点，我们可以使用正则表达式。

rule limit_rate_of_queries deny limit_queries 10 5 60

rule query_regex deny regex '.*select.*from.*user_data.*'

首先，我们定义的查询速率的限制。规则的第一个参数将允许的查询数设置为10个查询，第二个参数将采样率设置为5秒。如果用户执行查询速度比此更快，任何与正则表达式匹配的查询将被阻塞60秒。

users %@% match all rules limit_rate_of_queries query_regex

案例2 只允许在WHERE子句中删除

次案例我们只想防止在没有where子句的情况下managers表中的数据被删除。

要实现这一点，我们需要两个规则。

第一条规则定义所有删除操作必须有一个where子句。这个规则本身并不好，所以我们需要第二个。

第二个规则阻止与正则表达式匹配的所有查询。

rule safe_delete deny no_where_clause on_queries delete

rule managers_table deny regex '.*from.*managers.*'

 

users %@% match all rules safe_delete managers_table

测试

配置如下的过滤规则：

#过滤没有where的delete[noWhereDelete filter]type=filtermodule=dbfwfilterrules=/etc/ms_filter/noWhereDelete

测试，报错。

mysql> delete from tb ;ERROR 1141 (HY000): Access denied for user 'monitor'@'192.168.1.204' to database 't1': dbfwfilter: Query could not be tokenized and will hence be rejected.