[MDEV-12183] Can't create "new password" users on old_passwords=1 replication setup Created: 2017-03-06  Updated: 2017-05-21  Resolved: 2017-05-21

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System, Replication
Affects Version/s: 10.1.21
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Hartmut Holzgraefe Assignee: Sergei Golubchik
Resolution: Won't Fix Votes: 1
Labels: None


 Description   

When having both master and slave set up with old_passwords=1 it is possible to create users with new password hash format by setting old_passwords=0 on the session level.

The slave will break on this though as the variable setting is not replicated, and so either the old format is used on the slave, or an error about wrong password hash size is raised on the slave side, stopping the slave SQL thread, when giving the password hash value directly.

How to reproduce:

First create a replication setup where old_passwords=1 is the default global setting for both master and slave.

Then, on the master:

SET SESSION old_passwords=0; 
CRETE USER 'test_user'@'localhost' IDENTIFIED BY 'xxx';
 
 
 
SELECT user,host, password FROM mysql.user WHERE user='test_user'\G
 
*************************** 1. row ***************************
 
    user: test_user
 
    host: localhost
 
password: *3D56A309CD04FA2EEF181462E59011F075C89548

On the slave user was created with old password format:

select user,host, password from mysql.user where user='test_user'\G
 
*************************** 1. row ***************************
 
    user: test_user
 
    host: localhost
 
password: 663c5dd53dae4ed0

Now lets try to set the password hash directly on the master side:

SET SESSION old_passwords=0; 
 
 
CREATE USER 'test_user2'@'localhost' identified by password "*1234567891234567891234567891234567891234";

This breaks the slave:

               Last_SQL_Errno: 1372
 
               Last_SQL_Error: Error 'Password hash should be a 16-digit hexadecimal number' on query. Default database: ''. Query: 'create user 'test_user2'@'localhost' identified by password "*1234567891234567891234567891234567891234"'

The only workaround for now seems to be to use CREATE USER without password first, then to UPDATE the password column in the mysql.user column directly, followed by a FLUSH PRIVILEGES. This gets replicated to the slave correctly.

 



 Comments   
Comment by Elena Stepanova [ 2017-03-06 ]

Theoretically, I'd say it's the same principle that applies to any "New" Master => "Old" Slave replication – if the master uses a feature which does not exist (turned off, disabled) on the slave, replication is not guaranteed to work. However, if it's a real-life use cause which is on some reason important, I suppose something can be done about it. Assigning to serg to decide.

Comment by Hartmut Holzgraefe [ 2017-03-06 ]

Thinking again the better workaround, assuming that all future user accounts are supposed to get created with the new hash format, is to simply have 

old_passwords=0
 
secure-auth=0

on master and slave.

If both old and new format passwords need to be maintained (due to some old pre-4.1 clients, while all others shall use the newer has format) the "direct UPGRADE + FLUSH PRIVILIEGES" approach still seems to be the only viable workaround option for now though ...

Comment by Sergei Golubchik [ 2017-04-26 ]

I'd rather remove old_password variable completely in 10.3. Nobody should be using old authentication nowadays.
But even if one have to, one can always use it explicitly, since 5.2:

CREATE USER 'test_user'@'localhost' IDENTIFIED WITH mysql_old_password USING '663c5dd53dae4ed0'

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