I've compiled OpenSSL 1.0.2h into /usr/local/openssl on a MySQL 5.7.13 Server, using the following code:
After, I configured MySQL with:
When MySQL starts for the first time, it creates eight key/cert (.pem) files within that /dat directory:
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:
The "status" of the MySQL Server is as follows:
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 else remains the same.
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).