MySQL Enterprise Firewall is a commercial extension, and is included with MySQL Enterprise Edition. This new SQL based firewall was just released in the MySQL 5.6.24 Enterprise Server! Let me tell you a bit more about how it reduces security vulnerabilities, how it works, and how to use it.
All too often, as statistics and daily headlines show, badly written applications continue to expose an organizations sensitive data to malicious attackers. These vulnerabilities are continuously being exploited to steal personal, confidential information such as login credentials, credit card numbers and social security numbers.
The most common web application attack is SQL Injection, which according to security reports like the Verizon Data Breach Investigation Report, OWASP lists and others, is the result of poorly coded applications. Without going into too much detail, it is true that SQL injection can be prevented by properly coding an application. However we cannot guarantee that all applications are coded correctly to prevent an SQL Injection attack and even the best security oriented developer can make mistakes.
Plus, there is continuous pressure to get new applications to market quickly, which conflicts with taking the necessary time and effort to ensure applications are secure and free of vulnerabilities.
And it not just your code you need to be concerned about. There are plenty of web-frameworks out there, from rigid and complex frameworks to simple ones that carry a set of security issues, including SQL injection and other attack vulnerabilities. Or maybe you’re running other third party software which can have its own set of security vulnerabilities.
To overcome SQL Injection vulnerabilities, some would say just use prepared statements, but even this helpful API often seems to be a hurdle for developers under pressure to get their applications to market ASAP. And, when it comes to development pace, nothing is quicker and easier than to use than simple string concatenation and direct SQL statements. So, given time constraints and the push for getting applications to market immediately, many applications are ripe for SQL-injection style attacks, and the actual statistics show this.
So how can you overcome these challenges? Put in another layer of protection. One such effective approach to managing and reducing the risks associated with SQL-injection attacks is to introduce a query sanitizer at the database level which sorts out all good SQL (and let it run) from the bad SQL (which is rejected). This concept is referred to as an SQL firewall.
Today we’re very happy to announce that we have a firewall the runs within the MySQL server. MySQL Enterprise Firewall is available within the MySQL 5.6 Enterprise Server in version 5.6.24!
Installing the Firewall
The MySQL Enterprise Firewall plugin is bundled with the MySQL 5.6.24 (and later) Enterprise Server binaries, and is easily installed using the provided SQL file:
|
1 2 3 4 5 |
:: on Windows dos> mysql -u root -p mysql < share\win_install_firewall.sql # on UNIX/Linux shell> mysql -u root -p mysql < share/linux_install_firewall.sql |
The MySQL Enterprise Firewall is delivered as a plugin that can be easily enabled and used. Technically its composed of three user-defined functions (UDFs) and an information schema plugin. Management of the in-memory Firewall rules and persistent settings is handled by a regular stored procedure. All of these pieces are installed and set up when you load the firewall by running the [win|linux]_install_firewall.sql file which is located in the under MySQLs directory in the share directory.
If you examine the the [win|linux]_install_firewall.sql file, you’ll see that much of the management is done using regular SQL (except for the UDFs), and thus you can modify it to your heart’s content if you feel you want to improve upon it or customize it in any way. For example, here’s the stored procedure used to manage the Firewall modes:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE PROCEDURE sp_set_firewall_mode (IN arg_userhost VARCHAR(80), IN arg_mode varchar(12)) BEGIN IF arg_mode = "RECORDING" THEN SELECT read_firewall_whitelist(arg_userhost,FW.rule) FROM mysql.firewall_whitelist FW WHERE FW.userhost=arg_userhost; END IF; SELECT set_firewall_mode(arg_userhost, arg_mode); if arg_mode = "RESET" THEN SET arg_mode = "OFF"; END IF; INSERT IGNORE INTO mysql.firewall_users VALUES (arg_userhost, arg_mode); UPDATE mysql.firewall_users SET mode=arg_mode WHERE userhost = arg_userhost; IF arg_mode = "PROTECTING" OR arg_mode = "OFF" THEN DELETE FROM mysql.firewall_whitelist WHERE USERHOST = arg_userhost; INSERT INTO mysql.firewall_whitelist SELECT USERHOST,RULE FROM INFORMATION_SCHEMA.mysql_firewall_whitelist WHERE USERHOST=arg_userhost; END IF; END |
You can verify the installation by running SHOW PLUGINS. You should be able to see the the Firewall plugins in the list.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> show plugins; +----------------------------+----------+--------------------+-------------+-------------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+-------------+-------------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | PROPRIETARY | ... | partition | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY | | MYSQL_FIREWALL | ACTIVE | AUDIT | firewall.so | PROPRIETARY | | MYSQL_FIREWALL_WHITELIST | ACTIVE | INFORMATION SCHEMA | firewall.so | PROPRIETARY | | MYSQL_FIREWALL_USERS | ACTIVE | INFORMATION SCHEMA | firewall.so | PROPRIETARY | +----------------------------+----------+--------------------+-------------+-------------+ 45 rows in set (0.00 sec) |
Configuring the Firewall
After successfully installing you need to “teach” the firewall which type of SQL queries you consider safe. The easiest way to do this is to set the firewall into a recording mode.
Let’s imagine that you’ve got a WordPress installation which you want to protect, and the application is using the ‘wpuser@localhost’ account in your supporting MySQL database instance.
You first register this account with the Firewall. You do this by calling the stored proceedure we created earlier:
mysql> CALL sp_set_firewall_mode('wpuser@localhost','RECORDING');
Now you can go into wordpress and click on the links and perform acceptible operations – as opposed to things are hacker would try. The Firewall will record these SQL statements as templates which are somewhat similar to what prepared statements look like.
When you think you are done – you’ve covered all the test cases for normal usage – you then turn the Firewall into a PROTECTING mode and the firewall will now reject unwanted queries:
mysql> CALL sp_set_firewall_mode('wpuser@localhost','PROTECTING');
Here’s a visual diagram of the MySQL Enterprise Firewall workflow that helps to demonstrate how things work:
You can easily review and audit the rules that you’ve just recorded:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
mysql> SELECT userhost, substr(rule,1,80) FROM mysql.firewall_whitelist WHERE userhost= 'wpuser@localhost'; +------------------+----------------------------------------------------------------------------------+ | userhost | substr(rule,1,80) | +------------------+----------------------------------------------------------------------------------+ | wpuser@localhost | SELECT * FROM `wp_posts` WHERE `ID` = ? LIMIT ? | | wpuser@localhost | SELECT `option_value` FROM `wp_options` WHERE `option_name` = ? LIMIT ? | | wpuser@localhost | SELECT `wp_posts` . * FROM `wp_posts` WHERE ? = ? AND `wp_posts` . `ID` = ? AND | | wpuser@localhost | SELECT `post_author` FROM `wp_posts` WHERE `ID` = ? LIMIT ? | | wpuser@localhost | SELECT `wp_posts` . `ID` FROM `wp_posts` WHERE ? = ? AND `wp_posts` . `post_type | | wpuser@localhost | SELECT `t` . * , `tt` . * FROM `wp_terms` AS `t` INNER JOIN `wp_term_taxonomy` A | | wpuser@localhost | SELECT * FROM `wp_users` WHERE `user_login` = ? | | wpuser@localhost | SELECT `p` . `ID` FROM `wp_posts` AS `p` WHERE `p` . `post_date` > ? AND `p` . ` | | wpuser@localhost | SELECT `post_id` , `meta_key` , `meta_value` FROM `wp_postmeta` WHERE `post_id` | | wpuser@localhost | SELECT `term_taxonomy_id` FROM `wp_term_taxonomy` WHERE `taxonomy` = ? AND `term | | wpuser@localhost | SET NAMES utf8 | | wpuser@localhost | SELECT * FROM `wp_comments` JOIN `wp_posts` ON `wp_posts` . `ID` = `wp_comments` | | wpuser@localhost | SELECT SQL_CALC_FOUND_ROWS `wp_posts` . `ID` FROM `wp_posts` WHERE ? = ? AND `wp | | wpuser@localhost | SELECT `wp_posts` . * FROM `wp_posts` WHERE `ID` IN (?) | | wpuser@localhost | SELECT `autoload` FROM `wp_options` WHERE `option_name` = ? | | wpuser@localhost | SELECT `FOUND_ROWS` ( ) | | wpuser@localhost | SELECT `t` . * , `tt` . * FROM `wp_terms` AS `t` INNER JOIN `wp_term_taxonomy` A | | wpuser@localhost | SELECT `comment_approved` , COUNT ( * ) AS `num_comments` FROM `wp_comments` GRO | | wpuser@localhost | SET SESSION `sql_mode` = ? | | wpuser@localhost | SELECT * FROM `wp_comments` WHERE `comment_post_ID` = ? AND ( `comment_approved` | | wpuser@localhost | SELECT SQL_CALC_FOUND_ROWS `wp_posts` . `ID` FROM `wp_posts` WHERE ? = ? AND YEA | | wpuser@localhost | SELECT `t` . * , `tt` . * , `tr` . `object_id` FROM `wp_terms` AS `t` INNER JOIN | | wpuser@localhost | SELECT * FROM `wp_posts` WHERE ( `post_type` = ? AND `post_status` = ? ) ORDER B | | wpuser@localhost | SELECT `user_id` , `meta_key` , `meta_value` FROM `wp_usermeta` WHERE `user_id` | | wpuser@localhost | SELECT `p` . `ID` FROM `wp_posts` AS `p` WHERE `p` . `post_date` < ? AND `p` . ` | | wpuser@localhost | SELECT * FROM `wp_comments` WHERE `comment_ID` = ? LIMIT ? | | wpuser@localhost | SELECT `t` . `term_id` , `tt` . `parent` , `tt` . `count` FROM `wp_terms` AS `t` | | wpuser@localhost | SELECT DISTINCTROW `post_author` FROM `wp_posts` WHERE `post_type` = ? AND `post | | wpuser@localhost | SELECT `comment_ID` FROM `wp_comments` WHERE `comment_post_ID` = ? AND `comment_ | | wpuser@localhost | SELECT SQL_CALC_FOUND_ROWS `wp_posts` . `ID` FROM `wp_posts` INNER JOIN `wp_term | | wpuser@localhost | SELECT YEAR ( `post_date` ) AS `year` , SQL_TSI_MONTH ( `post_date` ) AS `month` | | wpuser@localhost | INSERT INTO `wp_options` ( `option_name` , `option_value` , `autoload` ) VALUES | | wpuser@localhost | SELECT COUNT ( * ) FROM `wp_comments` WHERE `comment_post_ID` = ? AND `comment_a | | wpuser@localhost | INSERT INTO `wp_comments` ( `comment_post_ID` , `comment_author` , `comment_auth | | wpuser@localhost | SELECT `option_name` , `option_value` FROM `wp_options` WHERE `autoload` = ? | | wpuser@localhost | SELECT @@SESSION . `sql_mode` | | wpuser@localhost | UPDATE `wp_posts` SET `comment_count` = ? WHERE `ID` = ? | | wpuser@localhost | SELECT `t` . * , `tt` . * FROM `wp_terms` AS `t` INNER JOIN `wp_term_taxonomy` A | | wpuser@localhost | SELECT `t` . * , `tt` . * FROM `wp_terms` AS `t` INNER JOIN `wp_term_taxonomy` A | +------------------+----------------------------------------------------------------------------------+ |
The rules listed in the table can alternatively be created by using the normalize_statement() UDF to generate the statement digest. In MySQL all queries which pass through the parser are tokenized. Our MySQL Performance Schema already uses this feature to produce SQL digests. Our firewall takes those digests and compares them against an in-memory hash. Matching a query against a whitelist of course adds a little extra processing and our preliminary testing has shown under concurrent stress level loads only a 2-3% performance impact added by running the firewall. That’s hardly noticeable especially under normal loads. And for most a very small cost for improved security that is likely well worth it.
Firewall Status
All registered accounts and their corresponding operational mode are listed in the information_schema.mysql_firewall_users table:
|
1 2 3 4 5 6 7 8 |
mysql> SELECT * FROM information_schema.mysql_firewall_users; +------------------+------------+ | USERHOST | MODE | +------------------+------------+ | wpuser@localhost | PROTECTING | | root@localhost | OFF | +------------------+------------+ 2 rows in set (0,01 sec) |
When a user account is under Firewall protection, failure to match the incoming query with a whitelisted digest will result in an error. For example:
|
1 2 3 4 |
mysql> show tables; ERROR 1045 (28000): Statement was blocked by Firewall mysql> drop table wp_posts; ERROR 1045 (28000): Statement was blocked by Firewall |
Such failures can be audited in the MySQL Server error log. For example:
|
1 2 |
2015-03-24T22:37:02.215890Z 22 [Note] Plugin MYSQL_FIREWALL reported: 'ACCESS DENIED for wpuser@localhost. Reason: No match in whitelist. Statement: SHOW TABLES ' 2015-03-24T22:37:25.041969Z 22 [Note] Plugin MYSQL_FIREWALL reported: 'ACCESS DENIED for wpuser@localhost. Reason: No match in whitelist. Statement: DROP TABLE `wp_posts` ' |
You can also monitor the Firewall operations by looking at the related Server status counters:
|
1 2 3 4 5 6 7 8 9 |
mysql> SHOW STATUS LIKE 'Firewall%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Firewall_access_denied | 32 | | Firewall_access_granted | 138 | | Firewall_cached_entries | 39 | +-------------------------+-------+ 3 rows in set (0,00 sec) |
Adding and Removing Rules
Using the bundled stored procedure you can easily remove and add new rules. To delete all of the rules for a user, you can simply use the RESET option:
mysql> CALL sp_set_firewall_mode('u1@10.0.0.1','RESET');
The RESET option will clear the whitelist rules for the user and then turn the Firewall protection OFF for them as well. Users with the OFF mode set will be completely ignored by the Firewall (no firewalling). An empty Firewall list is not allowed in PROTECTING mode–at least one whitelist rule must exist first. This is a safety precaution that prevents you from accidentally locking yourself out of the system. Should that happen anyway, you can simply restart the server with the mysql_firewall_mode=OFF Server option set.
Also say you’ve updated your software or for some reason you need to add a rule you can simply switch the mode to RECORDING withsp_set_firewall_mode and then execute all of the SQL statements – either directly as SQL or indirectly (from the software) – that you want to be added to the whitelist, or alternatively you can also INSERT new rows directly into the ‘mysql.firewall_whitelist’ table if you prefer (not recommended). You could also easily move a list from a staged to a production system for example. When using thesp_set_firewall_mode stored procedure, the new rules will be added to any existing rules when you subsequently switch the mode back from RECORDING to PROTECTING.
We look forward to your feedback on this new feature! You can leave a comment here on the blog post or in a support ticket. If you feel that you encountered any related bugs, please do let us know via a bug report.
As always, THANK YOU for using MySQL!