Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26739

Login allowed after ERROR 1396

    XMLWordPrintable

    Details

      Description

      It is reported by one of our customer that, after the error - 1396 while doing "ALTER USER" to change the password of a user, we can connect the database without a password.

      Locally, we can reproduce the same reported problem:

      We disable unix_socket and ALTER USER to change password attempt got error - 1396, when try to login with that user without password, we can login and using the valid password throwing error which is not expected behavior. Things works well, if we run the "flush privileges" after we got the error - 1396

      Disabled unix socket

      | unix_socket | DISABLED | AUTHENTICATION | NULL | GPL |
      

      Tried login to root user, without password, it's not allowing

      [root@test001 ~]# mysql -uroot
      ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded
      

      With the password provided, login in, expected

      [root@test001 ~]# mysql -uroot -p'O123$uresh'
      Welcome to the MariaDB monitor. Commands end with ; or \g.
      Your MariaDB connection id is 5
      Server version: 10.5.12-MariaDB MariaDB Server
      

      Created a user - user3

      MariaDB [(none)]> create user 'user3'@'%' identified by 'O123$uresh';
      Query OK, 0 rows affected (0.007 sec)
      

      from root OS user login check

      [root@test001 ~]# mysql -uuser3
      ERROR 1045 (28000): Access denied for user 'user3'@'localhost' (using password: NO)
      [root@test001 ~]# mysql -uuser3 -p
      Enter password: <=== just pressed ENTER key
      ERROR 1045 (28000): Access denied for user 'user3'@'localhost' (using password: NO)
      [root@test001 ~]# mysql -uuser3 -p'O123$uresh' <== given valid password
      Welcome to the MariaDB monitor. Commands end with ; or \g.
      Your MariaDB connection id is 26
      Server version: 10.5.12-MariaDB MariaDB Server
      

      from user3 OS user (same behavior as above, which is expected one)

      [user3@test001 ~]$ mysql -uuser3
      ERROR 1045 (28000): Access denied for user 'user3'@'localhost' (using password: NO)
      [user3@test001 ~]$ mysql -uuser3 -p
      Enter password:
      ERROR 1045 (28000): Access denied for user 'user3'@'localhost' (using password: NO)
      [user3@test001 ~]$ mysql -uuser3 -p'O123$uresh'
      Welcome to the MariaDB monitor. Commands end with ; or \g.
      Your MariaDB connection id is 32
      Server version: 10.5.12-MariaDB MariaDB Server
      

      Now, the actual check. Tried to change the password for the user 'user3' and got error 1396. After which without password, able to connect the database with user 'user3'

      [root@test001 ~]# mysql -uroot -p'O123$uresh'
      Welcome to the MariaDB monitor. Commands end with ; or \g.
      Your MariaDB connection id is 33
      Server version: 10.5.12-MariaDB MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> alter user 'user3'@'%' identified by 'tests';
      ERROR 1396 (HY000): Operation ALTER USER failed for 'user3'@'%'
      MariaDB [(none)]> exit
      Bye
      [root@test001 ~]# mysql -uuser3 <== no password
      Welcome to the MariaDB monitor. Commands end with ; or \g.
      Your MariaDB connection id is 34
      Server version: 10.5.12-MariaDB MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> exit
      Bye
      [root@test001 ~]# mysql -uuser3 -p
      Enter password: <======= Just pressed enter
      Welcome to the MariaDB monitor. Commands end with ; or \g.
      Your MariaDB connection id is 35
      Server version: 10.5.12-MariaDB MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> exit
      Bye
      [root@test001 ~]# mysql -uuser3 -p'O123$uresh' <==Given actual password
      ERROR 1045 (28000): Access denied for user 'user3'@'localhost' (using password: YES)
      

      Run the "flush privileges" then is working as expected

      [root@test001 ~]# mysql -uroot -p'O123$uresh'
      Welcome to the MariaDB monitor. Commands end with ; or \g.
      Your MariaDB connection id is 41
      Server version: 10.5.12-MariaDB MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> flush privileges;
      Query OK, 0 rows affected (0.001 sec)
       
      MariaDB [(none)]> exit
      Bye
      [root@test001 ~]# mysql -uuser3
      ERROR 1045 (28000): Access denied for user 'user3'@'localhost' (using password: NO)
      [root@test001 ~]# mysql -uuser3 -p
      Enter password:
      ERROR 1045 (28000): Access denied for user 'user3'@'localhost' (using password: NO)
      [root@test001 ~]# mysql -uuser3 -p'O123$uresh'
      Welcome to the MariaDB monitor. Commands end with ; or \g.
      Your MariaDB connection id is 44
      Server version: 10.5.12-MariaDB MariaDB Server{quote}
      

      Same scenario, works fine at v10.3.27. i.e., ERROR 1396 while ALTER USER.. without FLUSH PRIVILIEGES, works as expected.

      [root@test001 ~]# mysql -uroot -pO123$uresh
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 22
      Server version: 10.3.27-MariaDB MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> create user 'user1'@'%' identified by 'suresh';
      ERROR 1396 (HY000): Operation CREATE USER failed for 'user1'@'%'
      MariaDB [(none)]> exit
      Bye
      [root@test001 ~]# mysql -uuser1 -p
      Enter password:
      ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: NO)
      [root@test001 ~]# mysql -uuser1 -p'O123$uresh'
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 24
      Server version: 10.3.27-MariaDB MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> exit
      Bye
      [root@test001 ~]# mysql -uuser1
      ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: NO)
      [root@test001 ~]#
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              suresh.ramagiri@mariadb.com suresh ramagiri
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration