Bug #77595 'Access Denied' for sha256_password with SSL
Submitted: 2 Jul 2015 9:25 Modified: 3 May 2018 12:14
Reporter: Andrii Nikitin Email Updates:
Status: Closed Impact on me:
None 
Category:Connectors: mysqlnd ( PHP ) Severity:S2 (Serious)
Version:5.0.11-dev OS:Any
Assigned to: CPU Architecture:Any

[2 Jul 2015 9:25] Andrii Nikitin
Description:
Users created with sha256_password plugin are not able to connect from PHP trough SSL.

Without SSL works properly
SSL with native_password works properly

How to repeat:
1. Create users
create user 'b'@'localhost' identified by '1';
grant all on test.* to 'b'@'localhost';

create user 'a'@'localhost' identified with sha256_password;
grant all on test.* to 'a'@'localhost';

set old_passwords=2;

set password for 'a'@'localhost' = password('1');

flush privileges;

2. Run attached script c.php , which tries both SSL and no-SSL connections for the a and b users created above. Observe in output :

Would connect with:b@127.0.0.1 Pass:1  TO: test : 0
"Ssl_cipher" ""

Would connect with:b@127.0.0.1 Pass:1  TO: test : 2048
"Ssl_cipher" "DHE-RSA-AES256-SHA"

Would connect with:a@127.0.0.1 Pass:1  TO: test : 0
"Ssl_cipher" ""

Would connect with:a@127.0.0.1 Pass:1  TO: test : 2048
Warning: mysqli::real_connect(): (HY000/1045): Access denied for user 'a'@'localhost' (using password: YES) in /home/a/sandboxes/msb_5_6_25/c.php on line 30
Could not connect to server    1045: Access denied for user 'a'@'localhost' (using password: YES) 

Suggested fix:
Let SSL connections work for users created with sha256_password
[2 Jul 2015 9:31] Andrii Nikitin
test case with logs

Attachment: c.zip (application/x-zip-compressed, text), 22.15 KiB.

[2 Jul 2015 12:46] Andrii Nikitin
Posted by developer:
 
SSL connections from mysql command line client works properly for users created with sha256_password
[25 Feb 2016 15:17] Andrey Hristov
Hi,
which PHP version and which MySQL version did you use. Here is my try with PHP 5.6 and 7.0 and MySQL 5.7

Server version: 5.7.12-debug Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create user 'b'@'localhost' identified by '1';
Query OK, 0 rows affected (0,00 sec)

mysql> grant all on test.* to 'b'@'localhost';
Query OK, 0 rows affected (0,00 sec)

mysql> 
mysql> create user 'a'@'localhost' identified with sha256_password;
Query OK, 0 rows affected (0,00 sec)

mysql> grant all on test.* to 'a'@'localhost';
Query OK, 0 rows affected (0,00 sec)

mysql> 
mysql> set old_passwords=2;
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> 
mysql> set password for 'a'@'localhost' = password('1');
Query OK, 0 rows affected, 1 warning (0,01 sec)

mysql> 
mysql> flush privileges;
Query OK, 0 rows affected (0,00 sec)

-----
andrey@poohie:/work/dev/php/php-5.6/tmp$ ../php -v
PHP 5.6.16-dev (cli) (built: Feb 25 2016 15:48:21) 
Copyright (c) 1997-2016 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2016 Zend Technologies
andrey@poohie:/work/dev/php/php-5.6/tmp$ ../php c.php
Would connect with:b@127.0.0.1 Pass:1  TO: test : 0

Warning: mysqli::real_connect(): (HY000/2002): Connection refused in /work/dev/php/php-5.6/tmp/c.php on line 30
Could not connect to server    2002: Connection refused 
Setting SSL-parameters, CT_KEY:/home/a/sandboxes/msb_5_6_25/certs/client-key.pem, CT_Cert:/home/a/sandboxes/msb_5_6_25/certs/client-cert.pem, DB_CA_cert:/home/a/sandboxes/msb_5_6_25/certs/ca-cert.pem
Would connect with:b@127.0.0.1 Pass:1  TO: test : 2048

Warning: mysqli::real_connect(): (HY000/2002): Connection refused in /work/dev/php/php-5.6/tmp/c.php on line 30
Could not connect to server    2002: Connection refused 
Would connect with:a@127.0.0.1 Pass:1  TO: test : 0

Warning: mysqli::real_connect(): (HY000/2002): Connection refused in /work/dev/php/php-5.6/tmp/c.php on line 30
Could not connect to server    2002: Connection refused 
Setting SSL-parameters, CT_KEY:/home/a/sandboxes/msb_5_6_25/certs/client-key.pem, CT_Cert:/home/a/sandboxes/msb_5_6_25/certs/client-cert.pem, DB_CA_cert:/home/a/sandboxes/msb_5_6_25/certs/ca-cert.pem
Would connect with:a@127.0.0.1 Pass:1  TO: test : 2048

Warning: mysqli::real_connect(): (HY000/2002): Connection refused in /work/dev/php/php-5.6/tmp/c.php on line 30
Could not connect to server    2002: Connection refused 

-------
andrey@poohie:/work/dev/php/php-5.6/tmp$ ../../php-7.0/php -v
PHP 7.0.3-dev (cli) (built: Jan 13 2016 16:03:38) ( NTS DEBUG )
Copyright (c) 1997-2016 The PHP Group
Zend Engine v3.0.0, Copyright (c) 1998-2016 Zend Technologies
andrey@poohie:/work/dev/php/php-5.6/tmp$ ../../php-7.0/php c.php
Would connect with:b@127.0.0.1 Pass:1  TO: test : 0

Warning: mysqli::real_connect(): (HY000/2002): Connection refused in /work/dev/php/php-5.6/tmp/c.php on line 30
Could not connect to server    2002: Connection refused 
Setting SSL-parameters, CT_KEY:/home/a/sandboxes/msb_5_6_25/certs/client-key.pem, CT_Cert:/home/a/sandboxes/msb_5_6_25/certs/client-cert.pem, DB_CA_cert:/home/a/sandboxes/msb_5_6_25/certs/ca-cert.pem
Would connect with:b@127.0.0.1 Pass:1  TO: test : 2048

Warning: mysqli::real_connect(): (HY000/2002): Connection refused in /work/dev/php/php-5.6/tmp/c.php on line 30
Could not connect to server    2002: Connection refused 
Would connect with:a@127.0.0.1 Pass:1  TO: test : 0

Warning: mysqli::real_connect(): (HY000/2002): Connection refused in /work/dev/php/php-5.6/tmp/c.php on line 30
Could not connect to server    2002: Connection refused 
Setting SSL-parameters, CT_KEY:/home/a/sandboxes/msb_5_6_25/certs/client-key.pem, CT_Cert:/home/a/sandboxes/msb_5_6_25/certs/client-cert.pem, DB_CA_cert:/home/a/sandboxes/msb_5_6_25/certs/ca-cert.pem
Would connect with:a@127.0.0.1 Pass:1  TO: test : 2048

Warning: mysqli::real_connect(): (HY000/2002): Connection refused in /work/dev/php/php-5.6/tmp/c.php on line 30
Could not connect to server    2002: Connection refused
[25 Feb 2016 15:36] Andrii Nikitin
The logs in original c.zip indicate :
MySQL 5.6.25
PHP   5.5.26
Maybe the certificates expired? Could you try with some fresh certificates and confirm whether simple SSL works?
[18 Jul 2016 12:56] Steven Bennett
This still does not work. There is definitely an issue connecting to a SHA256 Password user from PHP using [mysqli].

I've compiled OpenSSL 1.0.2h into /usr/local/openssl on a MySQL 5.7.13 Server, using the following code:

    > tar xvzf openssl-1.0.2h.tar.gz -C /usr/local
    > #cd /usr/local/openssl-1.0.2h
    > ./config --prefix=/usr/local/openssl shared
    > make depend
    > make
    > make install

After, I configured MySQL with:

    > cmake . -DMYSQL_DATADIR=/usr/local/mysql/dat -DWITH-SSL=/usr/local/openssl

When MySQL starts for the first time, it creates eight key/cert (.pem) files within that /dat directory:

    • 1.ca.pem
    • ca-key.pem
    • client-cert.pem
    • client-key.pem
    • private_key.pem
    • public_key.pem
    • server-cert.pem
    • server-key.pem

From within MySQL Workbench, I can see that SSL is on in the Available Server Features:

    • SSL Availability On (Green Dot)

And that the SHA256/SSL files are identified in the Authentication section:

    • SHA256 password private key: private_key.pem
    • SHA256 password public key: public_key.pem
    • SSL CA: ca.pem
    • SSL CA path: n/a
    • SSL Cert: server-cert.pem
    • SSL CRL: n/a
    • SSL CRL path: n/a
    • SSL Key: server-key.pem

From the Users and Privileges section in Workbench I have created two test users:

    'user_standard'@'%' picking Standard for Authentication Type
    'user_sha256'@'%' picking SHA256 Password for Authentication Type.

In my my.cnf file I specify the following:

    [mysqld]
    ssl-ca = /usr/local/mysql/dat/ca.pem
    ssl-cert = /usr/local/mysql/dat/client-cert.pem
    ssl-key = /usr/local/mysql/dat/client-key.pem
    ssl-cipher = DHE-RSA-AES256-SHA

The "status" of the MySQL Server is as follows:

    mysql> status
    --------------
    ./bin/mysql  Ver 14.14 Distrib 5.7.13, for Linux (x86_64) using   EditLine wrapper

    Connection id:           4
    Current database:        mysql
    SSL:                     Cipher in use is DHE-RSA-AES256-SHA
    Current page:            stdout
    Using outfile:           ''
    Using delimiter:         ;
    Server version:          5.7.13-debug-log Source distribution
    Protocol version:        10
    Connection:              Localhost via UNIX socket
    Server characterset:     utf8
    Db     characterset:     utf8
    Client characterset:     utf8
    Conn.  characterset:     utf8
    UNIX socket:             /tmp/mysqld.sock

I have a separate client server running Apache 2.4.10 (configured with --with-ssl=/usr/local/openssl), PHP 7.0.7 (configured with --with-openssl=/usr/local/openssl), and the same OpenSSL 1.0.2h (configured with --prefix=/usr/local/openssl shared). My phpinfo.php file shows OpenSSL support is enabled and that its Library/Header Version is indeed 1.0.2h.

I then copied the ca.pem, client-cert.pem, and client-key.pem files from the MySQL Server to the Apache/PHP Server and made a directory to put them in: /usr/local/openssl/ssl/mysql/.

Here is my PHP code that is a "Success!" using the credentials for [user_standard] but I get,

    Warning: mysqli_real_connect(): (HY000/1045): Access denied for user 'user_sha256' (using password: YES)

simply switching the $user and $pass variables to log in as [user_sha256]. Everything remains the same.

    $conn = mysqli_init();
    $conn->options (MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
    $conn->ssl_set ('/usr/local/openssl/ssl/mysql/client-key.pem', '/usr/local/openssl/ssl/mysql/client-cert.pem', '/usr/local/openssl/ssl/mysql/ca.pem', NULL, 'DHE-RSA-AES256-SHA');

    $serv = 'MySQL_Server_5.7.13_Auto_Generated_Server_Certificate';
    $user = 'user_standard';  // Switching to 'user_sha256' = Permission denied
    $pass = 'user_standard';  // Switching to 'user_sha256' = Permission denied
    $data = 'test';

    $conn->real_connect ($serv, $user, $pass, $data, 3306, NULL, MYSQLI_CLIENT_SSL);

    if (!$conn) {
        die ('Connect error (' . mysqli_connect_errno(). '): ' . mysqli_connect_error() . "\n");
    } else {
        echo "Success!";
    }

    $conn->close();

I don't understand the difference and/or what possibly needs to be configured differently to allow PHP to connect to the SHA256 account.

And by the way, both the MySQL Server and the Apache/PHP Server are: CentOS Linux release 7.2.1511 (Core).
[3 May 2018 12:14] Johannes Schlüter
Posted by developer:
 
This has been fixed upstream in PHP since at least PHP 7.1.15 and 7.2.3, probably sooner.
[15 Jan 19:25] Nicholas Williams
Johannes, et. al:

This is still broken in MySQL 5.7.24, PHP 7.2.10, and OpenSSL 1.1.0g. I know that Johannes posted early last year that this had been resolved "in PHP since at least PHP 7.1.15 and 7.2.3," but this does not appear to be true. I can post significant more details here (or just go see my post at https://stackoverflow.com/questions/54177511/how-do-you-connect-to-mysql-using-phps-mysqli...), or, if preferred, I can file a new bug. Which would you prefer? Update here, or new bug?

Thanks,

Nick Williams