MySQL 8: Password Verification Policy

In keeping with my MySQL 8 user administration and security theme, I’d like to discuss the password verification-required policy introduced in MySQL 8.0.13. With this feature, it is possible to require that attempts to change an account password be verified by specifying the existing current password to be replaced.

Introduction

The password verification-required policy enables DBAs to prevent users from changing a password without proving that they know the current password. When would this happen? Such changes could occur if a user walked away from a terminal session without logging out, and a malicious user uses the open session to change the original user’s MySQL password. As you can imagine, this could have disastrous consequences:

• The original user is now unable to access MySQL until an administrator can reset the account password. 
• The malicious user can access MySQL until a password reset can be implemented compromising any data the original user had access to. 

Per-User Settings

Password-verification policy can be established globally, and individual user accounts can be set to either defer to the global policy or override the global policy with specific per-account behavior. This setting is established by the PASSWORD REQUIRE option of CREATE USER and ALTER USER statements. 

• If the account setting is PASSWORD REQUIRE CURRENT, then password changes must specify the current password. 
• If the account setting is PASSWORD REQUIRE CURRENT OPTIONAL, then the user can optionally supply the current password but it isn’t required for a password change.
• If the account setting is PASSWORD REQUIRE CURRENT DEFAULT, then the global password_require_current system variable determines the verification-required policy for the account: 
  • Password changes must specify the current password if password_require_current system variable is enabled. 
  •  Password changes may optionally specify the current password if password_require_current system variable is disabled. 

If the account setting is not PASSWORD REQUIRE CURRENT DEFAULT, the account setting takes precedence over the global policy established by the password_require_current system variable. Otherwise, the account defers to the password_require_current setting. 

Password verification is optional by default – the password_require_current system variable is disabled and accounts that are created with no PASSWORD REQUIRE option defaults to PASSWORD REQUIRE CURRENT DEFAULT. 

To see how per-account user settings interact with password_require_current system variable values to determine the account password verification-required policy, see below:

Per-Account Setting password_require_current System Variable Password Changes Require Current Password?

PASSWORD REQUIRE CURRENT OFF Yes

PASSWORD REQUIRE CURRENT ON Yes

PASSWORD REQUIRE CURRENT OPTIONAL OFF No

PASSWORD REQUIRE CURRENT OPTIONAL ON No

PASSWORD REQUIRE CURRENT DEFAULT OFF No

PASSWORD REQUIRE CURRENT DEFAULT ON Yes

For example, to implement a per-user requirement that the current password must be entered to change the password for a specific user, the following CREATE USER statement will accomplish this:

The ALTER USER equivalent would be:

This per-user verification policy overrides the global policy for all accounts specified. 

Global Settings

If you prefer to establish a global password-verification policy, change the password_require_current system variable from its disabled default. As an example, to establish a global policy that password changes must specify the current password, set the following in the configuration file:

You can also set this at runtime with the SET PERSIST statement within a running instance:

This sets a value for the running MySQL instance and saves the value to carry over to subsequent server restarts. To change the value for the running MySQL instance without having it carry over to subsequent restarts, use the GLOBAL keyword instead of PERSIST. 

Keep in mind that any per-user settings will override the global setting. This allows you to tailor a custom authentication scenario based on your use case. 

Exceptions

Privileged users can change any account password without specifying the current password regardless of the verification-required policy. A privileged user is any user with the global CREATE USER or UPDATE privilege for the ‘mysql’ system database. 

Wrapping Up

MySQL 8 has made great strides in bolstering user administration and user security. While we often take for granted something as simple as requiring the current password before a password change, using this option in concert with other MySQL 8 security features builds a secure and easily administered database environment that hasn’t been possible historically without relying on additional plugins or custom solutions.