New and Improved Functionality
The recently released 1.4.1 version of MariaDB MaxScale contains a bundle of great improvements to the Database Firewall Filter, dbfwfilter. This article starts by describing the dbfwfilter module and how it is used. Next we’ll find out what kinds of improvements were made to the filter in MaxScale 1.4.1 and we’ll finish by looking at a few use cases for it.
Here are the highlights of the new dbfwfilter functionality in the 1.4.1 release of MaxScale.
- Configurable filter actions on rule match
- Allow the query, block the query or ignore the match
- Logging of matching and/or non-matching queries
With these new features, you can easily implement various types of configurations including a dry-run mode where no action is taken but all matching and non-matching queries are logged.
Later on we’ll introduce the new features and explore how we can better secure our database environment by using these new features.
What Is the Database Firewall Filter?
The database firewall filter, dbfwfilter, is a module which acts as a firewall between the clients and the backend cluster. Similar to the iptables software found in most Linux based distributions, this module either allows or denies SQL queries based on a set of rules.
A rules is defined by a small and simple syntax that can be used to describe the kind of content it matches. These rules can then be assigned to users to make sets of user and rule groups. For more details about the rule syntax, read the Database Firewall Filter Documentation.
The dbfwfilter module allows you to control what kinds of queries are allowed. Because the filter understands the content that passes through it, it can prevent malicious attempts to execute SQL which can compromise your data.
Here are a few examples how the dbfwfilter can help improve the security of your database cluster.
- Block delete queries with no “WHERE” clause – preventing attacker from mass deleting data from tables and damage to customer data
- Block select queries on certain table (such as user data, customer data) with no “WHERE” clause – preventing attacker from getting mass access to confidential user data
- Only allow queries with certain columns on certain tables for a set of users. So these users will only have access to subset of columns and will not be able to access any other data
Configuring the Filter
The best way to understand how the dbfwfilter works is to configure it for use.We start by defining the rules for the filter. We’ll define a simple rule and apply it to all possible users. We already have MaxScale installed and configured for normal operation. For a good tutorial on setting up MaxScale, read the MaxScale Tutorial.
The rule we’ll create is a no_where_clause rule which matches if the query lacks a WHERE/HAVING clause. We’ll also add an optional on_queries part to the rule which allows us to limit the matching to update only.
rule protected_update deny no_where_clause on_queries update users %@% match any rules protected_update
The first line defines the rule protected_update while the second line applies this rule to all users. The match any makes it so that any rule in the list will cause it to be considered a match. Since we only have one rule, the value of match is not very important. The matching type allows you to combine simpler rules into a more complex one.
The next step is to create a filter definition. The following filter definition uses the dbfwfilter module and defines the rules parameter, which tells us where the rules for this filter are. The rules we defined earlier have been saved to /usr/home/markusjm/rules. The action parameter tells the filter what it should do when a query matches a rule. We’ll set to to block so the filter blocks any query that matches a rule.
[firewall-filter] type=filter module=dbfwfilter rules=/home/markusjm/rules action=block
We will use the following service configuration.
[RW Split Router] type=service router=readwritesplit servers=server1,server2,server3,server4 user=maxuser passwd=maxpwd filters=firewall-filter
Testing the Configuration
After we’ve configuration, we can start MaxScale and execute some queries. First we’ll create a table for our test and insert some values into it.
MySQL [test]> create table t1(id int); Query OK, 0 rows affected (0.41 sec) MySQL [test]> insert into test.t1 values (1), (2), (3); Query OK, 3 rows affected (0.08 sec) Records: 3 Duplicates: 0 Warnings: 0
Next we’ll try to update the values without defining a where clause.
MySQL [test]> update t1 set id=0; ERROR 1141 (HY000): Access denied for user 'maxuser'@'127.0.0.1' to database 'test': Required WHERE/HAVING clause is missing.
We can see that it was rejected because it matched the rule we defined. Let’s try an update with a where clause.
MySQL [test]> update t1 set id=0 where id=1; Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0
It works as expected. How about a select without a where clause?
MySQL [test]> select * from t1; +------+ | id | +------+ | 0 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec)
So our simple rule matches and works as expected. Next we’ll show a simple use case where two dbfwfilter instances are combined to form a more complex rule set.
Combining block and allow
Once we have our basic setup we can expand it by creating a second set of rules and a second filter definition. We’ll then combine these two filters into one filter pipeline which blocks queries that match the rule we defined earlier and only allow rules that match our new rule set. We start by defining the new rules.
rule safe_columns deny columns name email users %@% match any rules safe_columns
This rule matches when one of the name, address or salary columns are accessed. The rule is a simple one which allows us to restrict queries to a certain set of columns. We’ll save the configuration in /home/markusjm/rules-whitelist and continue to configure the new filter definition.
[whitelist-filter] type=filter module=dbfwfilter rules=/home/markusjm/rules-whitelist log_no_match=true action=allow
The filter definition is similar to the one we defined before apart from the different action value and the new log_no_match parameter. The filter will only allow queries that match the rules to be executed. In addition to this, all non-matching queries will be logged so we’ll know when an unexpected query is blocked.
Once we’ve configured the second filter, we can combine them into a pipeline in the following way.
[RW Split Router] type=service router=readwritesplit servers=server1,server2,server3,server4 user=maxuser passwd=maxpwd filters=whitelist-filter|firewall-filter
Now we can test how our new combined filters work. We’ll test using a simple table and one row of data.
MariaDB [(none)]> show create table test.t1G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `name` varchar(60) DEFAULT NULL, `address` varchar(60) DEFAULT NULL, `email` varchar(120) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) MariaDB [(none)]> select * from test.t1; +----------+---------------+-------------------+ | name | address | email | +----------+---------------+-------------------+ | John Doe | Castle Hill 1 | email@example.com | +----------+---------------+-------------------+ 1 row in set (0.00 sec)
Let’s try selecting name and email from the table.
MariaDB [(none)]> select name, email from test.t1; +----------+-------------------+ | name | email | +----------+-------------------+ | John Doe | firstname.lastname@example.org | +----------+-------------------+
As expected, the query is successful. We can try to select only address from the table but we will be denied access.
MySQL [(none)]> select address from test.t1; ERROR 1141 (HY000): Access denied for user 'maxuser'@'127.0.0.1'
So only queries which target either the name or email column pass through the whitelist-filter we’ve configured. Next we can test if updates to name work.
MySQL [(none)]> update test.t1 set name="No Name"; ERROR 1141 (HY000): Access denied for user 'maxuser'@'127.0.0.1': Required WHERE/HAVING clause is missing. MySQL [(none)]> update test.t1 set name="No Name" where name="John Doe"; Query OK, 1 row affected (0.11 sec) Rows matched: 1 Changed: 1 Warnings: 0 MySQL [(none)]> select name from test.t1; +---------+ | name | +---------+ | No Name | +---------+
As we can see, the filter we previously configured still works and by combining these two filters, we only allow queries that are in the set of allowed queries but not in the set of denied queries. This combining of rule sets allows us to create rich sets of rules that all the queries must conform to. Since we added the log_no_match parameter to the filter definition, we can see a log message with details about the non-matching query we executed.
2016-04-02 16:21:42 notice : [RW Split Router] Query for '%@%' by email@example.com was not matched: select address from test.t1
With this, we could implement a simple auditing mechanism at the cluster level which would allow us to detect unexpected queries and reveal information about the user who executed them.
What does the future hold?
In the near future, we’re aiming to implement smarter firewalling functionality into MaxScale. The smart firewall is aimed for minimal configuration and maximal ease of use by automating rule generation. The smart firewall is planned to be included in the next release of MaxScale.
If you have any questions, feedback or great ideas, join us on firstname.lastname@example.org for discussion about MaxScale. We also have the #maxscale IRC channel on FreeNode.