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

show create user cannot display password expired status and password expire interval

    XMLWordPrintable

    Details

      Description

      A user that has been set to PASSWORD EXPIRE and not have it unlocked by any of:

      • alter user user2@localhost PASSWORD EXPIRE NEVER
      • alter user user2@localhost PASSWORD EXPIRE INTERVAL 60 DAY
      • alter user user2@localhost PASSWORD EXPIRE DEFAULT

      10.4-4d6c6611443f1e0e1cdab34ac6e320031e7f980b

      MariaDB [(none)]> create user user2@localhost PASSWORD EXPIRE NEVER; show create user user2@localhost; select * from mysql.global_priv where user='user2';
      Query OK, 0 rows affected (0.001 sec)
       
      +-------------------------------------------------------+
      | CREATE USER for user2@localhost                       |
      +-------------------------------------------------------+
      | CREATE USER `user2`@`localhost` PASSWORD EXPIRE NEVER |
      +-------------------------------------------------------+
      1 row in set (0.000 sec)
       
      +-----------+-------+-----------------------------------------------------------------------------------------------------------------------------------+
      | Host      | User  | Priv                                                                                                                              |
      +-----------+-------+-----------------------------------------------------------------------------------------------------------------------------------+
      | localhost | user2 | {"access":0,"plugin":"mysql_native_password","authentication_string":"","password_last_changed":1604464098,"password_lifetime":0} |
      +-----------+-------+-----------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.001 sec)
       
      MariaDB [(none)]> alter user user2@localhost PASSWORD EXPIRE ; show create user user2@localhost;  select * from mysql.global_priv where user='user2';
      Query OK, 0 rows affected (0.001 sec)
       
      +-------------------------------------------------+
      | CREATE USER for user2@localhost                 |
      +-------------------------------------------------+
      | CREATE USER `user2`@`localhost` PASSWORD EXPIRE |
      +-------------------------------------------------+
      1 row in set (0.000 sec)
       
      +-----------+-------+--------------------------------------------------------------------------------------------------------------------------+
      | Host      | User  | Priv                                                                                                                     |
      +-----------+-------+--------------------------------------------------------------------------------------------------------------------------+
      | localhost | user2 | {"access":0,"plugin":"mysql_native_password","authentication_string":"","password_last_changed":0,"password_lifetime":0} |
      +-----------+-------+--------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.001 sec)
       
      MariaDB [(none)]> alter user user2@localhost PASSWORD EXPIRE NEVER; show create user user2@localhost; select * from mysql.global_priv where user='user2';
      Query OK, 0 rows affected (0.001 sec)
       
      +-------------------------------------------------+
      | CREATE USER for user2@localhost                 |
      +-------------------------------------------------+
      | CREATE USER `user2`@`localhost` PASSWORD EXPIRE |
      +-------------------------------------------------+
      1 row in set (0.000 sec)
       
      +-----------+-------+--------------------------------------------------------------------------------------------------------------------------+
      | Host      | User  | Priv                                                                                                                     |
      +-----------+-------+--------------------------------------------------------------------------------------------------------------------------+
      | localhost | user2 | {"access":0,"plugin":"mysql_native_password","authentication_string":"","password_last_changed":0,"password_lifetime":0} |
      +-----------+-------+--------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.001 sec)
       
      MariaDB [(none)]> alter user user2@localhost PASSWORD EXPIRE INTERVAL 60 DAY; show create user user2@localhost; select * from mysql.global_priv where user='user2
      ';
      Query OK, 0 rows affected (0.000 sec)
       
      +-------------------------------------------------+
      | CREATE USER for user2@localhost                 |
      +-------------------------------------------------+
      | CREATE USER `user2`@`localhost` PASSWORD EXPIRE |
      +-------------------------------------------------+
      1 row in set (0.000 sec)
       
      +-----------+-------+---------------------------------------------------------------------------------------------------------------------------+
      | Host      | User  | Priv                                                                                                                      |
      +-----------+-------+---------------------------------------------------------------------------------------------------------------------------+
      | localhost | user2 | {"access":0,"plugin":"mysql_native_password","authentication_string":"","password_last_changed":0,"password_lifetime":60} |
      +-----------+-------+---------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.001 sec)
       
      MariaDB [(none)]> alter user user2@localhost PASSWORD EXPIRE DEFAULT; show create user user2@localhost; select * from mysql.global_priv where user='user2';
      Query OK, 0 rows affected (0.000 sec)
       
      +-------------------------------------------------+
      | CREATE USER for user2@localhost                 |
      +-------------------------------------------------+
      | CREATE USER `user2`@`localhost` PASSWORD EXPIRE |
      +-------------------------------------------------+
      1 row in set (0.000 sec)
       
      +-----------+-------+---------------------------------------------------------------------------------------------------------------------------+
      | Host      | User  | Priv                                                                                                                      |
      +-----------+-------+---------------------------------------------------------------------------------------------------------------------------+
      | localhost | user2 | {"access":0,"plugin":"mysql_native_password","authentication_string":"","password_last_changed":0,"password_lifetime":-1} |
      +-----------+-------+---------------------------------------------------------------------------------------------------------------------------+
      
      

      So I'm assuming that any of the unexpired variants should set password_last_changed=NOW if its 0. At a minimum the `show create user` is incorrect.

        Attachments

          Activity

            People

            Assignee:
            robertbindar Robert Bindar
            Reporter:
            danblack Daniel Black
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: