MySQL Bugs: #114610: Generate hash for caching_sha2

Bug #114610	Generate hash for caching_sha2_password auth plugin
Submitted:	10 Apr 2024 12:55	Modified:	11 Apr 2024 9:48
Reporter:	Matthieu Bourgain	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Pluggable Authentication	Severity:	S3 (Non-critical)
Version:	8	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hello,

I want to generate a hash from an user's password, I try to follow this source code https://github.com/mysql/mysql-server/blob/ea7d2e2d16ac03afdd9cb72a972a95981107bf51/sql/au... to generate the hash but I have systematically this error 'The password hash doesn't have the expected format'

Do you have any official documentation to how to hash password ? The goal is to achieve that from Ansible (see https://github.com/ansible-collections/community.mysql/issues/621)

Thank you

How to repeat:
- name: myuser
  community.mysql.mysql_user:
    name: myuser
    plugin_hash_string:  "$A$005$9f86d081884c7d659a2feaa0c55ad015a3bf4f1b2b0b822cd15d6c15b0fd02fe"
    plugin: "caching_sha2_password"

Link with the correct line https://github.com/mysql/mysql-server/blob/ea7d2e2d16ac03afdd9cb72a972a95981107bf51/sql/au...

Thank you Mr. Mourgain,

For your bug report.

However, this is not a bug.

All that you need to do is using one of the following algorithms, as described on this page:

https://dev.mysql.com/doc/refman/8.3/en/pluggable-authentication.html

The choice is, actually, yours. Also , we truly do not know anything about the product that you mention in your opening comment.

Not a bug.

Hi,

You can also look at these source files:

./include/authentication_kerberos_clientopt-vars.h
./include/authentication_kerberos_clientopt-case.h
./include/authentication_kerberos_clientopt-longopts.h
./include/mysql/components/services/mysql_authentication_registration.h
./include/mysql/client_authentication.h
./plugin/x/src/interface/authentication_container.h
./plugin/x/src/interface/authentication.h
./plugin/x/src/server/authentication_container.h
./sql-common/client_async_authentication.h
./libmysql/authentication_kerberos/gssapi_authentication_client.h
./libmysql/authentication_kerberos/sspi_authentication_client.h
./sql/auth/sql_authentication.h
/plugin/x/src/server/authentication_container.cc
./sql-common/client_authentication.cc
./libmysql/authentication_oci_client/authentication_oci_client_plugin.cc
./libmysql/authentication_kerberos/sspi_authentication_client.cc
./libmysql/authentication_kerberos/gssapi_authentication_client.cc
./sql/auth/sql_authentication.cc

Hi,

Please, keep in mind that this is a forum for bug reports and you are not reporting any bug here.

Hi,

note that authentication_string is *not* necessary string like, it's binary data.

I think the best approach is to use the provided CREATE USER statement from am admin account and use mechanisms in the deployment infrastructure to hide the
clear text password.

We made a Python script to generate a custom hash compatible with MySQL because all hash generated by known library (hashlib, passlib...) doesn't work: https://github.com/ansible-collections/community.mysql/issues/621#issuecomment-2049308948

Yes it's not a bug, but it's definitely a lack of documentation

Thank you Mr. Terje Røsten  for providing the valuable feedback.