MySQL Bugs: #77595: 'Access Denied' for sha256

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

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

test case with logs

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

Posted by developer:
 
SSL connections from mysql command line client works properly for users created with sha256_password

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

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?

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

Posted by developer:
 
This has been fixed upstream in PHP since at least PHP 7.1.15 and 7.2.3, probably sooner.

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