{"id":2353,"date":"2018-04-16T11:36:09","date_gmt":"2018-04-16T10:36:09","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=2353"},"modified":"2020-05-27T07:50:21","modified_gmt":"2020-05-27T06:50:21","slug":"mysql-security-mysql-enterprise-firewall","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2018\/04\/16\/mysql-security-mysql-enterprise-firewall\/","title":{"rendered":"MySQL Security \u2013 MySQL Enterprise Firewall"},"content":{"rendered":"

When thinking about security within a MySQL installation, you should consider a wide range of possible procedures \/ best practices and how they affect the security of your MySQL server and related applications. MySQL provides many tools \/ features \/ plugins in order to protect your data including some advanced features like\u00a0Audit<\/a>,\u00a0TDE<\/a>, Data Masking & De-Identification<\/a>, Password Management<\/a>, Password Validation Plugin<\/a>, User Account Locking<\/a>, etc…<\/p>\n

\"MySQL<\/p>\n

In this seventh episode of the\u00a0MySQL Security<\/strong> series, we will see how MySQL Enterprise Firewall<\/a><\/strong> can help you to strengthen the protection of your data, in real-time<\/strong>, against cyber security threats including\u00a0SQL Injection attacks<\/strong> by monitoring, alerting, and blocking unauthorized database activity without any changes<\/strong> to your applications.<\/p>\n

Installing the MySQL Enterprise Firewall Plugin<\/h2>\n

MySQL Enterprise Firewall installation is an easy one-time operation that involves running a script (e.g. linux_install_firewall.sql<\/em><\/strong> in this blog post (Linux and similar systems that use .so<\/strong> as the file name suffix); win_install_firewall.sql<\/strong> for Windows systems that use .dll<\/strong>\u00a0as the file name suffix) located in the share<\/strong><\/em> directory of your MySQL installation.<\/p>\n

I’m using MySQL 5.7.21 Enterprise Edition<\/strong> :<\/p>\n

mysql> \nSELECT version();\n+-------------------------------------------+\n| version()                                 |\n+-------------------------------------------+\n| 5.7.21-enterprise-commercial-advanced-log |\n+-------------------------------------------+<\/pre>\n
Updated on 22nd of August 2018
Note<\/span>: MySQL Enterprise Firewall\u00a0works with MySQL 8.0<\/strong><\/span> as well. In other words examples below could be done with MySQL<\/strong> 8.0.12<\/strong>+<\/em><\/p>\n
MySQL Enterprise Firewall does not work together with the MySQL Query Cache<\/a>. Fortunately the query cache is disabled by default.<\/p>\n
mysql> \nSHOW VARIABLES LIKE 'query_cache_type';\n+------------------+-------+\n| Variable_name    | Value |\n+------------------+-------+\n| query_cache_type | OFF   |\n+------------------+-------+<\/pre>\n
Note<\/span><\/em>
The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0<\/a>.<\/em><\/p>\n
Note<\/span><\/em>
For a great query cache tuning advice from Domas Mituzas<\/a>\u00a0: click here<\/a> \ud83d\ude42<\/em><\/p>\n
Now we can installed the Firewall<\/p>\n
mysql> \nSOURCE \/usr\/share\/mysql\/linux_install_firewall.sql\n\nDatabase changed\nQuery OK, 0 rows affected (0.07 sec)\n\nQuery OK, 0 rows affected (0.06 sec)\n\nQuery OK, 0 rows affected (0.01 sec)\n\nQuery OK, 0 rows affected (0.00 sec)\n\nQuery OK, 0 rows affected (0.01 sec)\n\nQuery OK, 0 rows affected (0.00 sec)\n\nQuery OK, 0 rows affected (0.00 sec)\n\nQuery OK, 0 rows affected (0.01 sec)\n\nQuery OK, 0 rows affected (0.01 sec)\n\nQuery OK, 0 rows affected (0.01 sec)\n\nQuery OK, 0 rows affected (0.01 sec)\n\nQuery OK, 0 rows affected (0.01 sec)<\/pre>\n
And check if it has been launched with the system variable\u00a0mysql_firewall_mode<\/a><\/strong><\/em>\u00a0:<\/p>\n
mysql> \nSHOW GLOBAL VARIABLES LIKE 'mysql_firewall_mode';\n+---------------------+-------+\n| Variable_name       | Value |\n+---------------------+-------+\n| mysql_firewall_mode | ON    |\n+---------------------+-------+<\/pre>\n
Alternatively, we can also add mysql_firewall_mode<\/em><\/strong> under the [mysqld]<\/em> <\/strong>option group in the MySQL configuration file :<\/p>\n
[mysqld]\nmysql_firewall_mode=ON<\/pre>\n
It is also possible to disable or enable the firewall at runtime :<\/p>\n
mysql> \nSET GLOBAL mysql_firewall_mode = OFF;\n\nSET GLOBAL mysql_firewall_mode = ON;<\/pre>\n
Playtime<\/h2>\n
The MySQL Firewall is installed!<\/p>\n
Let’s assume now we have an application that uses the schema sakila<\/a><\/em> in this instance. This application has a dedicated user account (myApp@localhost) and all the privileges on sakila<\/em> :<\/p>\n
mysql> \nCREATE USER 'myApp'@'localhost' IDENTIFIED BY 'AppU$3rPwd';\n\nGRANT ALL ON sakila.* TO 'myApp'@'localhost';<\/pre>\n
Note<\/em><\/span><\/strong><\/p>\n
The firewall maintains whitelist rules on a per-account basis.<\/em><\/p>\n
Regular queries from this\u00a0hypothetical application are :<\/p>\n