dasini.net &#8211; Diary of a MySQL expert

Watch my videos on my YouTube channel and subscribe.

Thanks for using HeatWave & MySQL!

4

Filed under:
Group Replication, High Availability, MySQL, Tuto by Olivier DASINI

Plan your MySQL upgrade

October 21, 2020

Tags: Migration, Upgrade, video

Jardin Balata Martinique by Olivier DASINI

I’ve made a short video that will give you tips and tricks to successfully upgrade to MySQL 8

These information are from my presentation : Upgrade from MySQL 5.7 to MySQL 8.0

Thanks for using MySQL!

Follow me on twitter

Watch my videos on my YouTube channel.

Comments Off

Filed under:
Migration, MySQL, Trick, Video by Olivier DASINI

MySQL 8.0.21 New Features Summary

August 27, 2020

Tags: 8.0.21, Presentation, Summary

Presentation of some of the new features of MySQL 8.0.21 released on July 13th, 2020.

Highlight

Runtime disabling Innodb Redo Log
JSON_VALUE fonction
CREATE TABLE… SELECT is atomic
Per-user Comments & Attributes
MySQL Document Store Enhancements
MySQL Shell Enhancements
MySQL Router Enhancements
MySQL InnoDB Cluster Enhancements
MySQL Group Replication Enhancements
Thanks to the Contributors

Slides

MySQL 8.0.21 – New Features Summary from Olivier DASINI

Download this presentation and others on my SlideShare account.

Watch my videos on my YouTube channel.

That might interest you

Watch my videos on my YouTube channel and subscribe.

Thanks for using HeatWave & MySQL!

1

Filed under:
MySQL, New by Olivier DASINI

MySQL 8.0.20 New Features Summary

May 26, 2020

Tags: 8.0.20, Presentation, Summary, video

Presentation of some of the new features of MySQL 8.0.20 released on April 27th, 2020.

Highlight

Hash Joins
New InnoDB Doublewrite Buffer
Index-Level Optimizer Hints
SHOW_ROUTINE Privilege
MySQL Shell Enhancements
MySQL Router Enhancements
MySQL InnoDB Cluster Enhancements
MySQL Replication Enhancements
MySQL NDB Cluster Enhancements
MySQL Enterprise New Features
Thanks to the Contributors

Slides

MySQL 8.0.20 – New Features Summary from Olivier DASINI

Download this presentation and others on my SlideShare account.

Video

Watch this video and others on my YouTube channel.

That might interest you

Watch my videos on my YouTube channel and subscribe.

Thanks for using HeatWave & MySQL!

3

Filed under:
MySQL, New, Video by Olivier DASINI

MySQL Security – Dual Password Support

May 19, 2020

Tags: Dual Password, HIPAA, password, PCI DSS, security

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, Password Verification-Required Policy, Failed-Login Tracking and Temporary Account Locking, Connection-Control Plugins, Password Validation Component, etc…

TL;DR

Dual-password capability makes it possible to seamlessly perform credential changes without downtime.

MySQL implements dual-password capability with syntax that saves and discards secondary passwords :

The RETAIN CURRENT PASSWORD clause for the ALTER USER and SET PASSWORD statements saves an account current password as its secondary password when you assign a new primary password.
The DISCARD OLD PASSWORD clause for ALTER USER discards an account secondary password, leaving only the primary password.

The purpose is to avoid downtime while changing passwords in a replicated environment.

Clients can use the old password while a new password is being established in a group of servers and retire the old password only when the new password has been established across the whole group.

The workflow is :

On each server that is not a replication slave, establish the new password
e.g.
ALTER USER ‘myApp’@’host’ IDENTIFIED BY ‘NEW_password’ RETAIN CURRENT PASSWORD;
Wait for the password change to replicate throughout the system to all slave servers
Modify each application that uses the myApp account so that it connects to the servers using a password of ‘NEW_password’ rather than ‘OLD_password’
On each server that is not a replication slave, discard the secondary password
e.g.
ALTER USER ‘myApp’@’host’ DISCARD OLD PASSWORD;

Let’s take a quick look using MySQL 8.0

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

Create a user account myApp@localhost with password pwd1 :

MySQL root SQL> 
CREATE USER myApp@localhost IDENTIFIED BY 'pwd1';

Now we can connect with the name and the password :

$ mysql -u myApp -ppwd1 -e"SELECT USER()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| USER()          |
+-----------------+
| myApp@localhost |
+-----------------+

Note:
As indicated in the output, it is a very bad practice to put the password on the command line interface.

Now the DBA (super user) use ALTER USER statement with the RETAIN CURRENT PASSWORD clause to perform credential changes using the dual password mechanism by adding as primary password pwd2.
Thus pwd1 is now the secondary password :

MySQL root SQL> 
ALTER USER myApp@localhost IDENTIFIED BY 'pwd2' RETAIN CURRENT PASSWORD;

We can use the user name and the new password (pwd2) to connect :

$ mysql -u myApp -ppwd2 -e"SELECT USER()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| USER()          |
+-----------------+
| myApp@localhost |
+-----------------+

But the old password (pwd1) is still valid :

$ mysql -u myApp -ppwd1 -e"SELECT USER()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| USER()          |
+-----------------+
| myApp@localhost |
+-----------------+

Now it is the time to discard the secondary password (pwd1) :

MySQL root SQL> 
ALTER USER myApp@localhost DISCARD OLD PASSWORD;

$ mysql -u myApp -ppwd2 -e"SELECT USER()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| USER()          |
+-----------------+
| myApp@localhost |
+-----------------+


$ mysql -u myApp -ppwd1 -e"SELECT USER()"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'myApp'@'localhost' (using password: YES)

As you can see, only the new password (pwd2) is valid.

To Go Further

Reference Manual

Dual Password Support

MySQL Security

MySQL Security Serie – Password Management

MySQL Security Serie (1st edition)

Thanks for using MySQL!

Watch my videos on my YouTube channel and subscribe.

Thanks for using HeatWave & MySQL!

2

Filed under:
MySQL, Security, Tuto by Olivier DASINI

MySQL Security – Failed-Login Tracking and Temporary Account Locking

May 12, 2020

Tags: HIPAA, PCI DSS, security, User Account Locking

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, Password Verification-Required Policy, 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 unfortunately not enough.
Good news, MySQL 8.0 provide an easy way to increase database security with its failed-login tracking and temporary account locking feature.

TL;DR

DBA can configure user accounts such that too many consecutive login failures cause temporary account locking.

Temporary Account Locking in MySQL

After a number of consecutive time when the client failed to provide a correct password during a connection attempt, the user account can be temporary locked.

The required number of failures and the lock time are configurable per account, using the FAILED_LOGIN_ATTEMPTS (track consecutive login failures) and PASSWORD_LOCK_TIME (how many days to lock the account).

Both are options of the CREATE USER and ALTER USER statements.

Let’s have a quick look using MySQL 8.0

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

Account lock

Create a user that would have his account locked for 1 day after 1 consecutive failed logins :

$ mysqlsh root@localhost:3306 --sql
...


MySQL localhost:3306 ssl SQL> 
CREATE USER aUser@localhost IDENTIFIED BY 'pAssw0rD' FAILED_LOGIN_ATTEMPTS 1 PASSWORD_LOCK_TIME 1;

FAILED_LOGIN_ATTEMPTS : how many consecutive incorrect passwords cause temporary account locking.
A value of 0 disables the option.

PASSWORD_LOCK_TIME : number of days the account remains locked or UNBOUNDED (ie the duration of that state does not end until the account is unlocked).
A value of 0 disables the option.

We can see the user account details with mysql.user table :

MySQL localhost:3306 ssl SQL> 
SELECT user, host, User_attributes FROM mysql.user WHERE user = 'aUser'\G
*************************** 1. row ***************************
           user: aUser
           host: localhost
User_attributes: {"Password_locking": {"failed_login_attempts": 1, "password_lock_time_days": 1}}

If login failed a “FAILED_LOGIN_ATTEMPTS” number of time (1 time in this example), the account will be locked :

MySQL localhost:3306 ssl SQL> 
\connect aUser@localhost:3306
Creating a session to 'aUser@localhost:3306'
Please provide the password for 'aUser@localhost:3306': *
MySQL Error 3955 (HY000): Access denied for user 'aUser'@'localhost'. Account is blocked for 1 day(s) (1 day(s) remaining) due to 1 consecutive failed logins.

Tracking and locking could also be set up after the user creation :

$ mysqlsh root@localhost:3306 --sql


MySQL localhost:3306 ssl SQL> 
CREATE USER aUser2@localhost IDENTIFIED BY 'Dr0wssAp';


ALTER USER aUser2@localhost FAILED_LOGIN_ATTEMPTS 2 PASSWORD_LOCK_TIME UNBOUNDED;

In this example this user account will be locked (until the account is unlocked – more on that later) after 2 consecutive failed attempts.

You can also lock an account explicitly using ACCOUNT LOCK clause :

MySQL localhost:3306 ssl SQL>
CREATE USER aLockedUser@localhost IDENTIFIED BY RANDOM PASSWORD ACCOUNT LOCK;
+-------------+-----------+----------------------+
| user        | host      | generated password   |
+-------------+-----------+----------------------+
| aLockedUser | localhost | @.Yp{;ONp7-G62+EfON1 |
+-------------+-----------+----------------------+

In this example I created a user account with a random password generated by MySQL. This account is created locked.

Details are visible with mysql.user table :

MySQL localhost:3306 ssl SQL>
SELECT user, host, account_locked FROM mysql.user WHERE user = 'aLockedUser';
+-------------+-----------+----------------+
| user        | host      | account_locked |
+-------------+-----------+----------------+
| aLockedUser | localhost | Y              |
+-------------+-----------+----------------+

Any connection to this account will raised error 3118 :

MySQL localhost:3306 ssl SQL>
\connect aLockedUser@localhost:3306 
Creating a session to 'aLockedUser@localhost:3306'
Please provide the password for 'aLockedUser@localhost:3306': ********************
MySQL Error 3118 (HY000): Access denied for user 'aLockedUser'@'localhost'. Account is locked.

This account can be activate with something like :

MySQL localhost:3306 ssl SQL> 
ALTER USER aLockedUser@localhost ACCOUNT UNLOCK FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME UNBOUNDED;

Again mysql.user table will give you some information :

MySQL localhost:3306 ssl SQL> 
SELECT user, host, account_locked, User_attributes FROM mysql.user WHERE user = 'aLockedUser'\G
*************************** 1. row ***************************
           user: aLockedUser
           host: localhost
 account_locked: N
User_attributes: {"Password_locking": {"failed_login_attempts": 5, "password_lock_time_days": -1}}

Account unlock

Account can be unlocked with an ALTER USER … ACCOUNT UNLOCK statement :

MySQL localhost:3306 ssl SQL>
\connect aUser@localhost:3306
Creating a session to 'aUser@localhost:3306'
Please provide the password for 'aUser@localhost:3306': 
MySQL Error 3955 (HY000): Access denied for user 'aUser'@'localhost'. Account is blocked for unlimited day(s) (unlimited day(s) remaining) due to 2 consecutive failed logins.


ALTER USER aUser@localhost ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.0047 sec)


\connect aUser@localhost:3306
Creating a session to 'aUser@localhost:3306'
Please provide the password for 'aUser@localhost:3306': ********
Closing old connection...
Your MySQL connection id is 63
...
SELECT USER();
+-----------------+
| USER()          |
+-----------------+
| aUser@localhost |
+-----------------+

Other possibilities to unlock an account are :

Execution of an ALTER USER statement for the account that sets either FAILED_LOGIN_ATTEMPTS or PASSWORD_LOCK_TIME (or both) to any value.
e.g.

ALTER USER aUser@localhost FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1;

Obviously when the lock duration passes.
In this case, failed-login counting resets at the time of the next login attempt.
Execution of FLUSH PRIVILEGES
A server restart

To Go Further

Reference Manual

Failed-Login Tracking and Temporary Account Locking

MySQL Security

MySQL Security Serie – Password Management

MySQL Security Serie (1st edition)

Thanks for using MySQL!

Watch my videos on my YouTube channel and subscribe.

Thanks for using HeatWave & MySQL!

4

Filed under:
MySQL, Security, Tuto by Olivier DASINI

MySQL Security – Password Verification-Required Policy

May 5, 2020

Tags: HIPAA, password, Password Verification, PCI DSS, security

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.

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

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

MySQL Security

MySQL Security Serie – Password Management

MySQL Security Serie (1st edition)

Thanks for using MySQL!

Watch my videos on my YouTube channel and subscribe.

Thanks for using HeatWave & MySQL!

4

Filed under:
MySQL, Security, Tuto by Olivier DASINI

MySQL Security – Password Reuse Policy

April 28, 2020

Tags: HIPAA, Password Reuse, PCI DSS, security

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 Verification-Required 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. Actually, some regulations may require that users can not reuse a previous password.

You can do that by setting how often and / or how long an old password can be reuses. In this article, from my new MySQL Security series, we will see how to establish a policy for password reuse with MySQL 8.0 Password Reuse Policy.

TL;DR

MySQL provides password-reuse capability, which allows database administrators to determine the number of unique passwords a user must use before they can use an old password again.

Enable restrictions on reuse of previous passwords with MySQL

The main goal of Password Reuse Policy is to enable restrictions to be placed on reuse of previous passwords.
It can be established globally, and individual accounts can be set to either defer to the global policy or override the global policy with specific per-account behavior.

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

Let’s dig into it using MySQL 8.0.

$ mysqlsh daz@localhost --sql

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

PASSWORD HISTORY

Prohibit reusing any of the last 10 (then 24) passwords :

MySQL SQL> 
CREATE USER 'aUser'@'localhost' PASSWORD HISTORY 10;


SELECT user, host, password_reuse_history, password_last_changed FROM mysql.user WHERE user = 'aUser'\G
*************************** 1. row ***************************
                  user: aUser
                  host: localhost
password_reuse_history: 10
 password_last_changed: 2020-04-03 09:45:45


ALTER USER 'aUser'@'localhost' PASSWORD HISTORY 24;


SELECT user, host, password_reuse_history, password_last_changed FROM mysql.user WHERE user = 'aUser'\G
*************************** 1. row ***************************
                  user: aUser
                  host: localhost
password_reuse_history: 24
 password_last_changed: 2020-04-03 09:45:45

PASSWORD REUSE INTERVAL n DAY

Require a minimum of 180 (then 365) days elapsed before permitting reuse :

MySQL SQL> 
CREATE USER 'bUser'@'localhost' PASSWORD REUSE INTERVAL 180 DAY;


SELECT user, host, password_reuse_time, password_last_changed FROM mysql.user WHERE user = 'bUser'\G
*************************** 1. row ***************************
                 user: bUser
                 host: localhost
  password_reuse_time: 180
password_last_changed: 2020-04-03 10:03:20


ALTER USER 'bUser'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;


SELECT user, host, password_reuse_time, password_last_changed FROM mysql.user WHERE user = 'bUser'\G
*************************** 1. row ***************************
                 user: bUser
                 host: localhost
  password_reuse_time: 365
password_last_changed: 2020-04-03 10:03:20

Combine types of reuse restrictions

It is also possible to combine both types of reuse restrictions.
Simply use PASSWORD HISTORY and PASSWORD REUSE INTERVAL n DAY together :

MySQL SQL> 
CREATE USER 'cUser'@'localhost' 
  PASSWORD HISTORY 5 
  PASSWORD REUSE INTERVAL 180 DAY;


SELECT user, host, password_reuse_history, password_reuse_time, password_last_changed FROM mysql.user WHERE user = 'cUser'\G
*************************** 1. row ***************************
                  user: cUser
                  host: localhost
password_reuse_history: 5
   password_reuse_time: 180
 password_last_changed: 2020-04-03 10:11:31


ALTER USER 'cUser'@'localhost' 
  PASSWORD HISTORY 10 
  PASSWORD REUSE INTERVAL 365 DAY;


SELECT user, host, password_reuse_history, password_reuse_time, password_last_changed FROM mysql.user WHERE user = 'cUser'\G
*************************** 1. row ***************************
                  user: cUser
                  host: localhost
password_reuse_history: 10
   password_reuse_time: 365
 password_last_changed: 2020-04-03 10:11:31

Global Policy

Reuse policy can be established globally, as specified by the password_history and password_reuse_interval system variables.

The default password_history value is 0, which disables automatic password expiration.
Same for password_reuse_interval.

password_history and password_reuse_interval variables can be set in the MySQL configuration file (usually my.cnf or my.ini) but it can also be set and persisted at runtime using SET PERSIST :

SET PERSIST password_history = 10;

SET PERSIST password_reuse_interval = 365;


SHOW VARIABLES WHERE Variable_name IN ('password_history','password_reuse_interval');
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| password_history        | 10    |
| password_reuse_interval | 365   |
+-------------------------+-------+

The same behavior can be achieved using the my.cnf (or my.ini) file :

[mysqld]
password_history = 10
password_reuse_interval = 365

However it requires a server restart.

To defer the global policy for an account for both types of reuse restrictions you must use the DEFAULT clause :

MySQL SQL> 
CREATE USER olivier@localhost
  PASSWORD HISTORY DEFAULT
  PASSWORD REUSE INTERVAL DEFAULT;


ALTER USER ethan@localhost
  PASSWORD HISTORY DEFAULT
  PASSWORD REUSE INTERVAL DEFAULT;

To establish a global policy such that none of these restriction exist, set password_history and password_reuse_interval to 0 :

MySQL SQL> 
SET PERSIST password_history = 0;

SET PERSIST password_reuse_interval = 0;


SHOW VARIABLES WHERE Variable_name IN ('password_history','password_reuse_interval');
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| password_history        | 0     |
| password_reuse_interval | 0     |
+-------------------------+-------+

Please note that the empty password does not count in the password history and is subject to reuse at any time.

To Go Further

Reference Manual

MySQL Security

MySQL Security Serie – Password Management

MySQL Security Serie (1st edition)

Thanks for using MySQL!

Watch my videos on my YouTube channel and subscribe.

Thanks for using HeatWave & MySQL!

3

Filed under:
MySQL, Security, Tuto by Olivier DASINI

MySQL Security – Password Expiration Policy

April 21, 2020

Tags: HIPAA, password, Password Expiration, PCI DSS, security

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 Reuse Policy, Password Verification-Required 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. Actually, some regulations required that the password is renewed in a timely and appropriate manner (e.g. every 90 days).

In this article, we will see how to establish a policy for password expiration with MySQL 8.0 Password Expiration Policy.

TL;DR

MySQL provides password-expiration capability, which enables database administrators to require that users reset their password.

Establish a policy for password expiration with MySQL

The main goal of Password Expiration Policy is to require passwords to be changed periodically.
It can be established globally, and individual accounts can be set to either defer to the global policy or override the global policy with specific per-account behavior.

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

Let’s take a closer look using MySQL 8.0.

$ mysqlsh daz@localhost --sql

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

PASSWORD EXPIRE

Force user to change its password at the first connection.

Create a user with a random password and mark that password expired :

-- Mark the password expired so that the user must choose a new one at the first connection to the server
MySQL SQL> 
CREATE USER 'aUser'@'localhost' IDENTIFIED BY RANDOM PASSWORD PASSWORD EXPIRE;
+-------+-----------+----------------------+
| user  | host      | generated password   |
+-------+-----------+----------------------+
| aUser | localhost | (wvx3n7jH)bVNi3tOiQV |
+-------+-----------+----------------------+

We can see if the password is expired with mysql.user table :

MySQL SQL> 
SELECT user, host, password_lifetime, password_expired, password_last_changed FROM mysql.user WHERE user = 'aUser' \G
*************************** 1. row ***************************
                 user: aUser
                 host: localhost
    password_lifetime: NULL
     password_expired: Y
password_last_changed: 2020-04-01 12:31:57

Note that password_expired column is Y.

In clear, this new MySQL user will be able to connect to the server but he must reset its password before being able to executing statements

$ mysql -u aUser -p
Enter password: 
...


mysql> SELECT USER();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.


mysql> ALTER USER user() IDENTIFIED BY 'n3w_pAssw0rd';
Query OK, 0 rows affected (0.01 sec)


mysql> SELECT USER();
+-----------------+
| USER()          |
+-----------------+
| aUser@localhost |
+-----------------+

Column password_expired is now N.

MySQL SQL> 
SELECT user, host, password_lifetime, password_expired, password_last_changed FROM mysql.user WHERE user = 'aUser' \G
*************************** 1. row ***************************
                 user: aUser
                 host: localhost
    password_lifetime: NULL
     password_expired: N
password_last_changed: 2020-04-01 12:41:25

PASSWORD EXPIRE INTERVAL n DAY

Force user to change its password every N days.

Create a user with password that will expire in 90 days :

MySQL SQL> 
-- Require that a new password be chosen every 90 days
CREATE USER 'aNewUser'@'localhost' IDENTIFIED BY 'aN3w_pAssw0rd' PASSWORD EXPIRE INTERVAL 90 DAY;

We can see the password options in the mysql.user table :

MySQL SQL> 
SELECT user, host, password_lifetime, password_expired, password_last_changed FROM mysql.user WHERE user = 'aNewUser'\G
*************************** 1. row ***************************
                 user: aNewUser
                 host: localhost
    password_lifetime: 90
     password_expired: N
password_last_changed: 2020-04-01 15:40:14

Note that password_lifetime column is 90.

After 90 days any statement will generate error 1820 :

$ mysql -u aNewUser -p
...


mysql {aNewUser}> SELECT USER();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Password could be reset with ALTER USER command :

mysql {aNewUser}> 
ALTER USER user() IDENTIFIED BY '4noth3r_pa5sw0rd';
Query OK, 0 rows affected (0.01 sec)


mysql {aNewUser}> SELECT USER();
+--------------------+
| USER()             |
+--------------------+
| aNewUser@localhost |
+--------------------+

PASSWORD EXPIRE DEFAULT

This clause sets the account so that the global password expiration policy applies, as specified by the default_password_lifetime system variable.

The default default_password_lifetime value is 0, which disables automatic password expiration.
If the value of default_password_lifetime is a positive integer N, it indicates the permitted password lifetime; passwords must be changed every N days.

default_password_lifetime can be set in the MySQL configuration file but it can also be set and persisted at runtime using SET PERSIST :

MySQL SQL> 
SET PERSIST default_password_lifetime = 30;

The same behavior can be achieved using the configuration file (usually my.cnf or my.ini) :

[mysqld]
default_password_lifetime = 30

but it will require a server restart.

To defer the global expiration policy for an account you should use Password Expire Default clause :

MySQL SQL> 
CREATE USER olivier@localhost  PASSWORD EXPIRE DEFAULT;


ALTER USER aNewUser@localhost PASSWORD EXPIRE DEFAULT;

Lastly, to establish a global policy such that passwords never expire, set default_password_lifetime to 0 :

MySQL SQL> 
SET PERSIST default_password_lifetime = 0;


SHOW VARIABLES LIKE 'default_password_lifetime';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 0     |
+---------------------------+-------+

PASSWORD EXPIRE NEVER

It’s also possible to disable password expiration for an account.

MySQL SQL> 
-- Disables password expiration for the account so that its password never expires
CREATE USER 'pingDB'@'localhost' IDENTIFIED BY 'A-p45swOrd' PASSWORD EXPIRE NEVER;


SELECT user, host, password_lifetime, password_expired, password_last_changed FROM mysql.user WHERE user = 'pingDB'\G
*************************** 1. row ***************************
                 user: pingDB
                 host: localhost
    password_lifetime: 0
     password_expired: N
password_last_changed: 2020-04-02 12:42:03

Note that password_lifetime column is 0.

This expiration option overrides the global policy for all accounts named by the statement.

To Go Further

Reference Manual

MySQL Security

MySQL Security Serie – Password Management

MySQL Security Serie (1st edition)

Thanks for using MySQL!

Watch my videos on my YouTube channel and subscribe.

Thanks for using HeatWave & MySQL!

3

Filed under:
MySQL, Security, Tuto by Olivier DASINI

MySQL Security – Random Password Generation

April 15, 2020

Tags: HIPAA, password, PCI DSS, Random Password Generation, security

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, Password Expiration Policy, Password Reuse Policy, Password Verification-Required 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

Indeed this is a good start !

What if MySQL make your life easier by helping you to create user with strong secure password?
Well it is now possible in MySQL 8.0 🙂

TL;DR

MySQL has the capability of generating random passwords for user accounts, as an alternative to requiring explicit administrator-specified literal passwords.

A DBA can use CREATE USER, ALTER USER or SET PASSWORD for generate random passwords for user accounts.

Let’s have a quick look using MySQL 8.0

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

Create a user account

To create a new MySQL user account with a random password use the statement CREATE USER with the clause IDENTIFIED BY RANDOM PASSWORD :

MySQL SQL> 
CREATE USER aUser@localhost IDENTIFIED BY RANDOM PASSWORD;
+-------+-----------+----------------------+
| user  | host      | generated password   |
+-------+-----------+----------------------+
| aUser | localhost | XtByqo%asj81CJmM[dfC |
+-------+-----------+----------------------+

Modify a user account

To assign a new random password to a MySQL user account use the statement ALTER USER with the clause IDENTIFIED BY RANDOM PASSWORD :

MySQL SQL> 
ALTER USER aUser@localhost IDENTIFIED BY RANDOM PASSWORD;
+-------+-----------+----------------------+
| user  | host      | generated password   |
+-------+-----------+----------------------+
| aUser | localhost | Sn!b%6(Iu>{34GIOj>vS |
+-------+-----------+----------------------+

Assign a password

Another way to assign a new random password to a MySQL user account is to use the statement SET PASSWORD with the clause TO RANDOM :

MySQL SQL> 
SET PASSWORD FOR aUser@localhost TO RANDOM;
+-------+-----------+----------------------+
| user  | host      | generated password   |
+-------+-----------+----------------------+
| aUser | localhost | 7,ln_3HXG<b8Kvw!o&u, |
+-------+-----------+----------------------+

Please note that by default, generated random passwords have a length of 20 characters.
This length is controlled by the generated_random_password_length system variable, which has a range from 5 to 255.

To Go Further

Reference Manual

MySQL Security

MySQL Security Serie – Password Management

MySQL Security Serie (1st edition)

Thanks for using MySQL!

Watch my videos on my YouTube channel and subscribe.