[MDEV-9859] user with password on new format, not work on remote connections Created: 2016-04-01  Updated: 2016-05-14  Resolved: 2016-05-14

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System, Platform Debian
Affects Version/s: 10.1.13
Fix Version/s: N/A

Type: Bug Priority: Trivial
Reporter: Abdelakarim Mateos Assignee: Unassigned
Resolution: Cannot Reproduce Votes: 0
Labels: None
Environment:

Debian Jessie updated

Attachments: JPEG File 1__screen__sh_.jpg    

 Description   

The issue affects users when they try to access remotely.

The message is:

root@kvm401 [~]# mysql -u setdart_cms -p -h mysql.domain.com
 
Enter password:
 
ERROR 1275 (HY000): Server is running in --secure-auth mode, but 'setdart_cms'@'kvm401.otherdomain.net' has a password in the old format; please change the password to the new format

But the user has the password in appropriate format, in new password format

MariaDB [mysql]> select Host,User,Password from user where User like  'setdart_xxx';
 
+---------------+-------------+-------------------------------------------+
 
| Host          | User        | Password                                  |
 
+---------------+-------------+-------------------------------------------+
 
| localhost     | setdart_xxx | *88C12D1EC2E9914A3409CB4C90FA218349608425 |
 
| dom.com       | setdart_xxx | *88C12D1EC2E9914A3409CB4C90FA218349608425 |
 
| 81.184.16.XXX | setdart_xxx | *88C12D1EC2E9914A3409CB4C90FA218349608425 |
 
| 81.184.17.XXX | setdart_xxx | *88C12D1EC2E9914A3409CB4C90FA218349608425 |
 
| 213.98.87.XXX | setdart_xxx | *88C12D1EC2E9914A3409CB4C90FA218349608425 |
 
| 80.39.222.XX  | setdart_xxx | *88C12D1EC2E9914A3409CB4C90FA218349608425 |
 
| 81.45.170.XXX | setdart_xxx | *88C12D1EC2E9914A3409CB4C90FA218349608425 |
 
| 176.31.31.XXX | setdart_xxx | *88C12D1EC2E9914A3409CB4C90FA218349608425 |
 
+---------------+-------------+-------------------------------------------+
 
8 rows in set (0.00 sec)

If from an authorized location (office server) we try to access, no problem
But if we try to access from other authorized equipment, we skip the error message.

In another things, others were created, tests were made with the same result, and randomly one of them had the same problem suddenly started running.

Some may forget or any error in understanding the problem

 Comments   
Comment by Elena Stepanova [ 2016-04-01 ]

Please run 

SELECT user, host, password FROM mysql.user WHERE user = '';

and paste the output (you can obfuscate host names if you wish).

Comment by Abdelakarim Mateos [ 2016-04-16 ]

Sorry for daly. Just get my holydays

SELECT user, host, password FROM mysql.user WHERE user = 'setdart_xxxxxx'
 
	+----------------+---------------------+-------------------------------------------+
 
	| user           | host                | password                                  |
 
	+----------------+---------------------+-------------------------------------------+
 
	| setdart_xxxxxx | XXX.31.31.227       | *3ED850F72DF71010195D51D6A18EEFE0149DDA3F |
 
	| setdart_xxxxxx | XXX.74.121.100      | *3ED850F72DF71010195D51D6A18EEFE0149DDA3F |
 
	| setdart_xxxxxx | mysql.xxxxxxxxcom   | *3ED850F72DF71010195D51D6A18EEFE0149DDA3F |
 
	| setdart_xxxxxx | 81.45.xxx.221       | *3ED850F72DF71010195D51D6A18EEFE0149DDA3F |
 
	| setdart_xxxxxx | 5.135.xx.81         | *3ED850F72DF71010195D51D6A18EEFE0149DDA3F |
 
	| setdart_xxxxxx | kvm401.xxxxxxxx.net | *3ED850F72DF71010195D51D6A18EEFE0149DDA3F |
 
	| setdart_xxxxxx | localhost           | *3ED850F72DF71010195D51D6A18EEFE0149DDA3F |
 
	| setdart_xxxxxx | 178.xxx.xx.218      | *3ED850F72DF71010195D51D6A18EEFE0149DDA3F |
 
	| setdart_xxxxxx | 208.XXX.XX.106      | *3ED850F72DF71010195D51D6A18EEFE0149DDA3F |
 
	| setdart_xxxxxx | 81.XXX.17.102       | *3ED850F72DF71010195D51D6A18EEFE0149DDA3F |
 
	| setdart_xxxxxx | 81.XXX.16.187       | *3ED850F72DF71010195D51D6A18EEFE0149DDA3F |
 
	| setdart_xxxxxx | kvm401.XXXXXXX.com  | *3ED850F72DF71010195D51D6A18EEFE0149DDA3F |
 
	| setdart_xxxxxx | XXX.98.87.249       | *3ED850F72DF71010195D51D6A18EEFE0149DDA3F |
 
	| setdart_xxxxxx | 80.39.XXX.64        | *3ED850F72DF71010195D51D6A18EEFE0149DDA3F |
 
	| setdart_xxxxxx | XXXXXXX.com         | *3ED850F72DF71010195D51D6A18EEFE0149DDA3F |
 
	+----------------+---------------------+-------------------------------------------+

Comment by Abdelakarim Mateos [ 2016-04-16 ]

After some test, now work on one server

Modification work on server debian with 10.1.13-MariaDB-1~jessie (Server A)

I added on [client] section /etc/mysql/my.cnf

secure-auth

secure-auth

On server B, with 10.1.13-MariaDB-1~jessie over Centos 7 with WHM/Cpanel
I added on [client] section /etc/my.cnf same variable

On two server run

 
 
SHOW VARIABLES LIKE '%secure%';
 
+------------------+-------+
 
| Variable_name | Value |
 
+------------------+-------+
 
| secure_auth | ON |
 
| secure_file_priv | |
 
+------------------+-------+

Well. After restart

from server b (Debian) to server A

mysql -u setdart_abkrim -h kvm401.xxxxxx.com --password='XXXXXXXXX'
 
ERROR 1275 (HY000): Server is running in --secure-auth mode, but 'setdart_xxxxxx'@'XXX.31.31.227' has a password in the old format; please change the password to the new format

Form server A to B work fine

mysql -u setdart_XXXXX -h mysql.xxxxxx.com --password='XXXXXXXXX'
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 562
 
Server version: 10.1.13-MariaDB-1~jessie mariadb.org binary distribution
 
 
 
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]>

Comment by Elena Stepanova [ 2016-04-18 ]

abkrim,
When I asked to run 

SELECT user, host, password FROM mysql.user WHERE user = '';

I actually meant exactly that, empty string in the user field. Better yet, please run

SELECT user, host, password, plugin, authentication_string FROM mysql.user WHERE user = '';

The point is, since we only see obfuscated names in the output (and understandably so), we cannot make sure there is an exact match for your username@hostname in mysql.user table. And if there is no such record, the server would pick an anonymous account ''@hostname, even though the error message would still have the full name.

Comment by Abdelakarim Mateos [ 2016-05-14 ]

Close incident. Now work fine.

Generated at Thu Feb 08 07:37:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.