MySQL Security – MySQL Enterprise Firewall

April 16, 2018

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 AuditTDE, Password Management, Password Validation Plugin, User Account Locking, etc…

MySQL Security

 

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

 

Installing the MySQL Enterprise Firewall Plugin

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

I’m using MySQL 5.7.21 Enterprise Edition :

MySQL Enterprise Firewall does not work together with the MySQL Query Cache. Fortunately the query cache is disabled by default.

Note
The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.

Note
For a great query cache tuning advice from Domas Mituzas : click here 🙂

 

Now we can installed the Firewall

And check if it has been launched with the system variable mysql_firewall_mode :

 

Alternatively, we can also add mysql_firewall_mode under the [mysqld] option group in the MySQL configuration file :

 

It is also possible to disable or enable the firewall at runtime :

 

 

Playtime

The MySQL Firewall is installed!

Let’s assume now we have an application that uses the schema sakila in this instance. This application has a dedicated user account (myApp@localhost) and all the privileges on sakila :

Note

The firewall maintains whitelist rules on a per-account basis.

 

Regular queries from this hypothetical application are :

  • UPDATE rental SET return_date = <date> WHERE rental_id = <id>;
  • SELECT get_customer_balance(<id>, <date>);

But first, users are authenticated with :

  • SELECT staff_id, first_name, email, last_name, username, password FROM staff WHERE username = ‘<userName>’ AND password=sha1(<userPassword>);

Warning
Query above is not safe nor optimal for production.

 

Firewall allows 3 modes :

  • recording, the firewall adds the normalized statement to the account whitelist rules.
  • protecting, the firewall compares the normalized statement to the account whitelist rules. If there is a match, the statement passes and the server continues to process it. Otherwise, the server rejects the statement and returns an error to the client. The firewall also writes the rejected statement to the error log if the mysql_firewall_trace system variable is enabled.
  • detecting, the firewall matches statements as in protecting mode, but writes nonmatching statements to the error log without denying access.

 

MySQL Enterprise Firewall

Recording mode

Ok now we know our queries, let’s go back to the Firewall.

The basic and powerful idea of the MySQL Firewall is to deny SQL statement execution based on matching against a whitelist. In other words the Firewall learns acceptable statement patterns.

In order to create this whitelist, we’ll switch the Firewall in the RECORDING mode using sp_set_firewall_mode stored procedure :

We can know see what is the status of the Firewall for any user with INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS table :

 

 

During the recording mode, we can run the application. The queries generated by the application will be recorded in the Firewall’s whitelist :

Other queries…

And so on…

When the training is done switch the Firewall to protecting mode.

 

Protecting mode

Use the sp_set_firewall_mode stored procedure to switch the registered user to protecting mode:

 

Firewall stores SQL statements on a normalized digest form. You can check the whitelist with INFORMATION_SCHEMA.MYSQL_FIREWALL_WHITELIST table :

Note
For additional training you can switch back recording mode or even update (that is an UPDATE query) this table if necessary using the normalize_statement UDF.

 

In protecting mode, there are 2 kind of queries for the application point of view :

  • Acceptable

  • Unacceptable

 

SQL injection

One of the big advantage of the MySQL Firewall is that it can help protect against SQL Injection attacks. In this post, I will not go into details of what is an SQL injection. However below a simplistic example to illustrate the overall principle.

User name and password are needed for authentication :

Low quality code can generate unsafe queries :

Fortunately they are blocked by the MySQL Firewall :

 

Rejected queries can be seen in the MySQL error log if the mysql_firewall_trace system variable is enabled.

Unacceptable queries :

are available in the MySQL error log :

 

 

Detecting mode

MySQL Enterprise Firewall can also be used into a intrusion-detecting mode that writes suspicious statements to the error log but does not deny access.

Now using the application user account, suspicious queries will not be blocked :

however a message is written into the MySQL error log :

 

 

Monitor the Firewall

MySQL Enterprise Firewall provides the following status variables :

 

 

Uninstall the Firewall

To remove MySQL Enterprise Firewall, execute the following statements :

Note
You may have to kill the application remaining connections (e.g. KILL CONNECTION) or reconnect the application user (e.g. mysql> connect)

 

 

MySQL Enterprise Edition

MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools and technical support to achieve the highest levels of MySQL scalability, security, reliability, and uptime.

It reduces the risk, cost, and complexity in developing, deploying, and managing business-critical MySQL applications.

MySQL Enterprise Edition server Trial Download (Note – Select Product Pack: MySQL Database).

 

MySQL Enterprise Edition

 

 

In order to go further

MySQL 5.7 Security Series

  1. Password Validation Plugin
  2. Password Management
  3. User Account Locking
  4. The Connection-Control Plugins
  5. Enterprise Audit
  6. Enterprise Transparent Data Encryption (TDE)
  7. Enterprise Firewall

 

Reference Manual

 

MySQL Security

 

Thanks for using MySQL!

Follow me on twitter

 

0

MySQL Security – MySQL Enterprise Transparent Data Encryption

April 10, 2018

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  AuditFirewall, Password Management, Password Validation Plugin, User Account Locking, etc…

MySQL Security

In this sixth episode of the MySQL 5.7 Security series, we will see how data-at-rest encryption helps organizations implement stronger security controls and satisfy regulatory compliance.

You will be able to protect the privacy of your information, prevent data breaches and help meet popular regulatory requirements including GDPR, PCI DSS, HIPAA with MySQL Enterprise Transparent Data Encryption aka TDE.

 

MySQL Keyring

MySQL Enterprise Transparent Data Encryption relies on a MySQL Keyring plugin for master encryption key management. MySQL Server provides many plugin choices including :

 

In this article I’ll use the keyring_encrypted_file plugin with MySQL Enterprise Edition 5.7.21.

 

Warning
The keyring_file and keyring_encrypted_file plugins for encryption key management are not intended as a regulatory compliance solution. Security standards such as PCI, FIPS, and others require use of key management systems to secure, manage, and protect encryption keys in key vaults or hardware security modules (HSMs).

 

These keyring plugins would be use with MySQL Enterprise Audit to store the audit log file encryption password (you can read my previous article) and InnoDB tables when encrypted with  MySQL Enterprise Transparent Data Encryption, actually the topic of this article but you already know that, right? :).

 

Keyring Plugin Installation : keyring_encrypted_file

Like I stated before many keyring plugins are available and I’ll use the keyring plugin : keyring_encrypted_file, that stores keyring data in an encrypted file local to the server host.

 

Only one keyring plugin should be enabled at a time. So let’s check that we have none so far :

 

To be usable by the server, the plugin library file must be located in the MySQL plugin directory :

 

Update the MySQL configuration file and restart the MySQL server :

  • To be usable during the server startup process, keyring_encrypted_file must be loaded using the early-plugin-load option.
  • To specify the password for encrypting the keyring data file, set the keyring_encrypted_file_password system variable.
  • The keyring_encrypted_file_data system variable optionally configures the location of the file used by the keyring_encrypted_file plugin for data storage.

 

Warning
Because the my.cnf file stores a password when written as shown, it should have a restrictive mode and be accessible only to the account used to run the MySQL server.

 

 

We can have a look at the Keyring file. Its location could be found with keyring_file_data variable :

 

After a keyring plugin is configured, InnoDB tables may be encrypted.

 

InnoDB Transparent Encryption

MySQL Enterprise Transparent Data Encryption

InnoDB supports data encryption for InnoDB tables stored in file-per-table tablespaces.

I’m using Sakila Database, available here.

Below a couple of random queries :

 

Data-at-Rest Encryption

Usually it is not necessary to encrypt all the tables. After a deep data analysis let’s assume 2 relevant InnoDB tables must be encrypted : customer and staff.

So let’s encrypted these tables!

We can do it easily with a CREATE or ALTER TABLE statement using ENCRYPTION=”Y” :

There we go!

 

Note
Plan appropriately when altering an existing table with the ENCRYPTION option. ALTER TABLE … ENCRYPTION operations rebuild the table using ALGORITHM=COPY. ALGORITHM=INPLACE is not supported.

 

Identify Tables that Use InnoDB Tablespace Encryption

This information is available in INFORMATION_SCHEMA.TABLES :

 

Query encrypted Tables

Transparent Data Encryption means transparent for the clients ie no change in your applications.

In other words, you don’t have to modify your queries and sames queries with same datasets will give me the same result :

 

Master Key Rotation

Legal and regulatory requirements require a periodically rotation of the master encryption key. It’s also more than recommended to rotate the master key whenever you suspect a security breach. Rotating the master encryption key only changes the master encryption key and re-encrypts tablespace keys.

To rotate the master encryption key use ALTER INSTANCE ROTATE INNODB MASTER KEY :

Et voilà!

Now you know the MySQL Enterprise Transparent Encryption basics \o/

 

I would like to address a last item, regarding a common customer question about the disk footprint of an encrypted table.

So let’s play a little bit more with TDE.

We have a table, encryptedTable, not yet encrypted with 5 millions rows

Table size is 722 MB

 

Table is now encrypted

 

Table size is now : 870 MB

 

Is there a way to reclaim space (and optionally improve I/O efficiency)?  Yes of course, OPTIMIZE TABLE :

 

Table size is again 722 MB

 

 

MySQL Enterprise Edition

MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools and technical support to achieve the highest levels of MySQL scalability, security, reliability, and uptime.

It reduces the risk, cost, and complexity in developing, deploying, and managing business-critical MySQL applications.

MySQL Enterprise Edition server Trial Download (Note – Select Product Pack: MySQL Database).

 

MySQL Enterprise Edition

 

 

In order to go further

MySQL 5.7 Security Series

  1. Password Validation Plugin
  2. Password Management
  3. User Account Locking
  4. The Connection-Control Plugins
  5. Enterprise Audit
  6. Enterprise Transparent Data Encryption (TDE)
  7. Enterprise Firewall

 

Reference Manual

 

MySQL Security

 

Thanks for using MySQL!

Follow me on twitter

 

6

MySQL Security – MySQL Enterprise Audit

April 4, 2018

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 Transparent Data Encryption aka TDE, Firewall, Password Management, Password Validation Plugin, etc…

MySQL Security

In order to spot database misuse and/or to prove compliance to popular regulations including GDPR, PCI DSS, HIPAA, … database administrators can be required to record and audit database activities. In this fifth episode of the MySQL 5.7 Security series, we will see what MySQL Enterprise Audit provides to help organizations implement stronger security controls and satisfy regulatory compliance.

 

MySQL Enterprise Audit

MySQL Enterprise Edition includes MySQL Enterprise Audit which uses the open MySQL Audit API to enable standard, policy-based monitoring, logging, and blocking of connection and query activity executed on specific MySQL servers.

Audit plugin enables MySQL Server to produce a XML (default) or JSON log file (named audit.log) containing an audit record of server activity. The log contents include when clients connect and disconnect, and what actions they perform while connected, such as which databases and tables they access.

MySQL Enterprise Audit

 

Installation

I’m using MySQL 5.7.21 Enterprise Edition :

 

To install MySQL Enterprise Audit, look in the share directory of your MySQL installation and choose the script that is appropriate for your platform :

audit_log_filter_win_install.sql : Choose this script for Windows systems that use .dll as the file name suffix.

audit_log_filter_linux_install.sql : Choose this script for Linux and similar systems that use .so as the file name suffix.

Then run the script :

e.g.

 

You can verify the plugin installation examining the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement

 

The Audit plugin installed a bunch of User-Defined Function (UDF) :

These functions are described here.

 

The Audit plugin enables MySQL Server to produce a log file based in the datadir and named audit.log. By default, its contents is written in XML format, without compression nor encryption :

 

Configuration

After MySQL Enterprise Audit is installed, you can use the audit_log option for subsequent server startups to control the Audit plugin activation.

e.g. Prevent the plugin from being removed at runtime, select JSON as logging format and force log file rotation when it reaches 1MB :

Then restart the MySQL server.

We have now a JSON format audit log file.

 

As you can see, by default, contents of audit log files produced by the audit log plugin are not encrypted and may contain sensitive information, such as the text of SQL statements. For security reasons, audit log files should be written to a directory accessible only to the MySQL server and to users with a legitimate reason to view the log.

For additional security, you can enable audit log file encryption.

Encryption

To encrypt your audit log file, the MySQL keyring must be enabled because audit logging uses it for password storage. Note that any keyring plugin can be used e.g. : file-based, encrypted file, OKV KMIP compatible, AWS, …

To control whether audit log file encryption is enabled, set the audit_log_encryption system variable at server startup. Permitted values are NONE (no encryption; the default) and AES (AES-256-CBC cipher encryption).

To set or get the encryption password, use these user-defined functions (UDFs):

  • To set the encryption password, invoke audit_log_encryption_password_set(), which stores the password in the keyring, renames the current log file, and begins a new log file encrypted with the new password.
  • To get the current encryption password, invoke audit_log_encryption_password_get(), which retrieves the password from the keyring.

More details here.

Let’s practice!

Keyring Plugin Installation : keyring_encrypted_file

Like I stated before many keyring plugins are available. I’ll use the keyring plugin : keyring_encrypted_file, that stores keyring data in an encrypted file local to the server host.

 

Warning
The keyring_encrypted_file plugin for encryption key management is not intended as a regulatory compliance solution. Security standards such as PCI, FIPS, and others require use of key management systems to secure, manage, and protect encryption keys in key vaults or hardware security modules (HSMs).

 

Update the MySQL configuration file and restart the MySQL server :

 

Warning
Because the my.cnf file stores a password when written as shown, it should have a restrictive mode and be accessible only to the account used to run the MySQL server.

 

 

Now the default name of Audit log file is audit.log.enc instead of audit.log.
And obviously this is not the only change. Guess what? this new file is… encrypted (AES-256-CBC cipher encryption)

 

If you don’t want to use encryption anymore you’ll need to update audit_log_encryption option in your MySQL configuration file.
e.g.

Then restart the MySQL server

 

Compression

You can lowering the disk footprint of your audit logs with compression.

To control whether audit log file compression is enabled, set the audit_log_compression system variable at server startup.

Permitted values are NONE (no compression; the default) and GZIP (GNU Zip compression).

More details here.

Let’s practice!

 

Update the MySQL server configuration file :

 

Then restart the server :

Now the default name of Audit log file is audit.log.gz instead of audit.log.
This new file is compressed with GNU Zip.

 

Note
If both compression and encryption are enabled, compression occurs before encryption. To recover the original file manually, first decrypt it, then uncompress it.

 

If you don’t want to use compression anymore you’ll need to update audit_log_compression option in your MySQL configuration file.
e.g.

Then restart the MySQL server

 

Audit Log Filtering

Another amazing feature is the audit log filtering functions. It enables filtering control in JSON format by providing an interface to create, modify, and remove filter definitions and assign filters to user accounts.

When a connection arrives, the audit log plugin determines which filter to use for the new session by searching for the user account name in the current filter assignments:

  • If a filter is assigned to the user, the audit log uses that filter.
  • Otherwise, if no user-specific filter assignment exists, but there is a filter assigned to the default account (%), the audit log uses the default filter.
  • Otherwise, the audit log selects no audit events from the session for processing.

By default, no accounts have a filter assigned, so no processing of auditable events occurs for any account.

The following list briefly summarizes the UDFs that implement the SQL interface for audit filtering control:

  • audit_log_filter_set_filter() : Define a filter
  • audit_log_filter_remove_filter() : Remove a filter
  • audit_log_filter_set_user() : Start filtering a user account
  • audit_log_filter_remove_user() : Stop filtering a user account
  • audit_log_filter_flush() : Flush manual changes to the filter tables to affect ongoing filtering

Detailed description here.

 

Examples

Users and Filters can be monitored respectively with mysql.audit_log_user and mysql.audit_log_filter tables

e.g.

 

Logging All Events

You can log all auditable events for all users by create a simple filter to enable logging and assign it to the default account:

The filter assigned to % is used for connections from any account that has no explicitly assigned filter (which initially is true for all accounts).

 

To determine whether a filter has been assigned to the current session, check the session value of the read-only audit_log_filter_id system variable. If the value is 0, no filter is assigned. A nonzero value indicates the internally maintained ID of the assigned filter:

Note
Starting with MySQL 5.7.22, you can use JSON_PRETTY function that prints out a JSON value in a format that is easy to read.

 

Remove a filter on a user account

I don’t want to audit all users anymore. Instead I’ll use the log_all filter to audit the user account olivier@%.

Note
Filtering of current sessions for the user remains unaffected. 

 

Assign a filter explicitly to a particular user account

The filter is already defined (log_all), let’s assign it to the olivier@% user account :

In another session connection with user olivier :

All commands from user olivier are audited :

 

Logging Specific Event Classes

Create a new filter : log_table_access_events

It allows to audit all table accesses. This filter is assigned to user olivier. So it replace any previous filter.

 

Connection with user olivier

Run a query on a table (table access)

 

This command is logged in Audit log file :

 

Other kinds of queries will not be logged :

The Audit log file did not changed

 

It’s also possible to select a specific event on these tables like INSERT, DELETE, UPDATE…

You’ll find all the details here.

 

Blocking Execution of Specific Events

Event items can include an abort item that indicates whether to prevent qualifying events from executing.

This is convenient  to block execution of specific SQL statements.

Let’s block INSERTs, UPDATEs and DELETEs on sakila.payment table :

 

When using user olivier :

Any forbidden queries will be blocked…

and logged by the MySQL Audit plugin.

 

MySQL Enterprise Audit plugin is very powerful!

Please read the documentation for more information.

 

Uninstallation

To remove MySQL Enterprise Audit, execute the following statements :

Voilà!

 

 

MySQL Enterprise Edition

MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools and technical support to achieve the highest levels of MySQL scalability, security, reliability, and uptime.

It reduces the risk, cost, and complexity in developing, deploying, and managing business-critical MySQL applications.

MySQL Enterprise Edition server Trial Download (Note – Select Product Pack: MySQL Database).

MySQL Enterprise Edition

 

 

In order to go further

MySQL 5.7 Security Series

  1. Password Validation Plugin
  2. Password Management
  3. User Account Locking
  4. The Connection-Control Plugins
  5. Enterprise Audit
  6. Enterprise Transparent Data Encryption (TDE)
  7. Enterprise Firewall

 

Reference Manual

 

MySQL Security

 

Thanks for using MySQL!

Follow me on twitter

6

MySQL Security – The Connection-Control Plugins

March 29, 2018

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 Transparent Data Encryption aka TDEAudit, Firewall, Password Management, Password Validation Plugin, User Account Locking, etc…

MySQL Security

An ordinary threat databases could face is an attempt to discover the password by systematically trying every possible combination (letters, numbers, symbols). This is known as a brute force attack.
In this fourth episode of the MySQL 5.7 Security series, we will see how the MySQL DBA can leverage the Connection-Control Plugins to slow down brute force attacks.

 

The Connection-Control Plugins

The MySQL Server includes a plugin library that enables administrators to introduce an increasing delay in server response to clients after a certain number of consecutive failed connection attempts. This capability provides a deterrent that slows down brute force attacks that attempt to access MySQL user accounts.

 

Installation

In MySQL 5.7, the Connection-Control plugin is not installed by default :

 

The plugin library contains two plugins :

  • CONNECTION_CONTROL checks incoming connections and adds a delay to server responses as necessary.
  • CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS implements an INFORMATION_SCHEMA table that exposes more detailed monitoring information for failed connection attempts.

As usual, you can easily register the plugins at runtime with INSTALL PLUGIN statement :

Alternatively you can modify the configuration file (my.cnf / my.ini) and then restart the server

 

If the plugins have been previously registered with INSTALL PLUGIN or are loaded with plugin-load-add, you can use the connection-control and connection-control-failed-login-attempts options at server startup to control plugin activation.

e.g. to load the plugins at startup and prevent them from being removed at runtime, use these options :

 

Configuration

To enable you to configure its operation, the CONNECTION_CONTROL plugin exposes 3 system variables :

Note : To entirely disable checking for failed connection attempts, set connection_control_failed_connections_threshold to zero.

Default values are :

 

You can modify these variables at runtime with SET GLOBAL :

Indeed they can be made persistent with the configuration file :

 

Let’s test the Connection-Control plugin behavior with a threshold = 2 and a delay = 1 second :

Please focus on the command execution time i.e. real NmN.NNNs

Starting at the 3rd attempts the delay between each connection increase (approximately +1s (= 1000 ms) between each new failed connection attempts).

 

Monitoring

To monitor failed connections, use these information sources:

  • The Connection_control_delay_generated status variable indicates the number of times the server added a delay to its response to a failed connection attempt. This does not count attempts that occur before reaching the threshold defined by the connection_control_failed_connections_threshold system variable.
  • The INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS table provides information about the current number of consecutive failed connection attempts per client user/host combination. This counts all failed attempts, regardless of whether they were delayed.

 

You can easily monitor different user accounts :

 

Connection-Control failure monitoring :

 

Uninstalling Plugins

To remove the plugins, use the UNINSTALL PLUGIN statement :

  • UNINSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
  • UNINSTALL PLUGIN CONNECTION_CONTROL;

 

Note : Update the configuration file (my.cnf / my.ini) if necessary

 

 

In order to go further

MySQL 5.7 Security Series

  1. Password Validation Plugin
  2. Password Management
  3. User Account Locking
  4. The Connection-Control Plugins
  5. Enterprise Audit
  6. Enterprise Transparent Data Encryption (TDE)
  7. Enterprise Firewall

 

Reference Manual

 

MySQL Security

 

Thanks for using MySQL!

Follow me on twitter

4

MySQL Security – User Account Locking

March 14, 2018

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 Transparent Data Encryption aka TDEAudit, Firewall, Password Management, Password Validation Plugin, The Connection-Control Plugins, etc…

MySQL Security

For security reasons some context require you to setup a user account locking policy. Thus an unauthorized user is not able (anymore) to login to the MySQL server. In this 3rd article of the MySQL 5.7 Security series, we will see how to [un]lock a user account.

 

User Account Locking

MySQL supports locking and unlocking user accounts using the ACCOUNT LOCK and ACCOUNT UNLOCK clauses for the CREATE USER and ALTER USER statements:

  • When used with CREATE USER, these clauses specify the initial locking state for a new account. In the absence of either clause, the account is created in an unlocked state.
  • When used with ALTER USER, these clauses specify the new locking state for an existing account. In the absence of either clause, the account locking state remains unchanged.

 

So let’s create a user batchman { not this one 🙂 } with all privileges on sakila database:

 

Account locking state is recorded in the account_locked column of the mysql.user table:

The output from SHOW CREATE USER indicates whether an account is locked or unlocked:

Indeed accounts are unlock by default.

The new user account is allowed to connect to the server and can handle sakila’s objects:

 

Lock account

What if your security policy requires you to lock this account?

ACCOUNT LOCK clause is the answer :

batchman@localhost is now locked!

Any connection attempt with this user will failed :

 

Note.

If the user is still connected you’ll need to kill the connection.

 

MySQL returns an ER_ACCOUNT_HAS_BEEN_LOCKED error.

The server increments the Locked_connects status variable that indicates the number of attempts to connect to a locked account and writes a message to the error log :

 

 

In order to go further

MySQL 5.7 Security Series

  1. Password Validation Plugin
  2. Password Management
  3. User Account Locking
  4. The Connection-Control Plugins
  5. Enterprise Audit
  6. Enterprise Transparent Data Encryption (TDE)
  7. Enterprise Firewall

 

Reference Manual

 

MySQL Security

 

Thanks for using MySQL!

Follow me on twitter

5

MySQL Security – Password Management

March 7, 2018

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 Transparent Data Encryption aka TDEAudit, Firewall, Password Validation PluginUser Account Locking, The Connection-Control Plugins, etc…

MySQL Security

Some regulations required that the password is renewed in a timely and appropriate manner (e.g. every 90 days). In this article, 2nd of the MySQL 5.7 Security series, we will see how to establish a policy for password expiration with MySQL 5.7 Password Management.

 

Password Management

 

Basic password policy practices teaches us :

  • Each user must have a password
  • A user’s password should be changed periodically

MySQL provides password-expiration capability, which enables database administrators to require that users reset their password.

Let’s have a closer look!

 

Basically there are different clauses a DBA can use with CREATE USER or ALTER USER to establish a per account password expiration policy.

Let’s play with some of them.

 

PASSWORD EXPIRE

Force user to change its password at the first connection.

Note that password_expired column is Y.

This new MySQL user will be able to connect to the server but he must reset its password.

 

Column password_expired is now N.

 

 

PASSWORD EXPIRE INTERVAL N DAY

Force user to change its password every N days.

Note that password_lifetime column is 90.

 

After 90 days any statement will generate error 1820 :

 

Password could be reset with ALTER USER command :

 

 

PASSWORD EXPIRE NEVER

It’s also possible to disable password expiration for an account

Note that password_lifetime column is 0.

 

 

PASSWORD EXPIRE DEFAULT

This clause sets the account so that the global password expiration policy applies, as specified by the default_password_lifetime system variable.

In MySQL 5.7 it applies to accounts that use MySQL built-in authentication methods (accounts that use an authentication plugin of mysql_native_password or sha256_password).

 

The default default_password_lifetime value is 0, which disables automatic password expiration. If the value of default_password_lifetime is a positive integer N, it indicates the permitted password lifetime; passwords must be changed every N days.

 

default_password_lifetime can be changed at runtime with SET GLOBAL command. However it must be set in the MySQL configuration file for persistence.

e.g.

e.g. Create a user account with the default global password policy (90 days)

 

 

In order to go further

MySQL 5.7 Security Series

  1. Password Validation Plugin
  2. Password Management
  3. User Account Locking
  4. The Connection-Control Plugins
  5. Enterprise Audit
  6. Enterprise Transparent Data Encryption (TDE)
  7. Enterprise Firewall

 

Reference Manual

 

MySQL Security

 

Thanks for using MySQL!

Follow me on twitter

5

MySQL Security – Password Validation Plugin

March 1, 2018

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 Transparent Data Encryption aka TDEAudit, Firewall, Password Management, User Account Locking, The Connection-Control Plugins, etc…

MySQL Security

In this article, 1st of a MySQL 5.7 Security series, we will see how to enforce Strong Passwords with Password Validation Plugin when using MySQL 5.7.

Authentication with ID and password is a very simple and common (because it’s simple) way to secure the access to a resource, however the password can be the weak point of this system. In order to increase the security level, you can required that your user passwords meet certain minimal security requirements, using the MySQL Password validation plugin!

 

Password Validation Plugin

 

The Password validation plugin serves to test passwords and improve security. It exposes a set of system variables that enable you to define password policy.

For ALTER USER, CREATE USER, GRANT, and SET PASSWORD statements the plugin checks the password against the current password policy and rejects it if it is weak.

 

Examples are made with MySQL CE 5.7.21 on Linux:

 

Installation

Plugins are located in the… plugin directory. To know where is your MySQL plugin directory you can use SHOW VARIABLES :

 

Use the regular INSTALL PLUGIN statement:

INSTALL PLUGIN loads the plugin, and also registers it in the mysql.plugins system table to cause the plugin to be loaded for each subsequent normal server startup.

Alternatively you can modify the MySQL configuration file (e.g. my.cnf or my.ini) and reboot the instance.

e.g.

 

When installed some system and status variables are available:

They are described here.

 

Playtime

Let’s play a little a bit with the Password Validation Plugin.

 

Set Password Validation Plugin to the LOW level

When validate_password_policy is set to LOW (or 0) it checks only the length i.e. validate_password_length >= 8 (by default)

 

Warning

Passwords in the following examples are not secure. Do NOT use trivial passwords!

 

User creation that is not satisfy the policy will failed

 

Set Password Validation Plugin to the MEDIUM level

When validate_password_policy is set to MEDIUM (or 1) it checks

  • the length i.e. validate_password_length >= 8 (by default)
  • numeric
  • lowercase/uppercase
  • special characters

 

 

 

Set Password Validation Plugin to the STRONG level

When validate_password_policy is set to STRONG (or 2) it checks

  • the length i.e. validate_password_length >= 8 (by default)
  • numeric
  • lowercase/uppercase
  • special characters
  • dictionary file

 

 

The main difference with the medium policy is the possibility to use a dictionary file to for checking password against. Set validate_password_dictionary_file variable. By default, this variable has an empty value and dictionary checks are not performed.

 

 

In order to go further

MySQL 5.7 Security Series

  1. Password Validation Plugin
  2. Password Management
  3. User Account Locking
  4. The Connection-Control Plugins
  5. Enterprise Audit
  6. Enterprise Transparent Data Encryption (TDE)
  7. Enterprise Firewall

 

Reference Manual

 

MySQL Security

 

Thanks for using MySQL!

Follow me on twitter

7

Setting up ProxySQL 1.4 with MySQL 5.7 Group Replication

January 9, 2018

Lire cet article en français

There are 3 pillars for a database architecture:

  • Monitoring
  • Backup / Restore process
  • High Availability

This blog post is about database High Availability; more precisely about one of the best combo of the moment :
MySQLProxySQL

 

  • MySQL 5.7 Group Replication : the only native HA solution for MySQL, it’s a Single/Multi-master update everywhere replication plugin for MySQL with built-in automatic distributed recovery, conflict detection and group membership.
  • ProxySQL 1.4 : probably the best proxy for MySQL.

 

Note : MySQL replication is indeed more than a serious alternative for HA. By the way ProxySQL natively handle it.

Note : ProxySQL have a plethora of features, however the purpose of this article is its use in a MySQL Group Replication context..

Note : MySQL NDB Cluster is also a HA solution adapted for the distributed computing environment. This is out of the scope of this article.

 

ProxySQL is compatible with MySQL Group Replication since version 1.3 see (in French) : Configurer ProxySQL pour MySQL Group Replication. However, version 1.4 have a native MySQL Group Replication support. It is therefore easier to use these two popular technologies together.

This is what we will see now.

 

In this article I will make the following assumptions :

 

MySQL Group Replication

Specifications

  • Server version : 5.7.20
  • Plugin version : 1.0
  • Node 1 : mysql_node1, 172.22.0.10 : 3306
  • Node 2 : mysql_node2, 172.22.0.20 : 3306
  • Node 3 : mysql_node3, 172.22.0.30 : 3306

These characteristics therefore represent a 3-nodes MySQL Group Replication cluster, up and running:

MySQL Enterprise Monitor gives us a graphical view of the cluster and its state: (click to enlarge) :

MySQL

 

The cluster is in single primary mode, ie only one node is available for reading & writing at a time (while the other 2 nodes are read-only).

MySQL Enterprise Monitor shows us (click to enlarge):

MySQL

 

I am going to extend the MySQL 5.7 sys schema with the following scriptaddition_to_sys.sql

 

So I load the functions and views into the cluster primary node (mysql_node1) :

 

This script will allow ProxySQL to monitor the state of the cluster nodes.

e.g.

 

On the cluster side, the last configuration step consists of creating the supervision users that will be used by ProxySQL (yes, there is a relationship with the previous step) :).

Here again I use the primary of the group :

 

Let’s configure ProxySQL now !

 

ProxySQL

Specifications

  • Proxy version : 1.4.4
  • Admin Interface : 172.22.0.2:6032
  • Cluster connection : 172.22.0.2:3306

 

 

ProxySQL configuration can be done online, which is obviously a very good thing.

Let’s start by logging into the admin interface on the default port 6032 with the default admin user and password… : admin (!) – Defaults that can and must be changed in real life.

 

Servers configuration

First step, add the cluster nodes to the proxy :

 

Hostgroups configuration

I have done a brief presentation of ProxySQL 1.3 objects last time : Configurer ProxySQL pour MySQL Group Replication

Version 1.4 has some differences, the most notable in our context is the new table : mysql_group_replication_hostgroups :

 

The best description I have found is available in my brilliant colleague’s blog post MySQL Group Replication: native support in ProxySQL

I quote

There are many new columns, let’s have a look at their meaning:

Column Name Description
writer_hostgroup the id of the hostgroup that will contain all the members that are writer
backup_writer_hostgroup if the group is running in multi-primary mode, there are multi writers (read_only=0) but if the amount of these writer is
larger than the max_writers, the extra nodes are located in that backup writer group
reader_hostgroup the id of the hostgroup that will contain all the members in read_only
offline_hostgroup the id of the hostgroup that will contain the host not being online or not being part of the Group
active when enabled, ProxySQL monitors the Group and move the server according in the appropriate hostgroups
max_writers limit the amount of nodes in the writer hostgroup in case of group in multi-primary mode
writer_is_also_reader boolean value, 0 or 1, when enabled, a node in the writer hostgroup will also belongs the the reader hostgroup
max_transactions_behind if the value is greater than 0, it defines how much a node can be lagging in applying the transactions from the Group, see this post for more info

by  @Lefred 🙂

 

Our configuration is as follows :

  • writer_hostgroup = 2
  • backup_writer_hostgroup = 4
  • reader_hostgroup = 3
  • offline_hostgroup = 1
  • active = 1
  • max_writers = 1
  • writer_is_also_reader = 1
  • max_transactions_behind = 0

Which gives us :

 

Supervision configuration

A little further up we created a supervision user in the cluster (remember?).

It is this user that will use ProxySQL to be aware of the state of the various nodes of the cluster :

 

Application user creation

The application connects to the MySQL server with a user and password (in the best case: D).

This user must obviously exist in the different MySQL instances that form the cluster, with the correct MySQL privileges.

This user must also be created in ProxySQL :