30 mins with MySQL JSON functions

July 23, 2018
Tags: , ,

Lire cet article en français

Note: You may also be interested by 30 mins with JSON in MySQL

 

JSON (JavaScript Object Notation) is a popular way for moving data between various systems, including databases.  Starting with 5.7 MySQL supports a native JSON data type (internal binary format for efficiency) and a set of built-in JSON functions that allows you to perform operations on JSON documents.

This blog post is not a complete overview of the entire MySQL JSON functions set (RTFM instead) but rather an arbitrary presentation of some of them.

Note: MySQL 8 enables an alternative way of working with MySQL as a document store. (Not cover in this blog post).

 

I’m using MySQL 8.0.11, downloadable here.

MySQL native JSON data type

JSON Utility Functions

 

JSON_PRETTY

Improve readability with JSON_PRETTY

By default, display a JSON document in MySQL looks like something like this :

You can have a prettier display with JSON_PRETTY :

 

JSON_STORAGE_SIZE

Return the number of bytes used to store the binary representation of a JSON document with JSON_STORAGE_SIZE.

In this collection, the heavier document is 916 bytes, the lighter is 255 and the average size is 537.2814

Note: This is the space used to store the JSON document as it was inserted into the column, prior to any partial updates that may have been performed on it afterwards.

 

Functions That Search JSON Values

 

JSON_EXTRACT (->) / JSON_UNQUOTE / ->> operator

JSON_EXTRACT (or ->) returns data from a JSON document.

JSON_UNQUOTE unquotes JSON value and returns the result as a utf8mb4 string.

->> the JSON unquoting extraction operator is a shortcut for JSON_UNQUOTE(JSON_EXTRACT())

 

Both queries above are similar.

If you want the same result but without quotes use ->> or JSON_UNQUOTE(JSON_EXTRACT()) :

Both queries above are similar.

 

JSON_CONTAINS

Search whether the value of specified key matches a specified value with JSON_CONTAINS.

 

 

JSON_CONTAINS_PATH

Indicate whether a JSON document contains data at a given path or paths with JSON_CONTAINS_PATH.

 

Let’s insert a dummy document in the collection restaurants

How many documents without grades? :

Ok, only 1. We can easily check the structure of this document :

 

A bridge between 2 models

 

To paraphrase David Stokes (MySQL Community Manager) in his book MySQL and JSON – A practical Programming Guide.

The advantages of traditional relational data and schemaless data are both large. But in some cases, data in a schema needs to be schemaless, or schemaless-data needs to be in a schema. 

Making such metamorphosis is very easy to do with MySQL!

 

Relational to JSON

JSON_OBJECT

Evaluates a list of key-value pairs and returns a JSON object containing those pairs with JSON_OBJECT.

A traditional SQL query with a relational result set. The JSON document output non-JSON data :

This result set could be convert in a JSON format, actually a JSON object :

Other example :

 

 

JSON_OBJECTAGG

Takes two column names or expressions and returns a JSON object containing key-value pairs with JSON_OBJECTAGG.

Grouping rows are very often useful. This why we implemented some JSON aggregate functions like this one.

  • Note
    • It’s usually not a good idea to use ORDER BY RAND(). It works like a charm for small dataset, but it’s a true performance killer with huge datasets.
    • The best practice is to do it in the application or pre-compute random value in the database.

 

 

JSON_ARRAY

Evaluate a list of values and returns a JSON array containing those values with JSON_ARRAY.

Next example is an hierarchical query using a recursive  Common Table Expression aka recursive CTE (or WITH Syntax)

JSON format output with JSON_OBJECT & JSON_ARRAY :

 

 

JSON_ARRAYAGG

Aggregate a result set as a single JSON array whose elements consist of the rows with JSON_ARRAYAGG.

With this other JSON aggregate functions we will see different combinations of JSON format output :

 

 

 

 

JSON to Relational

Ok let’s transform JSON data into relational data!

 

JSON_TABLE

Extract data from a JSON document and returns it as a relational table having the specified columns with JSON_TABLE.

Actually I highly recommend you to spend time in the documentation for this powerful function, that allows you to map JSON data into a temporary relational table and then query from this table.

Enough blabla, let’s see some examples :

 

 

 

JSON_TABLE – Nested Data

Walk down the JSON document path and retrieve nested data.

For example, extract all grades for Hawaiian cuisine restaurants :

 

JSON_TABLE – Missing Data

Specify what to do when data is missing.

Default behavior :

 

Enforce the default behavior :

 

Raise an error :

 

Specify a default value :

 

 

Wrapup

I’ll stop here this introduction to this rich MySQL JSON functions world. I presented a subset of these functions but it definitely worth to spend some time to discover the entire set e.g. how to create, modify, indexing, … JSON documents.

Furthermore, if your workload does not fit in the relational model, you should use the MySQL 8 Document Store, that provide a CRUD API and some other cool stuffs. I’ll blog about it soon, so stay tune!

Anyway I’ll recommend you to read : Top 10 reasons for NoSQL with MySQL.

 

 

Misc

Documentation

 

Articles

 

Other resources

  • You’ll find some of the sample databases used in this article here.
  • Restaurants collection could be find here.
  • Some books that could be useful : here.

 

 

Thanks for using MySQL!

 

3

Plenty of new MySQL books

July 12, 2018

In the old days, when we wanted to strengthen our skills the only option was to buy a good book (this one litteraly changed my life, but also this one, this one, …). Nowadays one can find a lot of resources on the Internet, however quality is often poor :(.

Fortunately there are still some great people who are brave enough to write new books that will hopefully have an impact on the new generation of women and men (and the current generation as well) who build modern applications with MySQL, the world’s most popular open source database.

 

Let me introduce you 3 MySQL books :

 

MySQL and JSON: A Practical Programming Guide - Discover how to use JavaScript Object Notation (JSON) with MySQL

MySQL and JSON: A Practical Programming Guide

MySQL and JSON: A Practical Programming Guide
Discover how to use JavaScript Object Notation (JSON) with MySQL

Author : David Stokes   (MySQL Community Manager)

  • ISBN-13 : 978-1260135442
  • ISBN-10 : 1260135446

Publisher : Oracle Press

https://www.mhprofessional.com/9781260135442-usa-mysql-and-json-a-practical-programming-guide-group

 

 


Introducing the MySQL 8 Document Store - Building schemaless database solutions

Introducing the MySQL 8 Document Store

Introducing the MySQL 8 Document Store
Building schemaless database solutions

Author : Dr. Charles Bell (Development Manager)

  • eBook ISBN       : 978-1-4842-2725-1
  • Softcover ISBN : 978-1-4842-2724-4
  • DOI                       : 10.1007/978-1-4842-2725-1

Publisher : Apress

https://www.apress.com/fr/book/9781484227244

 

 


Pro MySQL NDB Cluster - Master the MySQL Cluster Lifecycle

Pro MySQL NDB Cluster

Pro MySQL NDB Cluster
Master the MySQL Cluster Lifecycle

Authors : Jesper Wisborg Krogh(MySQL Senior Principal Technical Support Engineer), Mikiya Okuno(MySQL Technical Analyst)

  • eBook ISBN       : 978-1-4842-2982-8
  • Softcover ISBN : 978-1-4842-2981-1
  • DOI                       : 10.1007/978-1-4842-2982-8

Publisher : Apress

https://www.apress.com/gp/book/9781484229811

 

 

 

I’ll finish this post with a Groucho Marx quotes :

“Outside of a dog, a book is man’s best friend. Inside of a dog it’s too dark to read.”

MySQL Books

 

 

Updated 2018/08/06

I also recommend my colleague Jesper‘s blog post: MySQL Books.

 

 

Thanks for using (and reading) MySQL!

 

1

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 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 :

 

Updated on 22nd of August 2018
Note
: MySQL Enterprise Firewall works with MySQL 8.0 as well. In other words examples below could be done with MySQL 8.0.12+

 

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

 

2

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 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.

 

Updated on 22nd of August 2018
Note
: MySQL Enterprise Transparent Data Encryption works with MySQL 8.0 as well. In other words examples below could be done with MySQL 8.0.12+

 

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  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 :

 

Updated on 22nd of August 2018
Note
: MySQL Enterprise Audit works with MySQL 8.0 as well. In other words examples below could be done with MySQL 8.0.12+

 

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.

 

Updated on 22nd of August 2018
For convenience, a JSON_PRETTY example with MySQL 8.0.12:

 

 

 

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.

And now Olivier is in big troubles!!! 🙂

 

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

6

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.