MySQL Security – Password Verification-Required Policy

May 5, 2020

When thinking about security within a MySQL installation, you can consider a wide range of possible procedures / best practices and how they affect the security of your MySQL server and related applications.

MySQL provides many tools / features / plugins or components in order to protect your data including some advanced features like Transparent Data Encryption (TDE)Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration Policy, Password Reuse Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, Connection-Control Plugins, Password Validation Component, etc…

MySQL Security

Basic password policy practices teaches us :

  • Each user must have a password
  • A user’s password should be changed periodically

However, often this is not enough.
Password Verification-Required Policy can help you to protect your database.
It will make it harder to modify a user’s password if someone get access to user’s session and not the credentials themselves.


TL;DR

MySQL 8.0 has introduced an optional behavior that authorize users to change their password only if they could provide the current password.

Require MySQL users to provide their current password to change it

There are different clauses a DBA can use with CREATE USER or ALTER USER to establish a per account password verification-required policy.

Let’s play using MySQL 8.0

PASSWORD REQUIRE CURRENT

Require that password changes specify the current password.

Syntax:
CREATE USER <user>@<host> PASSWORD REQUIRE CURRENT;
ALTER USER <user>@<host> PASSWORD REQUIRE CURRENT;

Create a user account with a password generated by MySQL and enable the password verification required policy :

We can see the policy is enable for this account with mysql.user table :

Note that Password_require_current column is Y.

We can test the policy.
Connect to the new created account :

Then modify the password :

To avoid the error 3892, we must use the REPLACE clause and provide the current password.

Please note that privileged users (users having the global CREATE USER privilege or the UPDATE privilege for the mysql system database) can change any account password without specifying the current password, regardless of the verification-required policy.

In other words, as a DBA privileged user I am able to change someone else password without the REPLACE clause :

PASSWORD REQUIRE CURRENT OPTIONAL

Do not require that password changes specify the current password (the current password may but need not be given).

Syntax:
CREATE USER <user>@<host> PASSWORD REQUIRE CURRENT OPTIONAL;
ALTER USER <user>@<host> PASSWORD REQUIRE CURRENT OPTIONAL;

Create a user account with a password generated by MySQL and enable the password verification policy but it is not required :

Note that Password_require_current column is N.

We can test the policy.
Connect to the new created account :

Then modify the password :

The current password is not required to change the password, well it is… optional 🙂

Global policy

The password verification-required policy is controlled by the password_require_current global system variable.

It can be changed online and persisted with SET PERSIST.

An alternative is to write it in the configuration file (usually my.cnf or my.ini) and restart the MySQL instance.

PASSWORD REQUIRE CURRENT DEFAULT

Defer to the global password verification-required policy for all accounts named by the statement.

Syntax:
CREATE USER <user>@<host> PASSWORD REQUIRE CURRENT DEFAULT;
ALTER USER <user>@<host> PASSWORD REQUIRE CURRENT DEFAULT;

Create a user account where its password verification policy take the global default value set a the instance level :

We can test the policy.
Connect to the new created account :

Because the global policy enable the Password Verification-Required Policy, we must use the REPLACE clause.

To Go Further

Reference Manual

MySQL Security Serie (1st edition)

Thanks for using MySQL!

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

4 Responses to “MySQL Security – Password Verification-Required Policy”

  1. […] Data Masking & De-Identification, Firewall, Password Expiration Policy, Password Reuse Policy, Password Verification-Required Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, Connection-Control […]

  2. […] Firewall, Random Password Generation, Password Expiration Policy, Password Reuse Policy, Password Verification-Required Policy, Dual Password Support, Connection-Control Plugins, Password Validation Component, […]

  3. […] Masking & De-Identification, Firewall, Random Password Generation, Password Expiration Policy, Password Verification-Required Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, Connection-Control […]

  4. […] Data Masking & De-Identification, Firewall, Random Password Generation, Password Reuse Policy, Password Verification-Required Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, Connection-Control […]