MySQL Bugs: #115973: Dual password usages should have a counter per users

Bug #115973	Dual password usages should have a counter per users
Submitted:	30 Aug 2024 17:04	Modified:	1 Oct 2024 8:13
Reporter:	Yoann La Cancellera	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Pluggable Authentication	Severity:	S4 (Feature request)
Version:	8.4	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When using the DISCARD OLD PASSWORD, we have to assume the old password is not used anymore. 
There are no ways of making sure it is not used by any currently connected user.

It is printed in the error log, but nothing is stored within MySQL to track it properly.

Using performance_schema.error_log and joining current processes using THREAD_ID could be a workaround, albeit it would be costly and potentially wrong.

How to repeat:
CREATE USER some_user IDENTIFIED BY 'some_password';

<connect with it on terminal 1>

ALTER USER some_user RETAIN CURRENT PASSWORD IDENTIFIED BY 'new_password';

<connect with it on terminal 2>
<reconnect terminal 1 to produce an error log>

\s does not any differences in terminals, nor do any statuses and variables

Suggested fix:

It could be either done via:
- a counter of old password being in use in each users
- having the timestamp of the latest auth using the old password stored for each users
- a field in performance_schema.users
- a field in performance_schema.session_connect_attrs

Hello Yoann,

Thank you for the report and feature request.

regards,
Umesh

Thank you for the very reasonable feature request.
I have a question: is it enough to just track old password usage since the last restart? 
Writing something in a disk-based table during login can have a serious performance impact.

Storing this information on disk seems overkill to me, especially if it has performance impacts
Having the info since the last restart seems reasonable to me

Just in case I'm detailing one of my suggestion, it was not clear
"- a field in performance_schema.session_connect_attrs"
I meant having the password shasum as a row in this table. I'm not saying it's the best idea, it might be problematic to store any password shasum outside of the user table. That way it could have been used to join mysql.user on the current or old password. It felt to be the quickest implementation to me

Thanks for your update