MySQL Bugs: #117471: Documentation regarding the caching_sha2_password algorithm and testvectors is missing.

Bug #117471	Documentation regarding the caching_sha2_password algorithm and testvectors is missing.
Submitted:	14 Feb 13:25	Modified:	17 Feb 15:09
Reporter:	Mirco Babin	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.4.4	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hello MySql Team,

MySql 8.4 disabled the mysql_native_password plugin by default. So the way forward is to start using the caching_sha2_password plugin.

Looking at https://dev.mysql.com/doc/refman/8.4/en/password-logging.html the IDENTIFIED WITH caching_sha2_password BY is listed as insecure. The only secure way of updating an password is by calculating the correct authentication_string and using IDENTIFIED WITH caching_sha2_password AS 0x....

But the how, how does one calculate the caching_sha2_password is nowhere described. At least, I could not find it. And for interoperability also testvectors are missing.

I managed to create a project https://github.com/MircoBabin/MySqlPasswords for calculating the caching_sha2_password. But I can't verify it is correct, because documentation from the MySql team is missing. My base reference for implementing the algorithm in 3 programming languages was this post https://crypto.stackexchange.com/questions/77427/whats-the-algorithm-behind-mysqls-sha256-... Which is very awkward, because that post states it is based on hashcat, a password cracking engine. At the moment this implementation works, for the plain text password input string I have used.

My request: please document the caching_sha2_password algorithm and publish testvectors, so I and others can implement the algorithm and test the interoperability.

Thank you,
Kind regards,
Mirco Babin

How to repeat:
Try to implement the caching_sha2_password algorithm outside of the MySql Sources in a different programming language.

Suggested fix:
Document the caching_sha2_password algorithm and publish testvectors.

Hi Mr. Babin,

Thank you for yoru bug report.

Things are much simpler then how you understood them.

All that you have to do is:

CREATE USER 'user'@'host' IDENTIFIED BY 'password';

MySQL will take care of everything else.

Not a bug.

Hello MySql Verification team,

The documentation states that CREATE USER 'user'@'host' IDENTIFIED BY 'password'; is INSECURE and maybe logged, processed by proxy's or disclosed in other ways. See https://dev.mysql.com/doc/refman/8.4/en/password-logging.html

The only SECURE way of creating an user is using CREATE USER 'user'@'host' IDENTIFIED WITH caching_sha2_password AS 0x24412430303524655F56782245563C5E31203E512F333579796A7857526A56527030334E666F34314D4D7978514A674C644B63777248594D746E71637550574D656345493836;

So the missing documentation regarding the caching_sha2_password algorithm and testvectors is still an issue.

Kind regards,
Mirco Babin

Hi Mr. Babin,

The page that you referred to does not contain the text that you wrote in your last comment.

The page URL that you have sent us is only about logging. 

You should abide by the logging instructions on that page, but you will not have to create your own caching password.

Hello MySql Verification team,

Consider the scenario where the MySql server is on premise at the customer, and only a remote client can create users. When sending CREATE USER 'user'@'host' IDENTIFIED BY 'password'; with the password in plaintext, what can happen?

1) The statement containing the plaintext password maybe logged on the remote client. By a shell tool, by HeidiSql, by performance logging, by PHP Laravel framework, by others. The logging maybe distributed to other places. And all of this still contains the plaintext password. So the plaintext password may end up in unexpected places and maybe disclosed in unforeseen ways. This is outside of the MySQL environment and there is nothing MySQL can do to mitigate or prevent this kind of logging.
2) The statement containing the password maybe sniffed by an attacker when it is send to the MySql Server over the tcp/ip connection.
3) An Sql proxy maybe active receiving the plaintext password. And logging or doing other things with it.
4) Typing in the statement with the plain password maybe filmed by someone looking over my shoulder.

---

So using a plaintext password is potentially INSECURE, when used outside of the controlled MySQL environment. Do we agree on that?

---

Then comes the question: how does one create a user and keeping the password a secret in a SECURE way. Such that even when the password is logged, sniffed, looked over the shoulder or by other means leaked, there is not a security breach revealing the plaintext password to unknown (evil) people?

The only way is by using the hash. So the CREATE USER 'user'@'host' IDENTIFIED WITH caching_sha2_password AS 0x24412430303524655F56782245563C5E31203E512F333579796A7857526A56527030334E666F34314D4D7978514A674C644B63777248594D746E71637550574D656345493836; does not contain a plaintext password anymore, but the hash. And is secure when leaked in unforeseen ways.

---

And then comes my obversation. There is no documentation about the caching_sha2_password algorithm and there are no testvectors.

---

Yes, I do understand what I'm doing is complex. But security comes at a cost and it is never cheap.

Kind Regards,
Mirco Babin

The answer to your question "with the password in plaintext, what can happen?".

Nothing can happen.

MySQL is using SSL for the connection cryptography, which can be read as much as you wish, but that is a cryptography that nobody ever managed to decipher !!!!

Not a bug.

Hello MySql Verification team,

You did not answer my question: So using a plaintext password is potentially INSECURE, when used outside of the controlled MySQL environment. Do we agree on that?

Kind regards,
Mirco Babin

No, we definitely do not agree with that.

You do not have to specify password on the command line.

With online tools, you just add -p option and then you type your password, which is NEVER visible.

With GUI clients, password is never displayed.

So, you are totally wrong.

Hello MySql Verification team,

Please answer this question: when one issues the MySql statement CREATE USER 'user'@'host' IDENTIFIED BY 'password';m  is the password then visible in plaintext?

Kind regards,
Mirco Babin

No, it is not .......

It is visible only to the one who runs the command and NOBODY else.

The one that runs that command must know what password is he / she typing, doesn't he / she ?? A mistype that goes unnoticed would be a problem, that would require ALTER USER.

Nobody else can see it, thanks to SSL.

We must remind you that this is a forum with bug reports with fully repeatable test cases.

And this is definitely NOT a bug.

Hence, if you wish to discuss it further, you can use our free support site or paid support site, as per your choice.

Hello MySql Verification team,

Well I'm not a native English speaker. So I'm trying to get my point across the table. I'm sorry that this takes so long.

Your conclusion "It is visible only to the one who runs the command and NOBODY else." is wrong. Because the CREATE USER 'user'@'host' IDENTIFIED BY 'password'; statement containing the plaintext password maybe written into a file, into a history file, into anything one can imagine.

Can we agree that the statement CREATE USER 'user'@'host' IDENTIFIED BY 'password'; containing the plaintext password maybe potentially written inside all kind of files, before reaching the MySql SSL connection?

Kind regards,
Mirco Babin

D

Hi,

No, we definitely can NOT agree about anything of the above.

CREATE and ALTER USER commands are not saved my any MySQL client program and can not find their way into any history file.

If you issue those commands directly in Unix shell, it is at your peril. Our Manual has many warnings on the subject.

Do note that that is a Reference Manual.

It is not User Manual.

MySQL does not write nor maintain Users Manual.

There are many, many users manual on the market, available in shops, on Amazon etc .......

Hence, please stop using bug reporting site for discussions. Use either paid or free support MySQL site, if you are looking for discussions.

And finaly:

This is not a bug.

Hello MySql Verification team,

"If you issue those commands directly in Unix shell, it is at your peril. Our Manual has many warnings on the subject."

Thank you. That is the point I want to bring across the table. It is my responsibility to not use CREATE USER 'user'@'host' IDENTIFIED BY 'password'; because it contains a plaintext password. It is not the MySql team responsibility.

So I'm being responsible and I'm wanting to use CREATE USER 'user'@'host' IDENTIFIED WITH caching_sha2_password AS 0x24412430303524655F56782245563C5E31203E512F333579796A7857526A56527030334E666F34314D4D7978514A674C644B63777248594D746E71637550574D656345493836;

Where is then documented how the 0x24412430303524655F56782245563C5E31203E512F333579796A7857526A56527030334E666F34314D4D7978514A674C644B63777248594D746E71637550574D656345493836 part is calculated?

Kind regards,
Mirco Babin

Hi,

You completely misunderstood what was written.

You can issue that command from any mysql client program in both interactive and batch mode.

Hence, all your other questions are totally irrelevant.

Hello MySql Verification team,

"You completely misunderstood what was written. You can issue that command from any mysql client program in both interactive and batch mode."

This is the last message I'm going to write. I did not misunderstood you. The "If you issue those commands directly in Unix shell, it is at your peril." is exactly why the caching_sha2_password algorithm should be officially documented. So that on the unix shell the plaintext 'password' can be translated by means of a calculation into the responsible hash hex sequence.

I did my best, maybe others can takeover from here.

Kind regards,
Mirco Babin