Dual password management

This topic describes how to configure and use dual password management in MySQL mode. With this feature, a user can have two valid passwords: the primary password (new password) and the secondary password (old password). Both can be used for login, referred to as the dual password state. This feature allows users to continue using the old password during configuration switches, reducing the risk of disconnection during a full-scale password change.

Syntax

You can use the ALTER USER or SET PASSWORD statement to modify a user's password and retain the old password, entering the dual password state.

Modify the password and retain the current password

When setting a new password, the old password is retained (both new and old passwords can be used for authentication for a period of time):

ALTER USER 'user_name'@'host'
  IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;

• user_name and host: follow the same account name rules as when creating a user (e.g., 'appuser'@'%').
• new_password: the new password in plain text (stored by the server according to the plugin policy).

In addition to ALTER USER, you can also use the SET PASSWORD statement with the RETAIN CURRENT PASSWORD clause at the end of the assignment clause, achieving the same effect as ALTER USER ... IDENTIFIED BY ... RETAIN CURRENT PASSWORD:

SET PASSWORD [FOR user] = PASSWORD('new_password') RETAIN CURRENT PASSWORD;

Discard the old password

After the new password has been switched, explicitly discard the retained old password, keeping only the current password:

ALTER USER 'user_name'@'host' DISCARD OLD PASSWORD;

After successful execution, the old password can no longer be used for login.

Syntax explanation

• Except for modifying the password of the current user, executing ALTER USER typically requires management privileges such as CREATE USER; the privilege requirements are the same as for the general ALTER USER statement.
• In the ALTER USER scenario, RETAIN CURRENT PASSWORD is only meaningful when specified together with IDENTIFIED BY; it indicates that the password change enters the dual password state. In the SET PASSWORD scenario, this clause is appended after the password option on the right side of the = sign.
• DISCARD OLD PASSWORD is used to clear the old password after all business operations have switched to the new password. If there is no valid old password, the behavior will be based on error prompts.

Examples

Change the password for the user appuser from any host to NewPass and retain the old password:

obclient> ALTER USER 'appuser'@'%' IDENTIFIED BY 'NewPass' RETAIN CURRENT PASSWORD;

Or use SET PASSWORD (equivalent effect):

obclient> SET PASSWORD FOR 'appuser'@'%' = PASSWORD('NewPass') RETAIN CURRENT PASSWORD;

After confirming that all business operations have switched to the new password, discard the old password:

obclient> ALTER USER 'appuser'@'%' DISCARD OLD PASSWORD;

Feature maintenance

Information related to dual passwords is reflected in system tables/views, facilitating audit and maintenance:

• The additional_password key in the user_attributes column of the mysql.user view stores attributes related to the old password, used in dual password scenarios.
• The OLD_PASSWORD and OLD_PASSWORD_START_TIME columns in the oceanbase.DBA_OB_USERS or oceanbase.CDB_OB_USERS views are used in dual password scenarios.

References

• ALTER USER
• SET PASSWORD