I’ve written previously about use cases where having accounts which cannot be used to establish client connections are useful. There are various hacks to accomplish this with legacy versions (insert invalid password hash into mysql.user table, etc.), and we introduced the mysql_no_login authentication plugin for this very purpose. Now as of MySQL 5.7.6, account locking gets native support through the ACCOUNT LOCK clause of CREATE USER and ALTER USER commands. This post revisits the use cases which drove this feature and the implementation details.

Use Cases

Security …

As noted in an earlier post, MySQL Server 5.7 prefers and enables SSL/TLS connections by default.  That’s great and useful progress towards secure connections, but we know that not all SSL/TLS ciphers are created equal – some are older and more vulnerable.  Furthermore, some recent vulnerabilities rely on the ability to negotiate less-secure ciphers during the handshake.  Monitoring which ciphers are used can help identify connections using low-grade ciphers, but also to build an appropriate restricted cipher list.  Using improvements to PERFORMANCE_SCHEMA introduced in 5.7, you can now easily do this – and this post will show you how.

The cipher used for each TLS connection is stored in a …

Complimenting the expanded CREATE USER syntax introduced in MySQL Server 5.7.6 is more useful ALTER USER syntax.  Before MySQL Server 5.7.6, ALTER USER could only be used to expire a user’s password.  That’s pretty limited.  With changes made in MySQL Server 5.7.6, a better distinction is made between privilege-level attributes (those which are managed via GRANT and REVOKE statements) and account-level attributes (those managed using CREATE USER and ALTER USER statements).  MySQL has a long history of confusing these – for example, requiring a GRANT …

As I wrote earlier, we want the default experience in MySQL 5.7 to be secure by default.  Part of this includes securing connections by automatically creating key material and using TLS for connections where possible.  This may have some significant implications for third-party software – especially products which depend upon capturing, evaluating and/or redirecting client/server traffic at the network level.  This blog post is intended to highlight for developers and users of such products potential issues they may want to consider or address during the pre-GA period for MySQL Server 5.7.

What types of products are dependent upon access to unencrypted protocol data?  Most immediately apparent are proxy-based and network capture-based products.  Proxy-based products typically rely on the same characteristics which can …

MySQL 5.7 aims to be the most secure MySQL Server release ever, and that means some significant changes in SSL/TLS.  This post aims to tie together individual enhancements introduced over the span of several Development Milestone Releases (DMRs) into the larger initiative.  In the simplest terms, we hope to have a minimal TLS configuration enabled by default, and for connections to prefer TLS by default.  Let’s dig into the various aspects of this:

Generation of TLS key material

MySQL Server has long supported TLS connections, yet very few deployments are actually configured to leverage this.  This is partly because creation of key material – the certificates and keys needed to establish TLS connections – is a multi-step, extra, manual process.  Basic TLS concepts have to be understood, third-party software …

When storing XML documents in a BLOB or TEXT column there was no way to create indexes on individual XML elements or attributes. With the new auto generated columns in MySQL 5.7 (1st Release Candidate available now!) this has changed! Let me give you an example. Let's work on the following table:

 mysql> SELECT * FROM country\G  
 *************************** 1. row ***************************  
 docid: 1  
  doc: <country>  
     <name>Germany</name>  
     <population>82164700</population>  
     <surface>357022.00</surface>  
     <city name="Berlin"><population></population></city>  
     <city …

Prior to MySQL 5.7, the CREATE USER command had a number of limitations:

• No way to set both authentication plugin and password
• No way to disable a user
• No way to define user resource limitations
• No way to set a non-default password expiration policy
• No way to require SSL/x509

All of these things could be done through other means, but typically involved other statements, such as GRANT commands.  Starting with MySQL 5.7.6, these can all be done through a new and improved CREATE USER syntax:

Passwords and authentication plugin

The most important aspect to me, from a security perspective, is the ability to now create user accounts with non-default authentication plugins (like sha256_password) and a non-blank password:

mysql> CREATE USER new@localhost
-> IDENTIFIED WITH sha256_password …

MySQL has provided support for proxy users since version 5.5, but the roles-like capabilities offered have been largely unnoticed until recently.  Part of that has been due to limitations on which types of accounts could leverage proxy user capabilities.  This changes with the release of MySQL Server 5.7.7 (Release Candidate), which includes support for proxy user mapping for the standard mysql_native_password and sha256_password authentication plugins.  This post will introduce the new functionality and explain how to leverage it to emulate certain features …

Two years ago Ovais Tariq had explained in detail what kinds of problems existed before MySQL introduced metadata locks in 5.5.3 and how these locks help to prevent them. Still, some implications of metadata locking in MySQL remain unclear for users – DBAs and even software developers that target recent MySQL versions. I’ve decided to include a slide or two into the presentation about InnoDB locks and deadlocks I plan to make (with my colleague Nilnandan Joshi) on April 16 at Percona Live 2015.

I decided to do this as …

With the changes to performance_schema in MySQL 5.7 Development Milestone Release it is now possible to analyze and profile the execution of stored programs. This is highly useful if you develop more complex stored procedures and try to find the bottlenecks. The "old" performance_schema up to MySQL 5.6 only reported a CALL statement with a runtime, but no information on statements that were executed WITHIN the stored procedure. Now let's try this in the latest MySQL 5.7.6 DMR release. After creating some test table and a test stored procedure we need to activate the events_statements_history_long consumer, which is OFF by default:

mysql> UPDATE setup_consumers SET ENABLED="YES" 
           WHERE NAME = "events_statements_history_long"; 

Then let's call the stored procedure that we want to inspect:

mysql> CALL …