How to change mysql user password by the user himself not by admin/root?

Question

How to change mysql user password by the user himself not by admin/root?
I mean, for a user john -

1. the dba creates the user john and sets the default password to john1234
2. now john is able to connect to mysql server.
3. john now wants to change his password to john4321, so that even the dba admin is not aware of the changed password.
4. infact, in my usecase, the root password is lost (hhmmm, and i think there is no way to recover it from the encrypted password string). But I am still fine if john can change his password himself.

Is it possible?

Answer 1

Normally your should be able to change your own password. The command is

SET PASSWORD = PASSWORD('john4321');

The only way to know whether you have the right is to try.
If you have a few ideas what the old password was you could compare the result of

SELECT PASSWORD ('possible old password');

to the hash in the table mySQL.user, if you have access to it.
21/02/2022 I have just created a user with no rights whatsoever. When I logged in as the new user I successfully ran the command:

SET PASSWORD = PASSWORD('1234');

and when I relogged in with password 1234 it worked.