mysql-clear-password,mysql_clear_password & authentication_ldap_simple password encryption

Preparing for my talk at the HK Open Source Conference

I wanted to confirm some of the things I had read about these plugins, mostly from Matthias Crauwels excellent article on the subject

.

My lab environment consists of Windows 2016 Domain controller and client machines, with MySQL 8 running on Oracle Linux 7.

First I configure the database to use the server-side plugin, configure the plugin to point to the domain controller, and create a database user associated via the plugin with my Windows account.

mysql> INSTALL PLUGIN authentication_ldap_simple SONAME 'authentication_ldap_simple.so';

Query OK, 0 rows affected (0.05 sec)

mysql> SET GLOBAL authentication_ldap_simple_server_host='win-dc.windows.domain';

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'patrick'@'%'

-> IDENTIFIED WITH authentication_ldap_simple

-> BY 'CN=patrick,CN=Users,DC=WINDOWS,DC=DOMAIN';

Query OK, 0 rows affected (0.08 sec)

Next I successfully connect from my Windows client to this database account passing in my Windows credentials.

[patrick@WIN-CLIENT] C:> mysql --host=lnx-mysql8.windows.domain `

>> --user=patrick `

>> --password=Password123 `

>> --enable-cleartext-plugin

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 63

Server version: 8.0.13-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2018, 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>

Checking the network packets between the Windows client and database server, observe that (almost all) the traffic is encrypted with a self-signed certificate.

[root@lnx-mysql8 ~]# tcpflow -c port 3306

tcpflow: listening on enp0s3

192.168.002.004.03306-192.168.002.002.49801: U

192.168.002.002.49801-192.168.002.004.03306:

192.168.002.002.49801-192.168.002.004.03306: ANi

239-.<>%=h&/05612?)i*17

U

0H1212013500Z0@1>0

DHn)i&gjV:/,J^f("qBDH8kW:lKQ+B 3;K^!.$5BxJ=0XWD,00U00

^uBey@/e;m4sQ

lJDiU{?s;[72FeLkS

p{WFXdr**yLPp'ij_(z`E"{Lxu|1DX$Jp`w;Ti

0H1212013500Z0<1:08U1MySQL_Server_8.0.13_Auto_Generated_CA_Certificate0"0 2 |s?jupq&GG]5`-2$1'$,AT"`OA/^d((~:n0Z'~O?$+az]y(De"5Klwiv(B"ST~rE0'7qIWZc%R$D8v9MzF|blAK00U00 9()~I?-Nq(#LRACVU>eOWB["IOm$]fvNa7Wm?

dYt^dT)-Y&UWUBlnmOA}?%YW>D

C*

'#-F*/=@=]mnDRVQ,RG(a|la^!fH)5"{EbynK4{q:CiV%#(f_hr_/-X~S/:

(@C{jB&{%ddU-F0fG/2t_aPUwA%&+:{K"t(+}Q~+|#XxJNSXhDz);=79.o{Qb+xF[Lv6;

192.168.002.004.03306-192.168.002.002.49801: ,

{xsGdN>

XEUyH`?T'd7fI JwN%:eq2#;y'Nh(hm}c$dG'zs

zT@(W$]#Wm4nw2t7(`X-5lK'SXwk0qS3

192.168.002.002.49801-192.168.002.004.03306: =@,Wa{q%)}aH9;.%~k$hoKI+a8B}@NR@Dp`JFDwK(1%9 %5XqO f:Pgmvi|>N^&=k/~egl]i@s;p

8&?4AKg>r63E

192.168.002.004.03306-192.168.002.002.49801: H`XmX2:@~Oq)BY-|G~L6

192.168.002.004.03306-192.168.002.002.49801: #`X-$~v4_L3

|s

192.168.002.002.49801-192.168.002.004.03306: ==@,W.BFu?6'F6|P)EC]?%n)ww

nSHK*+@6FS(9l|Y2>apy;-192.168.002.002.49801: |`X,

Note however it is possible to disable this encryption with the –ssl-mode=disabled flag.

[patrick@WIN-CLIENT] C:> mysql --host=lnx-mysql8.windows.domain `

>> --user=patrick `

>> --password=Password123 `

>> --enable-cleartext-plugin `

>> --ssl-mode=disabled

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 64

Server version: 8.0.13-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2018, 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>

Observe that the password is now transmitted unencrypted during authentication

[root@lnx-mysql8 ~]# tcpflow -c port 3306

tcpflow: listening on enp0s3

192.168.002.004.03306-192.168.002.002.49899: U

8.0.13-commercialAi95M2)P3(f}3caching_sha2_password

192.168.002.002.49899-192.168.002.004.03306: patrick lv

J5T{W0J-rcaching_sha2_passwordq_pid172program_namemysql_client_namelibmysql_thread2640_client_version8.0.13_osWin64_platformx86_64

192.168.002.004.03306-192.168.002.002.49899: mysql_clear_password

192.168.002.002.49899-192.168.002.004.03306: Password123

192.168.002.004.03306-192.168.002.002.49899:

192.168.002.002.49899-192.168.002.004.03306: !select @@version_comment limit 1

192.168.002.004.03306-192.168.002.002.49899: 'def@@version_comment$%$MySQL Enterprise Server - Commercial

Such connections can be prevented with the require_secure_transport=ON variable

mysql> SET GLOBAL require_secure_transport=ON;

Query OK, 0 rows affected (0.00 sec)

In this case connections which disable encryption will be rejected

[patrick@WIN-CLIENT] C:> mysql --host=lnx-mysql8.windows.domain `

>> --user=patrick `

>> --password=Password123 `

>> --enable-cleartext-plugin `

>> --ssl-mode=disabled

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.

[patrick@WIN-CLIENT] C:>

Unfortunately even though the connection is rejected, the password is still transmitted unencrypted during authentication process

[root@lnx-mysql8 ~]# tcpflow -c port 3306

tcpflow: listening on enp0s3

192.168.002.004.03306-192.168.002.002.49867: U

8.0.13-commercial@=l?K/sbq!Qa&sG{{Bcaching_sha2_password

192.168.002.002.49867-192.168.002.004.03306: patrick Au'"?..`%85n]~caching_sha2_passwordr_pid3416program_namemysql_client_namelibmysql_thread4992_client_version8.0.13_osWin64_platformx86_64

192.168.002.004.03306-192.168.002.002.49867: mysql_clear_password

192.168.002.002.49867-192.168.002.004.03306: Password123

192.168.002.004.03306-192.168.002.002.49867: aW#HY000Connections using insecure transport are prohibited while --require_secure_transport=ON.

However if we re-run the original connection attempt (with encrpyted traffic between database client and server)  capturing the network traffic between database and domain controller, we can see password is transferred unencypted as this point

[root@lnx-mysql8 .passwords]# tcpflow -c port 389

tcpflow: listening on enp0s3

192.168.002.004.43068-192.168.002.001.00389: 0PcK

NtVer0mainWINDOWS.DOMAIN

netlogon

192.168.002.001.00389-192.168.002.004.43070: 0d00znetlogon1jhl)X0K4fWINDOWSDOMAINWIN-DCWINDOWSWIN-DCDefault-First-Site-NameE0e

192.168.002.004.43070-192.168.002.001.00389: 0B

192.168.002.004.42990-192.168.002.001.00389: 0?`:(CN=patrick,CN=Users,DC=WINDOWS,DC=DOMAINPassword123

192.168.002.001.00389-192.168.002.004.42990: 0a

The documentation

alludes to some of the restrictions, although to my mind it seems to focus on the encryption between database client and server, but not between database server and Domain Controller (unless I’ve missed something).

The server-side authentication_ldap_simple

plugin performs simple LDAP authentication. For connections by accounts that use this plugin, client programs use the client-side mysql_clear_password

plugin, which sends the password to the server in clear text. No password hashing or encryption is used, so a secure connection between the MySQL client and server is recommended to prevent password exposure.

Based on the above observations, as I was expecting, this plugin combination is not really suitable for implementation for any environment which takes security seriously.

If that wasn’t enough reason to avoid the plugin, during the course of my investigation I discovered some other suprising behaviour.  Based on my sharings with support this should be resolved in the next release of SQL Server, so I’ll wait till that is released before sharing.

注意：本文来自hkpatora。本站无法对本文内容的真实性、完整性、及时性、原创性提供任何保证，请您自行验证核实并承担相关的风险与后果！

CoLaBug.com遵循[CC BY-SA 4.0]分享并保持客观立场，本站不承担此类作品侵权行为的直接责任及连带责任。您有版权、意见、投诉等问题，请通过[eMail]联系我们处理，如需商业授权请联系原作者/原网站。