Wsrtjtyk

I have found a weird phenomenon on my MariaDB server (version 10.1.26-MariaDB-0+deb9u1)

I used to have a user XYZ long time ago, and this user probably got deleted sometime. However, I tried to login using this user and I got the following error message:

mysqli_real_connect(): (HY000/1275): Server is running in
--secure-auth mode, but 'XYZ'@'localhost' has a password in the old format; please change the password to the new format

Just to be sure, I tried to login using a non-existing user. For example, I try to login as NOTEXISTING, just to verify that the error message is indeed different.

mysqli_real_connect(): (HY000/1045): Access denied for user
'NOTEXISTING'@'localhost' (using password: YES)

Now, the question is, where is the old user information stored?

The user does not exist in the mysql database:

select * from mysql.user where user = 'XYZ';

=> empty result

grep -r XYZ /path_to_mysql_database_dir/mysql/

=> nothing

I also tried "FLUSH PRIVILEGES" to reload the user table.

Do you have an idea where the user information is stored?

Update

After trying various things and even testing on a completely fresh installed system, I come to the conclusion that it must be some kind of bug, so I opened a bug report: https://jira.mariadb.org/browse/MDEV-17789 . Any other ideas are welcome.

The plaintext password is not stored anywhere.

SELECT user, host, password FROM user may provide something like

| pm_demo          | localhost   | FFC3F585           |

| dist             | localhost   | A8900DDB           |

| ronly            | localhost   | 5208517A           |

| spent            | localhost   | 26B08F08           |

| test             | 1.2.3.4     | A40C6DCC           |

That "password" is really an encrypted version of the plaintext password. It is the "old format", which is not very secure. New passwords look more like

*A5280BD3F8C6BCC6537FCC3E113D794DD53534CC

There are also other authentication mechanisms. (I don't know where you are in the evolution of authentication.)

SELECT * FROM user WHERE user = 'xyz'G

*************************** 1. row ***************************

                  Host: localhost

                  User: xyz

              Password: *6D800EA40C6DCC75BFF67DAB58D5D49FC5F8E568

           Select_priv: N

           Insert_priv: N

           Update_priv: N

           Delete_priv: N

           Create_priv: N

             Drop_priv: N

           Reload_priv: N

         Shutdown_priv: N

          Process_priv: N

             File_priv: N

            Grant_priv: N

...

          x509_subject: 

         max_questions: 0

           max_updates: 0

       max_connections: 0

  max_user_connections: 0

                plugin: mysql_native_password

 authentication_string: 

      password_expired: N

1 row in set (0.00 sec)





SHOW GRANTS FOR xyz@localhost;

+------------------------------------------------------------------------------------------------------------+

| Grants for xyz@localhost                                                                                   |

+------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'xyz'@'localhost' IDENTIFIED BY PASSWORD '*6D800EA40C6DCC75BFF67DAB58D5D49FC5F8E568' |

| GRANT ALL PRIVILEGES ON `xyz`.* TO 'xyz'@'localhost'                                                       |

+------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

A developer has confirmed that the (in my opinion wrong) error message is intentional behavior, based on a hash of the user table.

https://jira.mariadb.org/plugins/servlet/mobile#issue/MDEV-17789