MySQL Security — Random Password Generation

Thinking about security in MySQL installation, you can consider a wide range of possible procedures/recommendations and their impact on the security of your MySQL server and related applications.

MySQL provides many tools/functions/plugins or components to protect your data, including some additional features such as 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, etc.

Basic password policy practices teach us:

• Every user should have a password.
• User password should be changed periodically

Indeed, this is a good start!

WHAT IF MYSQL MAKES YOUR LIFE EASIER BY HELPING YOU CREATE A USER WITH A STRONG, SECURE PASSWORD?

Well, it’s now possible in MySQL 8.0.

TL; DR

MySQL has the ability to generate random passwords for user accounts, as an alternative to the requirement of explicit administrator defined literal passwords.

The database administrator can use CREATE USER, ALTER USER or SET PASSWORD to generate random passwords for user accounts.

Let us briefly review the use of MySQL 8.0.

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

CREATE USER ACCOUNT

To create a new MySQL user account with a random password, use the CREATE USER operator with the offer IDENTIFIED BY RANDOM PASSWORD:

MySQL SQL>
CREATE USER aUser@localhost IDENTIFIED BY RANDOM PASSWORD;
+----------+-----------+----------------------+
| user | host | generated password |
+----------+-----------+----------------------+
| AndreyEx | localhost | M3BA1Po%as1Kse8Jt!aC |
+----------+-----------+----------------------+

EDIT USER ACCOUNT

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

MySQL SQL>
ALTER USER aUser@localhost IDENTIFIED BY RANDOM PASSWORD;
+----------+-----------+----------------------+
| user | host | generated password |
+----------+-----------+----------------------+
| AndreyEx | localhost | SjAA*@(LA&fd43IOj>vS |
+----------+-----------+----------------------+

ASSIGN PASSWORD

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

MySQL SQL>
SET PASSWORD FOR aUser@localhost TO RANDOM;
+----------+-----------+----------------------+
| user | host | generated password |
+----------+-----------+----------------------+
| AndreyEx | localhost | 7kaJY^%x1<b8kT&84Du, |
+----------+-----------+----------------------+

Note that by default generated random passwords are 20 characters long.
This length is controlled by the system variable generate_random_password_length, which has a range from 5 to 255.