Infrastructure at your Service

Elisa Usai

Password Verification Policy in MySQL 8.0.13

The new release 8.0.13 for MySQL is available since last week.
Concerning security, this comes with a new feature already announced: the Password Verification Policy.
Let’s have a look…

This aim of this feature is to secure the attempts to change a password by specifying the old one to be replaced.
It is turned off by default:

mysql> show variables like 'password_require_current';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| password_require_current | OFF   |
+--------------------------+-------+

and we can activate it by several ways (as for some other password features):
1. Globally, at the server level:

mysql> set persist password_require_current='ON';
mysql> show variables like 'password_require_current';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| password_require_current | ON    |
+--------------------------+-------+

2. On a per-account-basis, and if we want to force the verification of the old password:

mysql> create user olivier@localhost identified by 'MySQLisPowerful' PASSWORD REQUIRE CURRENT;

3. On a per-account-basis, and if we want to make the verification of the old password optional:

mysql> create user gregory@localhost identified by 'SecurityIsImportant' PASSWORD REQUIRE CURRENT OPTIONAL;

Suppose that we have activated it at the server level, now let’s create one user account:

mysql> create user elisa@localhost identified by 'manager';

If we try to change the password for this user, we can do that without specifying any password:

mysql> alter user elisa@localhost identified by 'WhatsTheProblem';

Why? Because we are connected as the root account. Actually accounts which have the ‘CREATE USER’ or ‘UPDATE on mysql.*’ privileges are not affected by this policy.

So if we try to connect as our user ‘elisa’ and to change our password:

mysql> select user();
+-----------------+
| user()          |
+-----------------+
| elisa@localhost |
+-----------------+
mysql> alter user elisa@localhost identified by 'GoodVibes';
ERROR 13226 (HY000): Current password needs to be specified in the REPLACE clause in order to change it.

that is not possible. We can only do that if we specify our old password in the ‘ALTER USER’ statement through the ‘REPLACE’ clause:

mysql> alter user elisa@localhost identified by 'GoodVibes' replace 'WhatsTheProblem';
Query OK, 0 rows affected (0.12 sec)

Simple, isn’t it?
As a best practice in terms of security, I suggest you to activate this functionality in your MySQL environment.
For other information concerning new security features in MySQL 8.0 check the MySQL Documentation and come to my session MySQL 8.0 Community: Ready for GDPR? at the DOAG.

Leave a Reply

Elisa Usai
Elisa Usai