dasini.net – Diary of a MySQL expert

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…

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.

---

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

MySQL SQL> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.19    |
+-----------+

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 :

MySQL SQL> 
CREATE USER olivier@localhost IDENTIFIED BY RANDOM PASSWORD PASSWORD REQUIRE CURRENT;
+---------+-----------+----------------------+
| user    | host      | generated password   |
+---------+-----------+----------------------+
| olivier | localhost | S0RR73vpVqVPr35QdK&h |
+---------+-----------+----------------------+

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

SELECT user, host, Password_require_current, password_last_changed FROM mysql.user WHERE user = 'olivier'\G
*************************** 1. row ***************************
                    user: olivier
                    host: localhost
Password_require_current: Y
   password_last_changed: 2020-04-03 15:08:00

Note that Password_require_current column is Y.

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

MySQL SQL> 
\connect olivier@localhost
Creating a session to 'olivier@localhost'
Please provide the password for 'olivier@localhost': ********************

Then modify the password :

MySQL olivier SQL> 
ALTER USER USER() IDENTIFIED BY 'NEW_P4s5word';
ERROR: 3892: Current password needs to be specified in the REPLACE clause in order to change it.


MySQL olivier SQL> 
ALTER USER USER() IDENTIFIED BY 'NEW_P4s5word' REPLACE 'S0RR73vpVqVPr35QdK&h';
Query OK, 0 rows affected (0.0117 sec)

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 :

MySQL SQL> 
ALTER USER olivier@localhost identified by 'sïxS*Zj#&{2Svf}G';
Query OK, 0 rows affected (0.0098 sec)

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 :

MySQL SQL> 
CREATE USER ethan@localhost IDENTIFIED BY RANDOM PASSWORD PASSWORD REQUIRE CURRENT OPTIONAL;
+-------+-----------+----------------------+
| user  | host      | generated password   |
+-------+-----------+----------------------+
| ethan | localhost | B6>}Kgbw6;_>85e]U_A[ |
+-------+-----------+----------------------+


SELECT user, host, Password_require_current, password_last_changed FROM mysql.user WHERE user = 'ethan'\G
*************************** 1. row ***************************
                    user: ethan
                    host: localhost
Password_require_current: N
   password_last_changed: 2020-04-03 15:51:53

Note that Password_require_current column is N.

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

MySQL SQL> 
\connect ethan@localhost
Creating a session to 'ethan@localhost'
Please provide the password for 'ethan@localhost': ********************

Then modify the password :

MySQL ethan SQL> 
ALTER USER USER() IDENTIFIED BY 'NEW_P4s5word';
Query OK, 0 rows affected (0.0147 sec)


ALTER USER USER() IDENTIFIED BY 'An0th3r_Pa$$word' REPLACE 'NEW_P4s5word';
Query OK, 0 rows affected (0.0118 sec)

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.

MySQL SQL> 
SHOW VARIABLES LIKE 'password_require_current';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| password_require_current | OFF   |
+--------------------------+-------+


SET PERSIST password_require_current = ON;


SHOW VARIABLES LIKE 'password_require_current';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| password_require_current | ON    |
+--------------------------+-------+

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

[mysqld]
password_require_current = ON

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 :

MySQL SQL> 
SHOW VARIABLES LIKE 'password_require_current';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| password_require_current | ON    |
+--------------------------+-------+


CREATE USER defaultpvrp@localhost IDENTIFIED BY 'p0#' PASSWORD REQUIRE CURRENT DEFAULT;

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

MySQL SQL> 
\connect defaultpvrp@localhost
Creating a session to 'defaultpvrp@localhost'
Please provide the password for 'defaultpvrp@localhost': 
...


MySQL defaultpvrp SQL> 
ALTER USER USER() IDENTIFIED BY 'nEw_P4s5word';
ERROR: 3892: Current password needs to be specified in the REPLACE clause in order to change it.


ALTER USER USER() IDENTIFIED BY 'nEw_P4s5word' REPLACE 'p0#';
Query OK, 0 rows affected (0.0082 sec)

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

To Go Further

Reference Manual

• Password Verification-Required Policy
• password_require_current
• MySQL 8.0.13: Change Current Password Policy

MySQL Security Serie (1st edition)

• Password Validation Plugin
• Password Management
• User Account Locking
• The Connection-Control Plugins
• Enterprise Audit
• Enterprise Transparent Data Encryption (TDE)
• Enterprise Firewall
• Enterprise Data Masking and De-Identification

Thanks for using MySQL!

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

Filed under:
MySQL, Security, Tuto by Olivier DASINI