Explanation of Mysql SSL connection for users created with "REQUIRE SSL"

Question

I'm trying to understand the working of SSL while trying to connect to a MySQL server (via mysql-connector-python). I have MySQL (v5.7) and I have 2 users. One (root) is the default user and the other user (ssl_user) has REQUIRE SSL option enabled. If I try to connect to ssl_user with the following config, I get an error saying ssl_user does not have access.

mysql_conf = {
    'host': '127.0.0.1',
    'port': '3306',
    'user': 'root',
    'password': '',
    'ssl_disabled': True #Default value is False    
}
conn = mysql.connector.connect(**mysql_conf)

I'm able to connect to the MySQL if I change the value as 'ssl_disabled': False for the particular user.

I'm connecting to the mysql server via my flask app, What exactly does happen while connecting via this user created with Require ssl? does it mean the connection for this user is encrypted? if so, where does the encryption happen?

I'm not able to understand what exactly is the difference between connection via 'ssl_disabled': False and connecting by providing SSL certificates?

Answer 1

The Python connector prefers to use SSL by default, or if you set ssl_disabled=False.

That's unfortunately a double-negative. It means you did not disable SSL, therefore it is enabled. It would have been more clear if they had followed the convention of other MySQL clients that use ssl-mode, set to one of 'DISABLED', 'PREFERRED', 'REQUIRED', 'VERIFY_CA', 'VERIFY_IDENTITY'.

MySQL users by default may connect via TCP with an encrypted SSL connection or a non-encrypted connection. By defining your user with REQUIRE SSL, the user cannot connect if SSL is not enabled.

Encryption happens in the MySQL client library, which the Python connector calls. That's where the MySQL protocol is implemented. Many other clients use the same MySQL client library, such as Ruby, PHP, Perl, or C++ clients.

I can test a user similar to yours with the MySQL command-line client, which also uses the same client library.

I created my user:

mysql> create user testy@'%' require ssl;

Then in another window I tried connecting from a shell prompt:

% mysql -utesty -h 127.0.0.1 -p --ssl-mode DISABLED
Enter password: 
ERROR 1045 (28000): Access denied for user 'testy'@'localhost' (using password: NO)

That's as expected. If the user is defined to require SSL, and the client tries to connect with SSL disabled, then that user cannot connect.

% mysql -utesty -h 127.0.0.1 -p --ssl-mode PREFERRED
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql> status
...
SSL:            Cipher in use is TLS_AES_128_GCM_SHA256

The status reports that the current session is using SSL, by naming the cipher in use. If I weren't connected via SSL, it would say "Not in use" for the SSL cipher.

Certificates are optional. Certificates are for secure authentication. You can use an SSL connection to encrypt the packets, without using certificates. There are separate options in the MySQL CREATE USER statement for requiring a user be authenticated with a certificate, but most people don't bother with that. I've worked at a company where we used certificates instead of passwords for MySQL users. This was very secure, but it required that the company have its own certificate authority (CA) to create a unique certificate for each MySQL user (they used certificates for lots of things within the company, not just MySQL).