MySQL Security; Random Password Generation.

Preamble

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 can generate randomly generated passwords for user accounts rather than requiring explicitly defined literal passwords from the administrator.

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

Rather of asking the administrator to supply precisely defined, literal passwords. MySQL can produce random passwords for user accounts.

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.

Create users with a random password in MySQL

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.