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

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

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

          danblack Daniel Black added a comment -

          robertbindar thanks for describing MySQL behavior. While password expiration is compatibility implemented, and in this MDEV we want to ensure that a save/restore generates the same user especially as this part of MDEV-23630. We don't have to implement their bugs/deficiencies.

          ref: MDEV-24103 for last_password_changed save/restore, though I'm almost tempted to WONTFIX MDEV-24103 even though the restore isn't perfect and restored users are given extra grace for their password changes.

          On lambda's, there's other very specific parts of code in a function. Probably a static function is sufficient. Save lambdas where there's a need to change the function ptr/assignment.

          danblack Daniel Black added a comment - robertbindar thanks for describing MySQL behavior. While password expiration is compatibility implemented, and in this MDEV we want to ensure that a save/restore generates the same user especially as this part of MDEV-23630 . We don't have to implement their bugs/deficiencies. ref: MDEV-24103 for last_password_changed save/restore, though I'm almost tempted to WONTFIX MDEV-24103 even though the restore isn't perfect and restored users are given extra grace for their password changes. On lambda's, there's other very specific parts of code in a function. Probably a static function is sufficient. Save lambdas where there's a need to change the function ptr/assignment.
          robertbindar Robert Bindar added a comment -

          I agree with you, this issue should be fixed as we discussed, but again, not sure this should be a bug fix in 10.4, I'm more inclined to say it should go in 10.6, if we change behavior of existing feature, we should at least do it in our latest version.

          About the lambda here, I don't see any problem with it, I see it as a perfectly valid case especially given sql_acl.cc is 14k+ lines of code and a new function would complicate the file one step further for someone that browses through the file looking for some random functionality.

          Let me know if the patch is ok to push (except the two comments) and in which version you think we should push it.

          robertbindar Robert Bindar added a comment - I agree with you, this issue should be fixed as we discussed, but again, not sure this should be a bug fix in 10.4, I'm more inclined to say it should go in 10.6, if we change behavior of existing feature, we should at least do it in our latest version. About the lambda here, I don't see any problem with it, I see it as a perfectly valid case especially given sql_acl.cc is 14k+ lines of code and a new function would complicate the file one step further for someone that browses through the file looking for some random functionality. Let me know if the patch is ok to push (except the two comments) and in which version you think we should push it.
          danblack Daniel Black added a comment -

          If we push to 10.6, for users (and our mysqldump) we've created a need to a) implement a 10.4 work around. b) implement a 10.6 special case to back to what they probably have now. Code that takes SHOW CREATE USER output and only reads the the first row is no worse off. For those that read all rows it becomes a compatible enough to recreate the user as it was previously. I'm not sure I see a case of writing code where the additional row actually breaks anything (maybe if a coder added appended ";" outside the loop, not sure accounting for that case is worth it). So please, 10.4.

          GeoffMontee, I believe SkySQL might use this. Do you see a problem with the proposed patch?

          Lambdas, I can't tell if serg still has a objection after your rational.

          Last Review comment on patch:
          add_user_parameters isn't needed for the ALTER USER part. Its already included in the `CREATE USER` output and we'd just be duplicating any configuration there. So remove that one line.
          Don't forget multi-line comment syntax above.

          danblack Daniel Black added a comment - If we push to 10.6, for users (and our mysqldump) we've created a need to a) implement a 10.4 work around. b) implement a 10.6 special case to back to what they probably have now. Code that takes SHOW CREATE USER output and only reads the the first row is no worse off. For those that read all rows it becomes a compatible enough to recreate the user as it was previously. I'm not sure I see a case of writing code where the additional row actually breaks anything (maybe if a coder added appended ";" outside the loop, not sure accounting for that case is worth it). So please, 10.4. GeoffMontee , I believe SkySQL might use this. Do you see a problem with the proposed patch? Lambdas, I can't tell if serg still has a objection after your rational. Last Review comment on patch: add_user_parameters isn't needed for the ALTER USER part. Its already included in the `CREATE USER` output and we'd just be duplicating any configuration there. So remove that one line. Don't forget multi-line comment syntax above.
          robertbindar Robert Bindar added a comment -

          Thanks for the thorough answer danblack and good catch on add_user_parameters (i removed it but I needed to add the hostname because that function was adding the hostname inside). Once tests are ready I will push the patch if no more objections arrive.

          robertbindar Robert Bindar added a comment - Thanks for the thorough answer danblack and good catch on add_user_parameters (i removed it but I needed to add the hostname because that function was adding the hostname inside). Once tests are ready I will push the patch if no more objections arrive.
          robertbindar Robert Bindar added a comment -

          Pushed to 10.4

          robertbindar Robert Bindar added a comment - Pushed to 10.4

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.