MySQL :: Group profiles in MySQL Enterprise Firewall

firewall illustration

MySQL Firewall is an enterprise security solution providing ease of mind while protecting your database from rogue queries. Sometimes granting wide-style access privileges may feel a bit too generous, and leaves you wondering whether you could do something more. Join us as we explore Firewall, and (in particular) using Group profiles.

Having fun with Drupal

In MySQL you can GRANT privileges per user, and per database. You may go as far as granting blanket privileges (GRANT ALL …), or be more specific and grant a subset of them (e.g GRANT SELECT, UPDATE …). If database-wide access feels too permissive, one can grant privileges to a subset of database tables only. This level of access control granularity is achievable with privileges only, and may very well meet your needs.

Many applications proscribe the exact set of privileges required for successful operation. As an example, Drupal advises following set of privileges on a database:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,
INDEX, ALTER, CREATE TEMPORARY TABLES ON databasename.*
TO 'username'@'localhost' IDENTIFIED BY 'password';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,

INDEX, ALTER, CREATE TEMPORARY TABLES ON databasename.*

TO 'username'@'localhost' IDENTIFIED BY 'password';

One may, semi-jokingly, ask – “Will that be all?”. Drupal will, of course, make good use of all these privileges, but security wise we’re a bit less happy. Applications in general (and web applications in particular) are constantly a target of malicious attacks. In our example, write operations can be used for regular operation (adding and updating content), as well as taking your data for ransom.

Disambiguation between valid and invalid usage is sometimes quite tricky. At some point, your user account credentials may become compromised, and by the time you detect problematic usage, your RTO and RPO objectives will go “down the drain”.

Enter query firewall.

The Firewall

MySQL Enterprise Firewall enables you to record acceptable SQL usage (e.g. during application development or test phase), ensuring that recorded usage profile is honored during application operation. User accounts can be registered with Firewall, and assigned with some pre-recorded Firewall usage profile.

A profile consists of (normalized) set of allowed queries for a particular user. If we’d record actual user queries, the list would quickly become quite extensive:

SELECT author, content, date FROM articles WHERE article_id = 14;
SELECT author, content, date FROM articles WHERE article_id = 3;
SELECT author, content, date FROM articles WHERE article_id = 12;
SELECT author, content, date FROM articles WHERE article_id = 5;
...

SELECT author, content, date FROM articles WHERE article_id = 14;

SELECT author, content, date FROM articles WHERE article_id = 3;

SELECT author, content, date FROM articles WHERE article_id = 12;

SELECT author, content, date FROM articles WHERE article_id = 5;

...

Instead of storing all these queries within a usage profile, Firewall will “digest” such queries, to look a bit like this:

SELECT author, content, date FROM articles WHERE article_id = ?;

1	SELECT author, content, date FROM articles WHERE article_id = ?;

Only a single, so called “normalized” query will be recorded instead, which makes Firewall usage profiles much lighter. While a certain level of granularity is arguably lost in the process, we feel like it strikes a good balance between security and memory requirements. After all, if one can SELECT information from one article, why wouldn’t it be able to do the same with another?

Introducing Group profiles

Previous Firewall implementation provided a profile per user account. In practice, for a single Drupal installation, this would do the trick. If you had multiple users that’d have same usage profile, you’d need to record it for each of them from scratch. Depending on the number of users, this would range from a minor nuisance, and up to a daunting task.

groups are always more fun

Group profiles are exactly what you’d expect. They provide a way to record allowed normalized queries once, and share it between many user accounts. This way you can avoid re-recording overhead, and also reduce memory usage due to profile data duplication. Anecdotally, some of our customers record 10s to 100s of thousands of normalized queries in their profiles, which amounts to some hefty memory requirements.

Group profiles are quite flexible as well – they may completely replace current user account profiles. If you think about it, a group profile with a single user member is a functional equivalent of a user account profile.

As a consequence, in an effort to reduce duplicate functionality, user profiles will most probably be deprecated in upcoming releases (TBD). As always, migration facilities will be provided to make conversion of current user profiles a breeze.

A great additional property of group profiles is composition. User accounts can be members or multiple group profiles. For instance, cmsuser could have WordPress and Joomla group profiles applied, each of which was recorded previously with a respective application typical usage.

As always, with great power comes great responsibility, so it’s a good idea to take some time to understand the implications of attaching multiple group profiles. But, enough talk – let’s see it in action!

Firewall installation

We’ll quickly install and setup Firewall by hand. If you want to know more details about various ways to install Firewall, there’s probably no better place that our MySQL User Manual pages on Firewall installation. Either way, install procedure is super-easy:

user@host $ mysql -u root -p < linux_install_firewall.sql

Enter password: (enter root password here)

Firewall was also updated to use privileges for restricted operations involving profile management (remember, no more SUPER), so we’ll need to grant FIREWALL_ADMIN to our administrative user account.

mysql> GRANT FIREWALL_ADMIN ON *.* TO ‘admin_account’@’localhost’;

Query OK, 0 rows affected (0.01 sec)

Recording Firewall profile

When we record a group profile, we still need some user to actually record the typical usage. As an example, we’ll do minimal install of Drupal 9. We’ll create a new database and a new user for that purpose. We’ll also grant the required privileges, as documented:

mysql> CREATE DATABASE drupal_db;
Query OK, 1 row affected (0.05 sec)
mysql> CREATE USER ‘drupal_user’@’localhost’
    -> IDENTIFIED BY ‘password’;
Query OK, 0 rows affected (0.05 sec)
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE,
    -> DROP, INDEX, ALTER, CREATE TEMPORARY TABLES
    -> ON drupal_db.* TO ‘drupal_user’@’localhost’;
Query OK, 0 rows affected (0.04 sec)

Next, we’ll create a new group profile, we’ll set group operation mode to RECORDING, and set the user filter to match the recording user:

mysql> CALL mysql.sp_set_firewall_group_mode_and_user
    -> (‘drupal_group’, ‘RECORDING’, ‘drupal_user@localhost’);
+-------------------------------------------------------+
| read_firewall_group_allowlist(arg_group_name,FW.rule) |
+-------------------------------------------------------+
| Imported users: 0 Imported rules: 0 |
+-------------------------------------------------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.05 sec)

After creating the group, we’ll simply enlist user as a member of that group. This will enable all queries by the particular user to be passed through profile:

mysql> CALL mysql.sp_firewall_group_enlist
    -> (‘drupal_group’, ‘drupal_user@localhost’);
Query OK, 1 row affected (0.01 sec)

NOTE: When creating a group profile, you have the option of specifying a generic user, (which needs to be a group profile member) whose queries should be recorded. If you don’t specify the user (by using another stored procedure variant – sp_set_firewall_mode), then queries of all users belonging to a group profile will be recorded during recording. In our example we have only a single user enlisted in the group, so this is of little consequence.

However, profiles can be extended with new queries at the later time (by turning RECORDING on again). Once you start enlisting other users to a group profile, all their queries may be recorded when the profile mode is switched to RECORDING. It may be a good idea to have a recording user which will provide you with better control over which queries are recorded. This particular user can be used when you choose to extend the profile with new approved queries.

Firewall operation modes

RECORDING mode

Group profile is now in RECORDING mode, which will gather all normalized queries into Firewall in-memory cache. Given that application behaviour may change if queries are not allowed, during RECORDING mode Firewall won’t block any queries. Connect with the recording user to the database and issue queries. In our example, we’ll start Drupal installation procedure:

Drupal installation screenshot

Once we’ve installed Drupal, we can check the number of recorded queries. You can also inspect the recorded set available at performance_schema.firewall_group_allowlist:

mysql> SELECT COUNT(*) FROM
    -> performance_schema.firewall_group_allowlist;
+----------+
| COUNT(*) |
+----------+
| 173 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT * FROM
    -> performance_schema.firewall_group_allowlist;
(… list of recorded Drupal queries omitted …)

Keep in mind that the exact number of recorded queries will depend on the Drupal installation profile you chose. Each profile may create different tables, causing Drupal to issue additional queries. In this example, we went with minimal installation.

DETECTING mode

Performance schema table exposes state of recorded queries in Firewall memory cache. These are not persisted during RECORDING phase, due to the performance concerns. Once your recording session is done, you can switch your profile’s operational mode to DETECTING. Switching operational mode will persist recorded queries to the mysql schema table:

mysql> SELECT COUNT(*) FROM mysql.firewall_group_allowlist
    -> WHERE NAME=”drupal_group”;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.01 sec)
mysql> CALL mysql.sp_set_firewall_group_mode
    -> (‘drupal_group’, ‘DETECTING’);
Query OK, 173 rows affected (0.09 sec)
mysql> SELECT COUNT(*) FROM mysql.firewall_group_allowlist
    -> WHERE NAME=”drupal_group”;
+----------+
| COUNT(*) |
+----------+
| 173 |
+----------+
1 row in set (0.01 sec)

We’ve currently put our group profile to DETECTING operational mode. If a user that is a member of a group firewall issues a query that was not recorded during RECORDING phase, this will cause an information level message emitted to the error log. The query itself, however, will execute and won’t be blocked.

This could be considered a less invasive form of using Firewall – your application won’t suddenly misbehave if it issues an unapproved query, but you’ll be informed about the transgression. It may very well make sense to use this mode in the beginning, so that you may analyze queries that happen from time to time and to be able to add them to the profile by incrementally recording at some later time.

To see how this would look, we may try to install some Drupal module, which should issue some queries that were not recorded in the profile previously. As a consequence, a series of messages will be emitted to the log, similar to this:

2021-01-20T07:23:26.366393Z 14 [Note] [MY-011191] [Server]
Plugin MYSQL_FIREWALL reported:
‘SUSPICIOUS STATEMENT from ‘drupal_user@localhost’.
Reason: No match in allowlist.
Statement: SELECT ? FROM taxonomy_term_revision LIMIT ?, …’

If you can’t see them, you may need to adjust your log_error_verbosity to include INFORMATION level messages (i.e. set it to value of 3). At this point, there’s a large number of them, given that installation of Drupal’s Taxonomy module introduces plenty of as-of-yet unseen queries. It is recommended for a recording session to be thorough, as to make these messages few and far between. DETECTING mode will then become a way of detecting some extraordinary behaviour. By analyzing those messages you will conclude either that your profile needs some more training, or that something strange is going on.

PROTECTING mode

Firewall’s most restrictive operational mode is PROTECTING. When you switch a profile in PROTECTING mode, any query that does not fit the profile will not be executed. This can have consequences on the application behaviour, so you should turn it on when you’re confident enough that no other queries are expected, nor allowed.

We’re going to set drupal_group group profile to PROTECTING mode by using sp_set_firewall_group_mode:

mysql> CALL mysql.sp_set_firewall_group_mode
    -> (‘drupal_group’, ‘PROTECTING’);
Query OK, 173 rows affected (0.07 sec)

Queries that do not match group profile will now be blocked (you need to set mysql_firewall_trace global variable in order to see informational messages in the error log):

2021-01-20T07:55:24.132101Z 21 [Note] [MY-011192] [Server]
Plugin MYSQL_FIREWALL reported:
‘ACCESS DENIED for ‘drupal_user@localhost’.
Reason: No match in allowlist.
Statement: DROP SCHEMA drupal_db‘

The user that issued the offending query will receive simple client error message:

mysql> DROP DATABASE drupal_db;

ERROR 1045 (28000): Statement was blocked by Firewall

OFF mode

From time to time, you may temporarily wish to turn off firewall processing for a particular group of users. This may happen, e.g. when you are performing one-time maintenance tasks. At that times, you can switch group operational mode to OFF:

mysql> CALL mysql.sp_set_firewall_group_mode
    -> (‘drupal_group’, ‘OFF’);
Query OK, 173 rows affected (0.08 sec)

NOTE: Keep in mind, once you’ve switched to OFF, Firewall will provide no protection, and any query will be allowed for users of the particular profile (unless they have other group profiles attached). Try to keep profiles in OFF operation mode only for limited amounts of time in production environment.

RESET mode

A special operation mode (RESET) will clear a profile of all previously recorded queries. For instance – we want our Drupal application to perform only regular day-to-day activities, but we’ve just recorded the whole installation procedure in the drupal group. We can clear recorded queries, and restart recording in order to capture a more lean and secure version of typical usage:

mysql> CALL mysql.sp_set_firewall_group_mode
    -> (‘drupal_group’, ‘RESET’);
Query OK, 173 rows affected (0.06 sec)
mysql> CALL mysql.sp_set_firewall_group_mode
    -> (‘drupal_group’, ‘RECORDING’);
+-------------------------------------------------------+
| read_firewall_group_allowlist(arg_group_name,FW.rule) |
+-------------------------------------------------------+
| Imported users: 0 Imported rules: 0 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
Query OK, 1 row affected (0.01 sec)

Now users that are members of the group will be allowed to use regular, day-to-day queries (such as showing and editing articles), but their activity will be logged (and optionally blocked) if they try to do something that would match queries that were required during install phase.

Multiple groups

Firewall group functionality is quite powerful. It enables user accounts to be members of different groups at the same time. You could have, e.g, a cmsuser that is a member of Drupal and WordPress groups. It may allow you to achieve some really clean and powerful security setups.

Unfortunately, once you get a flexible tools, you’re in charge of configuring it properly, and there are some pitfalls that you should consider when testing group functionality. Here’s a short (and non-extensive) list of properties that may not be immediately apparent.

Gotchas

In all of these examples, we’ll assume that user is a member of two groups, say WordPress and Drupal.

If both groups are in DETECTING mode, each group will emit messages about offending queries to the error log. All queries will be allowed.
If one of the groups is in OFF mode, and other in DETECTING or PROTECTING mode, behaviour will be as if the group in OFF mode is not attached.
If at least one group is in RECORDING mode all queries of the group members will be allowed, even if they belong to other groups. This is a consequence of a fact that queries issued in RECORDING mode must be allowed in order for application to behave properly.
Queries of users belonging to two groups in RECORDING mode will be recorded in both groups (unless group is set to filter recording queries of only one user account.)
If one group is in DETECTING mode, and the other is in PROTECTING mode, all queries will be allowed. This is because DETECTING mode is more permissive.

As you can see, some of the combinations may be less fortunate. If you engage in assigning users to multiple groups, we advise you to thoroughly analyze the behaviour of your setup, due to the complex nature of the rules. Therefore, unless necessary, we recommend that you enlist any particular user to a single group at the time. Caveat emptor.

Exit and out

We continue our efforts to provide you with the tools you need. If you find them useful, or have some ideas or questions, give us a shout. Enjoy your experience, and once again, thank you for using MySQL!