Dual-password management

This topic describes how to configure dual-password management, its usage, and provides examples in MySQL-compatible mode. With dual-password management, a user can have two valid passwords: the primary password and the secondary password. The primary password is the current effective new password, while the secondary password is the retained old password. Both can be used for login simultaneously, which is called the dual-password state. This feature allows you to use the old password to connect during the configuration switching period, reducing the risk of disconnection caused by a one-time full password change.

Syntax

You can use the ALTER USER or SET PASSWORD statement to change a user's password while retaining the old password, entering the dual-password state.

Change the password and retain the current password

Set a new password while retaining the original password (both the old and new passwords can be used for authentication for a period):

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

• user_name, host: follow the account name rules used when creating the user (for example, 'appuser'@'%').
• new_password: the plaintext new password (stored by the server according to plugin policies).

In addition to ALTER USER, you can also add RETAIN CURRENT PASSWORD at the end of the assignment clause in the SET PASSWORD statement, which has 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 completing the new password switch, explicitly discard the retained old password and keep only the current password:

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

After successful execution, the old password cannot be used for login anymore.

Syntax description

• Except for changing the password of the current user, executing ALTER USER usually requires corresponding management privileges such as CREATE USER. The privilege requirements are the same as for general ALTER USER.
• In the ALTER USER scenario, RETAIN CURRENT PASSWORD is meaningful only when specified together with IDENTIFIED BY; it indicates entering the dual-password state with this password change. In the SET PASSWORD scenario, append this clause after the password option on the right side of =.
• DISCARD OLD PASSWORD is used to clean up the old password after all business has switched to the new password. If no valid old password currently exists, the behavior will be determined by the error prompt.

Examples

Change the account password of user appuser connecting from any host to NewPass, while retaining the old password before the password change:

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

Or use SET PASSWORD (with equivalent effect):

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

After confirming that the business has switched to the new password, discard the old password:

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

Feature maintenance

Dual-password related information is reflected in system tables/views for audit and maintenance purposes:

• 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 view are used for dual-password scenarios.

References

• ALTER USER
• SET PASSWORD