Ulf Wendel

MySQL 5.6: SHA256 secure password support for PHP mysqlnd

| 0 comments

Pluggable Authentication is one of the many new MySQL 5.6 features. Pluggable authentication adds a capability to use external authentication services (PAM, Windows login IDs, LDAP, Kerberos, …) but also introduces built-in strong SHA-256 hashing for passwords. The SHA-256 Authentication Plugin uses encryption to protect the password from being sniffed during authentication. Read a live report from teaching the PHP mysqlnd library the new secure authentication method.

Your choices

Early MySQL versions have had very poor password hashing. The PHP mysqlnd library has never supported the old, insecure password hashing that was used until MySQL 4.1. With MySQL 4.1 things got better. Since then MySQL has supported two password hashing algorithms, which is why there are the two MySQL SQL functions OLD_PASSWORD() and PASSWORD(). Oracle demanded MySQL 5.6 to offer even higher levels of security and thus there are even more choices. Those are relevant to you as a PHP developer:

Authentication Plugin Description mysqlnd support
mysql_old_password Insecure pre-MySQL 4.1 authentication no, insecure!
mysql_native_password MySQL 4.1+ default PHP 5.3+
mysql_clear_password MySQL 5.5.10+ optional, no hashing or encryption, use only with secure connection and external authentication service PHP 5.4+
sha256_password MySQL 5.6.6+ optional PHP 5.5+

The MySQL 5.6 SHA-256 Authentication Plugin ensures that passwords are never exposed as cleartext when connecting to the server. To protect the password either SSL connections or RSA encryption are used. If neither method is available, the connection attempt fails. This makes SHA-256 a very secure albeit not the fastest method to authenticate a MySQL user.

Authentication method is set per user

Every MySQL user account can be configured to use a certain authentication method. You can query the MySQL user table to see authentication method is used for which account.

 mysql> SELECT Host, User, plugin from mysql.user WHERE plugin != ""; +-----------+-------+-----------------------+ | Host | User | plugin | +-----------+-------+-----------------------+ | localhost | sha | sha256_password | | localhost | shapa | sha256_password | | localhost | ulf | mysql_native_password | +-----------+-------+-----------------------+ 

Use the IDENTIFIED WITH auth_plugin clause of the the CREATE USER SQL statement to set the authentication plugin for a new user. For example, the above shown user “shapa” has been created using CREATE USER 'shapa'@'localhost' IDENTIFIED WITH sha256_password followed by SET old_passwords=2 (odd name, right?) and SET PASSWORD FOR 'shapa'@'localhost' = PASSWORD('shapa'). Please, check the MySQL manual for glory details skipped here for brevity.

If you try to connect to MySQL using the example ‘shapa’ user but your PHP clients lacks support for SHA-256 you should get a warning similar to the following. However, before we can look into the client (driver) part, we need to master the server part.

 

Warning: mysqli_connect(): The server requested authentication 
method unknown to the client [sha256_password] in Command line code on line 1

Warning: mysqli_connect(): (HY000/2054): The server requested authentication
method unknown to the client in Command line code on line 1

 

Checking the server for SHA_256 support

You can query the information schema PLUGINS table for a list of authentication plugins supported by your server.

 

mysql> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 5.6.8-debug-log |
+-----------------+

mysql> SELECT * FROM INFORMATION_SCHEMA.PLUGINS 
         WHERE PLUGIN_TYPE="AUTHENTICATION"\G
[...]
*************************** 3. row ***************************
           PLUGIN_NAME: sha256_password
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: AUTHENTICATION
   PLUGIN_TYPE_VERSION: 1.0
        PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
         PLUGIN_AUTHOR: Oracle
    PLUGIN_DESCRIPTION: SHA256 password authentication
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: FORCE

 

Server: no proper way to detect SHA-256 flavor/features

Unfortunately the server behaves differently depending on how it was compile. By default MySQL 5.6 is built using the yaSSL. Alternatively if can be built using OpenSSL.

The servers choice of the SSL library determines its features. The SHA-256 never sends a cleartext password. It either uses SSL or RSA encryption to protect the password. If yaSSL is used, the MySQL 5.6 default, RSA encryption is not available. In this case a SHA-256 based can only be established if SSL is used.

Server SHA-256 features

Library SSL RSA
yaSSL (server default) yes no
OpenSSL yes yes

A server compiled using OpenSSL can either use an SSL connection or RSA encryption. OpenSSL is the more powerful solution. If you plan to use RSA over SSL you may want to know in advance how MySQL was built and what flavor of SHA-256 authentication it supports.

Unfortunately there is no proper way to tell how the server was compiled. As a user you cannot use the server status variable HAVE_OPENSSL to determine the library used. HAVE_OPENSSL is an alias for HAVE_SSL, which is a generic flag to indicate any kind of SSL support by MySQL…. Below is a hack using nm to inspect a MySQL server binary.

 

> nm /usr/local/mysql/bin/mysqld | grep -i openssl | wc -l
9
> nm /usr/local/mysql/bin/mysqld | grep -i yassl | wc -l
0

 

In the worst case you end up with a client attempting to use RSA encryption, a server not capable of it and an error message that fails to hint you what is wrong.

PHP mysqlnd is using OpenSSL

The PHP mysqlnd library is using OpenSSL for its client-side SHA-256 authentication support. Thus, it always includes support for both SSL and RSA password protection, given that the server supports it as well (see above). SHA-256 support has been introduced to PHP mysqlnd in PHP 5.5.0-dev. You can check the output of phpinfo() to verify the list of built-in mysqlnd authentication plugins. Note, that you have to compile PHP with OpenSSL support (--with-openssl) to see auth_plugin_sha256_password listed and supported.

 

> ./buildconf --force
> ./configure --with-mysql=mysqlnd \
              --with-mysqli=mysqlnd \
              --with-pdo-mysql=mysqlnd  \
              --with-openssl \
              [...]
> make clean && make -j2
> sapi/cli/php -i | grep mysqlnd
[...]
mysqlnd => enabled
Version => mysqlnd 5.0.11-dev - 20120503 - $Id: b0db48f5f71b45623b2b6aed210dd5b2464d06c4 $
Loaded plugins => mysqlnd,example,debug_trace,auth_plugin_mysql_native_password,auth_plugin_mysql_clear_password,auth_plugin_sha256_password
[...]

 

PHP mysqlnd is now able to connect to MySQL using the SHA-256 authentication method:

 

> sapi/cli/php -r '$link = new mysqli("localhost", "shapa", "shapa", "test", 3313, "/tmp/mysql566.sock"); var_dump($link->server_info);'
string(15) "5.6.8-debug-log"

 

Please note, that this requires a properly configured server. If, for example, you have missed to setup the RSA key on the server, you may get error messages like the following:

> sapi/cli/php -d mysqlnd.debug="d:t:O,/tmp/mysqlnd.trace" -r '$link = new mysqli("localhost", "shapa", "shapa", "test", 3313, "/tmp/mysql566.sock"); var_dump($link->server_info);'

Warning: mysqli::mysqli(): (HY000/2006): MySQL server has gone away in Command line code on line 1

Warning: Unknown: Couldn't fetch mysqli in Command line code on line 1
NULL

> /usr/local/mysql/bin/mysql -ushapa -pshapa  -S/tmp/mysql566.sock test
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'shapa'@'localhost' (using password: YES)

 

Neither the error message returned by PHP nor the one of the mysql prompt is very informative. To debug the situation you should check the server configuration for suspicious configuration settings. As said, in this case, it is the lack of a public key that makes the connection attempt fail.

mysql> SHOW STATUS LIKE '%rsa%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Rsa_public_key |       |
+----------------+-------+
mysql> SHOW VARIABLES LIKE "%sha%";
+----------------------------------+-----------------+
| Variable_name                    | Value           |
+----------------------------------+-----------------+
| sha256_password_private_key_path | private_key.pem |
| sha256_password_public_key_path  | public_key.pem  |
+----------------------------------+-----------------+

 

Happy hacking!

@Ulf_Wendel Follow me on Twitter

Leave a Reply

Required fields are marked *.